Mercurial > gemma
changeset 5005:f697a398eff4 wmst-config
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 | f74e47c0c919 |
| children | 5269fd823ca7 |
| files | schema/gemma.sql schema/updates/1422/01.amend_trigger_func.sql schema/version.sql |
| diffstat | 3 files changed, 53 insertions(+), 6 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/gemma.sql Wed Mar 11 10:59:34 2020 +0100 +++ b/schema/gemma.sql Wed Mar 11 11:24:20 2020 +0100 @@ -71,22 +71,30 @@ -- Trigger function to be used as a constraint trigger to enforce uniqueness -- of geometries in the column with its name given as an argument to the --- trigger function +-- trigger function. If additional column names are given, +-- the group of given columns is tested for equality. CREATE OR REPLACE FUNCTION prevent_st_equals() RETURNS trigger AS $$ DECLARE new_geom geometry; + tg_arg text; + 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 + -- Test each additional argument for equality + filters = format('%s AND %2$I = $2.%2$I', filters, tg_arg); + END LOOP; EXECUTE format( - 'SELECT bool_or(ST_Equals($1, CAST(%I AS geometry))) FROM %I.%I ' - 'WHERE id <> $2', - TG_ARGV[0], TG_TABLE_SCHEMA, TG_TABLE_NAME) + '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.id; + USING new_geom, NEW; IF has_equal THEN RAISE EXCEPTION 'new row for relation "%" violates constraint trigger "%"',
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1422/01.amend_trigger_func.sql Wed Mar 11 11:24:20 2020 +0100 @@ -0,0 +1,39 @@ +CREATE OR REPLACE FUNCTION prevent_st_equals() RETURNS trigger AS +$$ +DECLARE + new_geom geometry; + tg_arg text; + 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 + -- Test each additional argument for equality + filters = format('%s AND %2$I = $2.%2$I', filters, tg_arg); + 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;