Mercurial > gemma

diff schema/gemma.sql @ 5141:722b7c305319

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Prevent equal axis geometries with intersecting validity Such things usually cannot happen via importing, but it is at least consistent with constraints on other tables and the functionality is needed for fairway dimensions, since they are first stored in the staging area and equal geometries with intersecting validity could be generated by a concurrent import. Set the CONSTRAINT TRIGGER as INITIALLY DEFERRED because new entries are inserted before the validity of existing entries is adapted in the same transaction.
author Tom Gottfried <tom@intevation.de>
date Tue, 31 Mar 2020 12:50:30 +0200
parents 882b3d2308c4
children f11b9b50fcc9
line wrap: on
line diff
--- a/schema/gemma.sql	Tue Mar 31 10:52:26 2020 +0200
+++ b/schema/gemma.sql	Tue Mar 31 12:50:30 2020 +0200
@@ -71,13 +71,17 @@
 
 -- 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. If additional column names are given,
--- the group of given columns is tested for equality.
+-- trigger function. If additional column names are given as trigger arguments,
+-- the group of given columns is tested for equality, if no operators are
+-- given. Optionally, a column can be compared with a different operator
+-- given after the keyword "WITH" in the trigger argument.
 CREATE OR REPLACE FUNCTION prevent_st_equals() RETURNS trigger AS
 $$
 DECLARE
     new_geom geometry;
     tg_arg text;
+    col varchar;
+    opr varchar;
     filters text;
     has_equal boolean;
 BEGIN
@@ -86,7 +90,11 @@
         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);
+        -- or with operator given after keyword "WITH"
+        tg_arg = lower(tg_arg);
+        col = split_part(tg_arg, ' with ', 1);
+        opr = COALESCE(NULLIF(split_part(tg_arg, ' with ', 2), ''), '=');
+        filters = format('%s AND %I %s $2.%2$I', filters, col, opr);
     END LOOP;
     EXECUTE format(
             'SELECT EXISTS(SELECT 1 FROM %I.%I '
@@ -632,7 +640,9 @@
     )
     CREATE CONSTRAINT TRIGGER waterway_axis_wtwaxs_unique
         AFTER INSERT OR UPDATE OF wtwaxs, validity ON waterway_axis
-        FOR EACH ROW EXECUTE FUNCTION prevent_st_equals('wtwaxs', 'validity')
+        DEFERRABLE INITIALLY DEFERRED
+        FOR EACH ROW
+        EXECUTE FUNCTION prevent_st_equals('wtwaxs', 'validity WITH &&')
     CREATE INDEX waterway_axis_validity
         ON waterway_axis USING GiST (validity)