Mercurial > gemma
annotate schema/updates/1436/01.amend_prevent_st_equals.sql @ 5259:680be197844d
Merged branch new-fwa.
author | Sascha Wilde <wilde@intevation.de> |
---|---|
date | Wed, 13 May 2020 11:28:34 +0200 |
parents | 722b7c305319 |
children |
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; |