Mercurial > gemma
view schema/updates/1422/01.amend_trigger_func.sql @ 5560:f2204f91d286
Join the log lines of imports to the log exports to recover data from them.
Used in SR export to extract information that where in the meta json
but now are only found in the log.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Wed, 09 Feb 2022 18:34:40 +0100 |
parents | f697a398eff4 |
children |
line wrap: on
line source
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;