Mercurial > gemma

diff schema/updates/1436/01.amend_prevent_st_equals.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
children
line wrap: on
line diff
--- /dev/null	Thu Jan 01 00:00:00 1970 +0000
+++ b/schema/updates/1436/01.amend_prevent_st_equals.sql	Tue Mar 31 12:50:30 2020 +0200
@@ -0,0 +1,43 @@
+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
+    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
+        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 '
+                '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;