Mercurial > gemma

annotate 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
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
5141
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
1 CREATE OR REPLACE FUNCTION prevent_st_equals() RETURNS trigger AS
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
2 $$
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
3 DECLARE
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
4 new_geom geometry;
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
5 tg_arg text;
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
6 col varchar;
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
7 opr varchar;
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
8 filters text;
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
9 has_equal boolean;
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
10 BEGIN
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
11 EXECUTE format('SELECT CAST($1.%I AS geometry)', TG_ARGV[0])
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
12 INTO new_geom
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
13 USING NEW;
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
14 FOREACH tg_arg IN ARRAY TG_ARGV[1:] LOOP
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
15 tg_arg = lower(tg_arg);
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
16 col = split_part(tg_arg, ' with ', 1);
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
17 opr = COALESCE(NULLIF(split_part(tg_arg, ' with ', 2), ''), '=');
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
18 filters = format('%s AND %I %s $2.%2$I', filters, col, opr);
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
19 END LOOP;
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
20 EXECUTE format(
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
21 'SELECT EXISTS(SELECT 1 FROM %I.%I '
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
22 'WHERE id <> $2.id AND ST_Equals($1, CAST(%I AS geometry))'
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
23 '%s)',
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
24 TG_TABLE_SCHEMA, TG_TABLE_NAME, TG_ARGV[0], filters)
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
25 INTO has_equal
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
26 USING new_geom, NEW;
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
27 IF has_equal THEN
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
28 RAISE EXCEPTION
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
29 'new row for relation "%" violates constraint trigger "%"',
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
30 TG_TABLE_NAME, TG_NAME
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
31 USING
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
32 DETAIL = format('Failing row contains geometry in %s',
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
33 Box2D(new_geom)),
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
34 ERRCODE = 23505,
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
35 SCHEMA = TG_TABLE_SCHEMA,
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
36 TABLE = TG_TABLE_NAME,
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
37 COLUMN = TG_ARGV[0],
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
38 CONSTRAINT = TG_NAME;
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
39 END IF;
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
40 RETURN NEW;
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
41 END;
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
42 $$
722b7c305319 Prevent equal axis geometries with intersecting validity
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
43 LANGUAGE plpgsql;