# HG changeset patch
# User Tom Gottfried <tom@intevation.de>
# Date 1583922260 -3600
# Node ID f697a398eff4abc32cc20f90e1be39f1a1d642bd
# Parent  f74e47c0c9193716fe58c9ba9a697dbe413a58da
Amend trigger function

Additional column names given as arguments to the trigger function
will be tested for equality along with the geometry. This allows to
implement behavior similar to a multi-column unique constraint
(in preparation to store historic versions of axis segments with
differing temporal validity).

Using EXISTS instead of the aggregate should be more efficient.

diff -r f74e47c0c919 -r f697a398eff4 schema/gemma.sql
--- a/schema/gemma.sql	Wed Mar 11 10:59:34 2020 +0100
+++ b/schema/gemma.sql	Wed Mar 11 11:24:20 2020 +0100
@@ -71,22 +71,30 @@
 
 -- Trigger function to be used as a constraint trigger to enforce uniqueness
 -- of geometries in the column with its name given as an argument to the
--- trigger function
+-- trigger function. If additional column names are given,
+-- the group of given columns is tested for equality.
 CREATE OR REPLACE FUNCTION prevent_st_equals() RETURNS trigger AS
 $$
 DECLARE
     new_geom geometry;
+    tg_arg text;
+    filters text;
     has_equal boolean;
 BEGIN
     EXECUTE format('SELECT CAST($1.%I AS geometry)', TG_ARGV[0])
         INTO new_geom
         USING NEW;
+    FOREACH tg_arg IN ARRAY TG_ARGV[1:] LOOP
+        -- Test each additional argument for equality
+        filters = format('%s AND %2$I = $2.%2$I', filters, tg_arg);
+    END LOOP;
     EXECUTE format(
-            'SELECT bool_or(ST_Equals($1, CAST(%I AS geometry))) FROM %I.%I '
-                'WHERE id <> $2',
-            TG_ARGV[0], TG_TABLE_SCHEMA, TG_TABLE_NAME)
+            'SELECT EXISTS(SELECT 1 FROM %I.%I '
+                'WHERE id <> $2.id AND ST_Equals($1, CAST(%I AS geometry))'
+                '%s)',
+             TG_TABLE_SCHEMA, TG_TABLE_NAME, TG_ARGV[0], filters)
         INTO has_equal
-        USING new_geom, NEW.id;
+        USING new_geom, NEW;
     IF has_equal THEN
         RAISE EXCEPTION
             'new row for relation "%" violates constraint trigger "%"',
diff -r f74e47c0c919 -r f697a398eff4 schema/updates/1422/01.amend_trigger_func.sql
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/schema/updates/1422/01.amend_trigger_func.sql	Wed Mar 11 11:24:20 2020 +0100
@@ -0,0 +1,39 @@
+CREATE OR REPLACE FUNCTION prevent_st_equals() RETURNS trigger AS
+$$
+DECLARE
+    new_geom geometry;
+    tg_arg text;
+    filters text;
+    has_equal boolean;
+BEGIN
+    EXECUTE format('SELECT CAST($1.%I AS geometry)', TG_ARGV[0])
+        INTO new_geom
+        USING NEW;
+    FOREACH tg_arg IN ARRAY TG_ARGV[1:] LOOP
+        -- Test each additional argument for equality
+        filters = format('%s AND %2$I = $2.%2$I', filters, tg_arg);
+    END LOOP;
+    EXECUTE format(
+            'SELECT EXISTS(SELECT 1 FROM %I.%I '
+                'WHERE id <> $2.id AND ST_Equals($1, CAST(%I AS geometry))'
+                '%s)',
+             TG_TABLE_SCHEMA, TG_TABLE_NAME, TG_ARGV[0], filters)
+        INTO has_equal
+        USING new_geom, NEW;
+    IF has_equal THEN
+        RAISE EXCEPTION
+            'new row for relation "%" violates constraint trigger "%"',
+                TG_TABLE_NAME, TG_NAME
+            USING
+                DETAIL = format('Failing row contains geometry in %s',
+                    Box2D(new_geom)),
+                ERRCODE = 23505,
+                SCHEMA = TG_TABLE_SCHEMA,
+                TABLE = TG_TABLE_NAME,
+                COLUMN = TG_ARGV[0],
+                CONSTRAINT = TG_NAME;
+    END IF;
+    RETURN NEW;
+END;
+$$
+LANGUAGE plpgsql;
diff -r f74e47c0c919 -r f697a398eff4 schema/version.sql
--- a/schema/version.sql	Wed Mar 11 10:59:34 2020 +0100
+++ b/schema/version.sql	Wed Mar 11 11:24:20 2020 +0100
@@ -1,1 +1,1 @@
-INSERT INTO gemma_schema_version(version) VALUES (1421);
+INSERT INTO gemma_schema_version(version) VALUES (1422);