view schema/updates/1422/01.amend_trigger_func.sql @ 5666:37c2354a6024 clickable-links

Render links only to known bottlenecks
author Thomas Junk <thomas.junk@intevation.de>
date Tue, 05 Dec 2023 15:34:31 +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;