gemma: schema/gemma.sql comparison

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	db87f34805fb
children	ad67e4286d65

comparison

equal deleted inserted replaced

-:ae79c9546908
+:433bad131e5c
 RETURN NULL; -- ignored
 END;
 $$
 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
 --
 -- Namespace not to be accessed directly by any user
 depth_reference varchar NOT NULL, -- REFERENCES depth_references,
 octree_checksum varchar,
 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
 ON DELETE CASCADE,
 height numeric NOT NULL,

Mercurial > gemma