# HG changeset patch
# User Sascha Wilde <wilde@sha-bang.de>
# Date 1696438435 -7200
# Node ID 796196f83678fcb726d27939b7758587a8cc5596
# Parent  ace5358e15271645c23980eb90f878507f9b2321
Fix check_sr_in_bn_area trigger function to only use staging_done bn.

diff -r ace5358e1527 -r 796196f83678 schema/gemma.sql
--- 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
diff -r ace5358e1527 -r 796196f83678 schema/updates/1471/01.fix-sr_in_bn_area-trigger.sql
--- /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;
+$$;
diff -r ace5358e1527 -r 796196f83678 schema/version.sql
--- 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);