Mercurial > gemma

annotate schema/updates/1471/01.fix-sr_in_bn_area-trigger.sql @ 5736:55892008ec96 default tip

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Fixed a bunch of corner cases in WG import.
author Sascha Wilde <wilde@sha-bang.de>
date Wed, 29 May 2024 19:02:42 +0200
parents 796196f83678
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
5656
796196f83678 Fix check_sr_in_bn_area trigger function to only use staging_done bn.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
1 CREATE OR REPLACE FUNCTION check_sr_in_bn_area() RETURNS trigger
796196f83678 Fix check_sr_in_bn_area trigger function to only use staging_done bn.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
2 LANGUAGE plpgsql
796196f83678 Fix check_sr_in_bn_area trigger function to only use staging_done bn.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
3 AS $$
796196f83678 Fix check_sr_in_bn_area trigger function to only use staging_done bn.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
4 BEGIN
796196f83678 Fix check_sr_in_bn_area trigger function to only use staging_done bn.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
5 IF NOT st_intersects((SELECT area
796196f83678 Fix check_sr_in_bn_area trigger function to only use staging_done bn.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
6 FROM waterway.bottlenecks
796196f83678 Fix check_sr_in_bn_area trigger function to only use staging_done bn.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
7 WHERE bottleneck_id = NEW.bottleneck_id
796196f83678 Fix check_sr_in_bn_area trigger function to only use staging_done bn.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
8 AND validity @> NEW.date_info::timestamptz
796196f83678 Fix check_sr_in_bn_area trigger function to only use staging_done bn.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
9 AND staging_done),
796196f83678 Fix check_sr_in_bn_area trigger function to only use staging_done bn.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
10 NEW.area)
796196f83678 Fix check_sr_in_bn_area trigger function to only use staging_done bn.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
11 THEN
796196f83678 Fix check_sr_in_bn_area trigger function to only use staging_done bn.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
12 RAISE EXCEPTION
796196f83678 Fix check_sr_in_bn_area trigger function to only use staging_done bn.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
13 'new row for relation "%" violates constraint trigger "%"',
796196f83678 Fix check_sr_in_bn_area trigger function to only use staging_done bn.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
14 TG_TABLE_NAME, TG_NAME
796196f83678 Fix check_sr_in_bn_area trigger function to only use staging_done bn.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
15 USING
796196f83678 Fix check_sr_in_bn_area trigger function to only use staging_done bn.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
16 DETAIL = 'Failing row area has no intersection with bottleneck.',
796196f83678 Fix check_sr_in_bn_area trigger function to only use staging_done bn.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
17 ERRCODE = 23514,
796196f83678 Fix check_sr_in_bn_area trigger function to only use staging_done bn.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
18 SCHEMA = TG_TABLE_SCHEMA,
796196f83678 Fix check_sr_in_bn_area trigger function to only use staging_done bn.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
19 TABLE = TG_TABLE_NAME,
796196f83678 Fix check_sr_in_bn_area trigger function to only use staging_done bn.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
20 CONSTRAINT = TG_NAME;
796196f83678 Fix check_sr_in_bn_area trigger function to only use staging_done bn.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
21 END IF;
796196f83678 Fix check_sr_in_bn_area trigger function to only use staging_done bn.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
22 RETURN NEW;
796196f83678 Fix check_sr_in_bn_area trigger function to only use staging_done bn.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
23 END;
796196f83678 Fix check_sr_in_bn_area trigger function to only use staging_done bn.
Sascha Wilde <wilde@sha-bang.de>
parents:
diff changeset
24 $$;