Mercurial > gemma
view schema/updates/1103/01.fix_constraint_error_codes.sql @ 5095:e21cbb9768a2
Prevent duplicate fairway areas
In principal, there can be only one or no fairway area at each point
on the map. Since polygons from real data will often be topologically
inexact, just disallow equal geometries. This will also help to
avoid importing duplicates with concurrent imports, once the history
of fairway dimensions will be preserved.
author | Tom Gottfried <tom@intevation.de> |
---|---|
date | Wed, 25 Mar 2020 18:10:02 +0100 |
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;