Mercurial > gemma

comparison schema/gemma.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 4a816ecf70de
children e8b2dc771f9e
comparison
equal deleted inserted replaced
5004:f74e47c0c919 5005:f697a398eff4
69 to be applied. 69 to be applied.
70 */ 70 */
71 71
72 -- Trigger function to be used as a constraint trigger to enforce uniqueness 72 -- Trigger function to be used as a constraint trigger to enforce uniqueness
73 -- of geometries in the column with its name given as an argument to the 73 -- of geometries in the column with its name given as an argument to the
74 -- trigger function 74 -- trigger function. If additional column names are given,
75 -- the group of given columns is tested for equality.
75 CREATE OR REPLACE FUNCTION prevent_st_equals() RETURNS trigger AS 76 CREATE OR REPLACE FUNCTION prevent_st_equals() RETURNS trigger AS
76 $$ 77 $$
77 DECLARE 78 DECLARE
78 new_geom geometry; 79 new_geom geometry;
80 tg_arg text;
81 filters text;
79 has_equal boolean; 82 has_equal boolean;
80 BEGIN 83 BEGIN
81 EXECUTE format('SELECT CAST($1.%I AS geometry)', TG_ARGV[0]) 84 EXECUTE format('SELECT CAST($1.%I AS geometry)', TG_ARGV[0])
82 INTO new_geom 85 INTO new_geom
83 USING NEW; 86 USING NEW;
87 FOREACH tg_arg IN ARRAY TG_ARGV[1:] LOOP
88 -- Test each additional argument for equality
89 filters = format('%s AND %2$I = $2.%2$I', filters, tg_arg);
90 END LOOP;
84 EXECUTE format( 91 EXECUTE format(
85 'SELECT bool_or(ST_Equals($1, CAST(%I AS geometry))) FROM %I.%I ' 92 'SELECT EXISTS(SELECT 1 FROM %I.%I '
86 'WHERE id <> $2', 93 'WHERE id <> $2.id AND ST_Equals($1, CAST(%I AS geometry))'
87 TG_ARGV[0], TG_TABLE_SCHEMA, TG_TABLE_NAME) 94 '%s)',
95 TG_TABLE_SCHEMA, TG_TABLE_NAME, TG_ARGV[0], filters)
88 INTO has_equal 96 INTO has_equal
89 USING new_geom, NEW.id; 97 USING new_geom, NEW;
90 IF has_equal THEN 98 IF has_equal THEN
91 RAISE EXCEPTION 99 RAISE EXCEPTION
92 'new row for relation "%" violates constraint trigger "%"', 100 'new row for relation "%" violates constraint trigger "%"',
93 TG_TABLE_NAME, TG_NAME 101 TG_TABLE_NAME, TG_NAME
94 USING 102 USING