Mercurial > gemma

comparison schema/updates/1422/01.amend_trigger_func.sql @ 5005:f697a398eff4 wmst-config

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Amend trigger function Additional column names given as arguments to the trigger function will be tested for equality along with the geometry. This allows to implement behavior similar to a multi-column unique constraint (in preparation to store historic versions of axis segments with differing temporal validity). Using EXISTS instead of the aggregate should be more efficient.
author Tom Gottfried <tom@intevation.de>
date Wed, 11 Mar 2020 11:24:20 +0100
parents
children
comparison
equal deleted inserted replaced
5004:f74e47c0c919 5005:f697a398eff4
1 CREATE OR REPLACE FUNCTION prevent_st_equals() RETURNS trigger AS
2 $$
3 DECLARE
4 new_geom geometry;
5 tg_arg text;
6 filters text;
7 has_equal boolean;
8 BEGIN
9 EXECUTE format('SELECT CAST($1.%I AS geometry)', TG_ARGV[0])
10 INTO new_geom
11 USING NEW;
12 FOREACH tg_arg IN ARRAY TG_ARGV[1:] LOOP
13 -- Test each additional argument for equality
14 filters = format('%s AND %2$I = $2.%2$I', filters, tg_arg);
15 END LOOP;
16 EXECUTE format(
17 'SELECT EXISTS(SELECT 1 FROM %I.%I '
18 'WHERE id <> $2.id AND ST_Equals($1, CAST(%I AS geometry))'
19 '%s)',
20 TG_TABLE_SCHEMA, TG_TABLE_NAME, TG_ARGV[0], filters)
21 INTO has_equal
22 USING new_geom, NEW;
23 IF has_equal THEN
24 RAISE EXCEPTION
25 'new row for relation "%" violates constraint trigger "%"',
26 TG_TABLE_NAME, TG_NAME
27 USING
28 DETAIL = format('Failing row contains geometry in %s',
29 Box2D(new_geom)),
30 ERRCODE = 23505,
31 SCHEMA = TG_TABLE_SCHEMA,
32 TABLE = TG_TABLE_NAME,
33 COLUMN = TG_ARGV[0],
34 CONSTRAINT = TG_NAME;
35 END IF;
36 RETURN NEW;
37 END;
38 $$
39 LANGUAGE plpgsql;