Mercurial > gemma
view schema/updates/1436/01.amend_prevent_st_equals.sql @ 5507:279900b28b1b deactivate-users
Client: Notify user by successfully deleting/deactivating users
* Display an info-toast for the user's deleting-process.
author | Fadi Abbud <fadi.abbud@intevation.de> |
---|---|
date | Thu, 23 Sep 2021 14:47:36 +0200 |
parents | 722b7c305319 |
children |
line wrap: on
line source
CREATE OR REPLACE FUNCTION prevent_st_equals() RETURNS trigger AS $$ DECLARE new_geom geometry; tg_arg text; col varchar; opr varchar; 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 tg_arg = lower(tg_arg); col = split_part(tg_arg, ' with ', 1); opr = COALESCE(NULLIF(split_part(tg_arg, ' with ', 2), ''), '='); filters = format('%s AND %I %s $2.%2$I', filters, col, opr); 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;