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;