Mercurial > gemma

changeset 5656:796196f83678

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Fix check_sr_in_bn_area trigger function to only use staging_done bn.
author Sascha Wilde <wilde@sha-bang.de>
date Wed, 04 Oct 2023 18:53:55 +0200
parents ace5358e1527
children afbf8914bc2b
files schema/gemma.sql schema/updates/1471/01.fix-sr_in_bn_area-trigger.sql schema/version.sql
diffstat 3 files changed, 27 insertions(+), 2 deletions(-) [+]
line wrap: on
line diff
--- a/schema/gemma.sql	Thu Sep 14 18:52:18 2023 +0200
+++ b/schema/gemma.sql	Wed Oct 04 18:53:55 2023 +0200
@@ -248,7 +248,8 @@
     IF NOT st_intersects((SELECT area
                           FROM waterway.bottlenecks
                           WHERE bottleneck_id = NEW.bottleneck_id
-                            AND validity @> NEW.date_info::timestamptz),
+                            AND validity @> NEW.date_info::timestamptz
+                            AND staging_done),
                          NEW.area)
     THEN
         RAISE EXCEPTION
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/schema/updates/1471/01.fix-sr_in_bn_area-trigger.sql	Wed Oct 04 18:53:55 2023 +0200
@@ -0,0 +1,24 @@
+CREATE OR REPLACE FUNCTION check_sr_in_bn_area() RETURNS trigger
+LANGUAGE plpgsql
+AS $$
+BEGIN
+    IF NOT st_intersects((SELECT area
+                          FROM waterway.bottlenecks
+                          WHERE bottleneck_id = NEW.bottleneck_id
+                            AND validity @> NEW.date_info::timestamptz
+                            AND staging_done),
+                         NEW.area)
+    THEN
+        RAISE EXCEPTION
+            'new row for relation "%" violates constraint trigger "%"',
+                TG_TABLE_NAME, TG_NAME
+            USING
+                DETAIL = 'Failing row area has no intersection with bottleneck.',
+                ERRCODE = 23514,
+                SCHEMA = TG_TABLE_SCHEMA,
+                TABLE = TG_TABLE_NAME,
+                CONSTRAINT = TG_NAME;
+    END IF;
+    RETURN NEW;
+END;
+$$;
--- a/schema/version.sql	Thu Sep 14 18:52:18 2023 +0200
+++ b/schema/version.sql	Wed Oct 04 18:53:55 2023 +0200
@@ -1,1 +1,1 @@
-INSERT INTO gemma_schema_version(version) VALUES (1470);
+INSERT INTO gemma_schema_version(version) VALUES (1471);