Mercurial > gemma
view schema/updates/1103/01.fix_constraint_error_codes.sql @ 4484:819eafe60f41
Merged
author | Sascha Wilde <wilde@intevation.de> |
---|---|
date | Thu, 26 Sep 2019 12:16:23 +0200 |
parents | ec8438712447 |
children |
line wrap: on
line source
-- This is Free Software under GNU Affero General Public License v >= 3.0 -- without warranty, see README.md and license for details. -- SPDX-License-Identifier: AGPL-3.0-or-later -- License-Filename: LICENSES/AGPL-3.0.txt -- Copyright (C) 2019 by via donau -- – Österreichische Wasserstraßen-Gesellschaft mbH -- Software engineering by Intevation GmbH -- Author(s): -- * Sascha Wilde <sascha.wilde@intevation.de> -- -- CONSTRAINT FUNCTIONS -- -- The only change in the following functions is the error code -- returend in case of failure: it should be -- 23503: foreign_key_violation -- as what we are emulating is kind of an foreign key... CREATE OR REPLACE FUNCTION check_valid_gauge() RETURNS trigger AS $$ DECLARE -- FIXME: I'm using text for the isrs code and cast it on demand. -- If someone is able to get it to work with isrs or isrs_base as -- type, feel free to show me how its done... ;-) [sw] referenced_gauge text; new_validity tstzrange; BEGIN EXECUTE format('SELECT $1.%I', TG_ARGV[0]) INTO referenced_gauge USING NEW; EXECUTE format('SELECT $1.%I', TG_ARGV[1]) INTO new_validity USING NEW; IF EXISTS ( SELECT * FROM waterway.gauges WHERE location = referenced_gauge::isrs AND validity && new_validity ) THEN RETURN NEW; ELSE RAISE EXCEPTION 'new row for relation "%" violates constraint trigger "%"', TG_TABLE_NAME, TG_NAME USING DETAIL = format('No matching gauge %s found.', isrs_AsText(referenced_gauge::isrs)), ERRCODE = 23503, SCHEMA = TG_TABLE_SCHEMA, TABLE = TG_TABLE_NAME, COLUMN = TG_ARGV[0], CONSTRAINT = TG_NAME; END IF; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION check_valid_gauge_ts() RETURNS trigger AS $$ DECLARE -- FIXME: I'm using text for the isrs code and cast it on demand. -- If someone is able to get it to work with isrs or isrs_base as -- type, feel free to show me how its done... ;-) [sw] referenced_gauge text; new_tstz timestamptz; BEGIN EXECUTE format('SELECT $1.%I', TG_ARGV[0]) INTO referenced_gauge USING NEW; EXECUTE format('SELECT $1.%I', TG_ARGV[1]) INTO new_tstz USING NEW; IF EXISTS ( SELECT * FROM waterway.gauges WHERE location = referenced_gauge::isrs AND validity @> new_tstz ) THEN RETURN NEW; ELSE RAISE EXCEPTION 'new row for relation "%" violates constraint trigger "%"', TG_TABLE_NAME, TG_NAME USING DETAIL = format('No matching gauge %s for %s found.', (isrs_AsText(referenced_gauge::isrs)), new_tstz), ERRCODE = 23503, SCHEMA = TG_TABLE_SCHEMA, TABLE = TG_TABLE_NAME, COLUMN = TG_ARGV[0], CONSTRAINT = TG_NAME; END IF; END; $$ LANGUAGE plpgsql; CREATE OR REPLACE FUNCTION check_valid_bottleneck_ts() RETURNS trigger AS $$ DECLARE referenced_bottleneck_id text; new_tstz timestamptz; BEGIN EXECUTE format('SELECT $1.%I', TG_ARGV[0]) INTO referenced_bottleneck_id USING NEW; EXECUTE format('SELECT $1.%I', TG_ARGV[1]) INTO new_tstz USING NEW; IF EXISTS ( SELECT * FROM waterway.bottlenecks WHERE bottleneck_id = referenced_bottleneck_id AND validity @> new_tstz ) THEN RETURN NEW; ELSE RAISE EXCEPTION 'new row for relation "%" violates constraint trigger "%"', TG_TABLE_NAME, TG_NAME USING DETAIL = format('No matching bottleneck %s for %s found.', referenced_bottleneck_id, new_tstz), ERRCODE = 23503, SCHEMA = TG_TABLE_SCHEMA, TABLE = TG_TABLE_NAME, COLUMN = TG_ARGV[0], CONSTRAINT = TG_NAME; END IF; END; $$ LANGUAGE plpgsql;