Mercurial > gemma
changeset 3746:433bad131e5c
Added check trigger to sounding results to ensure they intersect the bn-area.
author | Sascha Wilde <wilde@intevation.de> |
---|---|
date | Mon, 24 Jun 2019 16:13:05 +0200 |
parents | ae79c9546908 |
children | ad67e4286d65 |
files | schema/gemma.sql |
diffstat | 1 files changed, 30 insertions(+), 0 deletions(-) [+] |
line wrap: on
line diff
--- a/schema/gemma.sql Mon Jun 24 15:46:13 2019 +0200 +++ b/schema/gemma.sql Mon Jun 24 16:13:05 2019 +0200 @@ -178,6 +178,33 @@ $$ LANGUAGE plpgsql; +-- Constraint trigger: sounding Results must intersect with the area +-- of the bottleneck they belong to. +CREATE OR REPLACE FUNCTION check_sr_in_bn_area() RETURNS trigger +LANGUAGE plpgsql +AS $$ +BEGIN + IF NOT st_intersects((SELECT area + FROM waterway.bottlenecks + WHERE (bottleneck_id, validity) + =(NEW.bottleneck_id, NEW.bottleneck_validity)), + NEW.area) + THEN + RAISE EXCEPTION + 'new row for relation "%" violates constraint trigger "%"', + TG_TABLE_NAME, TG_NAME + USING + DETAIL = 'Failing row area has no intersection with bottleneck.', + ERRCODE = 23514, + SCHEMA = TG_TABLE_SCHEMA, + TABLE = TG_TABLE_NAME, + CONSTRAINT = TG_NAME; + END IF; + RETURN NEW; +END; +$$; + + -- -- GEMMA data -- @@ -663,6 +690,9 @@ octree_index bytea, staging_done boolean NOT NULL DEFAULT false ) + CREATE CONSTRAINT TRIGGER sounding_results_in_bn_area + AFTER INSERT OR UPDATE ON sounding_results + FOR EACH ROW EXECUTE FUNCTION check_sr_in_bn_area(); CREATE TABLE sounding_results_contour_lines ( sounding_result_id int NOT NULL REFERENCES sounding_results