Mercurial > gemma

comparison schema/gemma.sql @ 4897:638a61fb881b fairway-marks-import

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Merge branch default into fairway-marks-import
author Tom Gottfried <tom@intevation.de>
date Fri, 31 Jan 2020 16:53:06 +0100
parents dd888bbb7e43 566e9ee70d45
children 6f244b5eb716
comparison
equal deleted inserted replaced
4896:dd888bbb7e43 4897:638a61fb881b
25 -- 25 --
26 -- Functions to be used in CHECK constraints 26 -- Functions to be used in CHECK constraints
27 -- 27 --
28 28
29 -- Check if a given string can be used as a FROM item in an SQL statement 29 -- Check if a given string can be used as a FROM item in an SQL statement
30 -- All objects in stmt should be schema qualified if not in the public schema
31 CREATE OR REPLACE FUNCTION is_valid_from_item(stmt text) RETURNS boolean 30 CREATE OR REPLACE FUNCTION is_valid_from_item(stmt text) RETURNS boolean
32 AS $$ 31 AS $$
33 BEGIN 32 BEGIN
34 EXECUTE format('SELECT * FROM (%s) AS test', stmt); 33 EXECUTE format('SELECT * FROM (%s) AS test', stmt);
35 RETURN true; 34 RETURN true;
37 WHEN OTHERS THEN 36 WHEN OTHERS THEN
38 RAISE NOTICE USING MESSAGE = SQLERRM, ERRCODE = SQLSTATE; 37 RAISE NOTICE USING MESSAGE = SQLERRM, ERRCODE = SQLSTATE;
39 RETURN false; 38 RETURN false;
40 END 39 END
41 $$ 40 $$
42 SET search_path TO 'public'
43 LANGUAGE plpgsql 41 LANGUAGE plpgsql
44 STRICT; 42 STRICT;
45 43
46 44
47 -- 45 --
229 -- name is to ensure, it is fired last after other triggers. 227 -- name is to ensure, it is fired last after other triggers.
230 CREATE OR REPLACE FUNCTION check_sr_in_bn_area() RETURNS trigger 228 CREATE OR REPLACE FUNCTION check_sr_in_bn_area() RETURNS trigger
231 LANGUAGE plpgsql 229 LANGUAGE plpgsql
232 AS $$ 230 AS $$
233 BEGIN 231 BEGIN
234 IF NOT public.ST_Intersects((SELECT area 232 IF NOT st_intersects((SELECT area
235 FROM waterway.bottlenecks 233 FROM waterway.bottlenecks
236 WHERE bottleneck_id = NEW.bottleneck_id 234 WHERE bottleneck_id = NEW.bottleneck_id
237 AND validity @> NEW.date_info::timestamptz), 235 AND validity @> NEW.date_info::timestamptz),
238 NEW.area) 236 NEW.area)
239 THEN 237 THEN