--- a/schema/gemma.sql	Tue Jul 23 12:37:35 2019 +0200
+++ b/schema/gemma.sql	Tue Jul 23 13:10:51 2019 +0200
@@ -72,6 +72,47 @@
 $$
 LANGUAGE plpgsql;
 
+-- Trigger function to be used as a constraint trigger to enforce
+-- existance of a referenced gauge with intersecting validity.  The
+-- columns with the referenced gauge isrs code an the validity are
+-- given as arguments to the trigger function.
+CREATE OR REPLACE FUNCTION check_valid_gauge() RETURNS trigger AS
+$$
+DECLARE
+    -- FIXME: I'm using text for the isrs code and cast it on demand.
+    -- If someone is able to get it to work with isrs or isrs_base as
+    -- type, feel free to show me how its done...  ;-) [sw]
+    referenced_gauge text;
+    new_validity tstzrange;
+BEGIN
+    EXECUTE format('SELECT $1.%I', TG_ARGV[0])
+        INTO referenced_gauge
+        USING NEW;
+    EXECUTE format('SELECT $1.%I', TG_ARGV[1])
+        INTO new_validity
+        USING NEW;
+    IF EXISTS ( SELECT * FROM waterway.gauges
+                  WHERE location = referenced_gauge::isrs
+                  AND validity && new_validity )
+    THEN
+        RETURN NEW;
+    ELSE
+        RAISE EXCEPTION
+            'new row for relation "%" violates constraint trigger "%"',
+                TG_TABLE_NAME, TG_NAME
+            USING
+                DETAIL = format('No matching gauge %s found.',
+                    isrs_AsText(referenced_gauge::isrs)),
+                ERRCODE = 23505,
+                SCHEMA = TG_TABLE_SCHEMA,
+                TABLE = TG_TABLE_NAME,
+                COLUMN = TG_ARGV[0],
+                CONSTRAINT = TG_NAME;
+    END IF;
+END;
+$$
+LANGUAGE plpgsql;
+
 -- Trigger functions to be used as statement-level AFTER triggers,
 -- associating time-based referencing objects to matching version
 CREATE OR REPLACE FUNCTION move_gauge_referencing() RETURNS trigger AS
@@ -648,11 +689,6 @@
         EXCLUDE USING GiST (bottleneck_id WITH =, validity WITH &&)
             DEFERRABLE INITIALLY DEFERRED,
         gauge_location isrs NOT NULL,
-        gauge_validity tstzrange NOT NULL,
-        CHECK(validity <@ gauge_validity),
-        CONSTRAINT gauge_key
-            FOREIGN KEY (gauge_location, gauge_validity) REFERENCES gauges
-                ON UPDATE CASCADE,
         objnam varchar,
         nobjnm varchar,
         stretch isrsrange NOT NULL,
@@ -675,6 +711,9 @@
         source_organization varchar NOT NULL,
         staging_done boolean NOT NULL DEFAULT false
     )
+    CREATE CONSTRAINT TRIGGER waterway_bottlenecks_reference_gauge
+        AFTER INSERT OR UPDATE OF gauge_location ON bottlenecks
+        FOR EACH ROW EXECUTE FUNCTION check_valid_gauge('gauge_location','validity')
     -- Associate referencing objects to matching bottleneck version
     CREATE TRIGGER move_referencing AFTER INSERT OR UPDATE OF validity
         ON bottlenecks FOR EACH ROW