Mercurial > gemma

annotate schema/updates/1436/01.amend_prevent_st_equals.sql @ 5490:5f47eeea988d logging

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Use own logging package.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Mon, 20 Sep 2021 17:45:39 +0200
parents 722b7c305319
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;