Mercurial > gemma

annotate schema/updates/1100/02.remove_bottleneck_validity_refs.sql @ 4047:8c6bc85db711 historization_ng

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
WIP: Remove references to bottleneck validity in DB schema.
author Sascha Wilde <wilde@intevation.de>
date Wed, 24 Jul 2019 16:15:21 +0200
parents
children 0ba3fc89b499
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
4047
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
1 -- This is Free Software under GNU Affero General Public License v >= 3.0
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
2 -- without warranty, see README.md and license for details.
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
3
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
4 -- SPDX-License-Identifier: AGPL-3.0-or-later
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
5 -- License-Filename: LICENSES/AGPL-3.0.txt
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
6
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
7 -- Copyright (C) 2019 by via donau
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
8 -- – Österreichische Wasserstraßen-Gesellschaft mbH
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
9 -- Software engineering by Intevation GmbH
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
10
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
11 -- Author(s):
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
12 -- * Sascha Wilde <sascha.wilde@intevation.de>
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
13
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
14 --
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
15 -- CONSTRAINT FUNCTIONS
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
16 --
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
17
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
18 -- We still want to ensure, that there is at least a valid bottleneck
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
19 -- at any time of the referencing objects validity. To ensure this we
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
20 -- need a trigger constraint:
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
21 CREATE OR REPLACE FUNCTION check_valid_bottleneck_ts() RETURNS trigger AS
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
22 $$
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
23 DECLARE
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
24 referenced_bottleneck_id text;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
25 new_tstz timestamptz;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
26 BEGIN
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
27 EXECUTE format('SELECT $1.%I', TG_ARGV[0])
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
28 INTO referenced_bottleneck_id
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
29 USING NEW;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
30 EXECUTE format('SELECT $1.%I', TG_ARGV[1])
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
31 INTO new_tstz
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
32 USING NEW;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
33 IF EXISTS ( SELECT * FROM waterway.bottlenecks
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
34 WHERE bottleneck_id = referenced_bottleneck_id
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
35 AND validity @> new_tstz )
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
36 THEN
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
37 RETURN NEW;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
38 ELSE
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
39 RAISE EXCEPTION
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
40 'new row for relation "%" violates constraint trigger "%"',
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
41 TG_TABLE_NAME, TG_NAME
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
42 USING
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
43 DETAIL = format('No matching bottleneck %s for %s found.',
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
44 referenced_bottleneck_id, new_tstz),
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
45 ERRCODE = 23505,
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
46 SCHEMA = TG_TABLE_SCHEMA,
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
47 TABLE = TG_TABLE_NAME,
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
48 COLUMN = TG_ARGV[0],
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
49 CONSTRAINT = TG_NAME;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
50 END IF;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
51 END;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
52 $$
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
53 LANGUAGE plpgsql;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
54
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
55 -- Redifine constraint trigger: sounding Results must intersect with
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
56 -- the area of the bottleneck they belong to. Bottleneck is
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
57 -- determined dynamically via date_info.
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
58 CREATE OR REPLACE FUNCTION check_sr_in_bn_area() RETURNS trigger
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
59 LANGUAGE plpgsql
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
60 AS $$
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
61 BEGIN
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
62 IF NOT st_intersects((SELECT area
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
63 FROM waterway.bottlenecks
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
64 WHERE bottleneck_id = NEW.bottleneck_id
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
65 AND validity @> NEW.date_info::timestamptz),
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
66 NEW.area)
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
67 THEN
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
68 RAISE EXCEPTION
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
69 'new row for relation "%" violates constraint trigger "%"',
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
70 TG_TABLE_NAME, TG_NAME
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
71 USING
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
72 DETAIL = 'Failing row area has no intersection with bottleneck.',
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
73 ERRCODE = 23514,
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
74 SCHEMA = TG_TABLE_SCHEMA,
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
75 TABLE = TG_TABLE_NAME,
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
76 CONSTRAINT = TG_NAME;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
77 END IF;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
78 RETURN NEW;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
79 END;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
80 $$;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
81
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
82
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
83 --
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
84 -- SOUNDING RESULTS
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
85 --
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
86
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
87 -- Dynamic version of sounding_differences geoserver view: Instead of
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
88 -- a static reference to a specific bottleneck data set via primary
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
89 -- key (id, validity) we check for a bottleneck valid at the time of
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
90 -- the survey at executiuon time.
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
91 CREATE OR REPLACE VIEW waterway.sounding_differences AS
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
92 SELECT
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
93 sd.id AS id,
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
94 bn.objnam AS objnam,
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
95 srm.date_info AS minuend,
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
96 srs.date_info AS subtrahend,
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
97 sdcl.height AS height,
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
98 CAST(sdcl.lines AS geometry(multilinestring, 4326)) AS lines
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
99 FROM caching.sounding_differences sd
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
100 JOIN caching.sounding_differences_contour_lines sdcl
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
101 ON sd.id = sdcl.sounding_differences_id
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
102 JOIN waterway.sounding_results srm
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
103 ON sd.minuend = srm.id
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
104 JOIN waterway.sounding_results srs
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
105 ON sd.subtrahend = srs.id
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
106 JOIN waterway.bottlenecks bn
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
107 ON srm.bottleneck_id = bn.bottleneck_id
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
108 AND srm.date_info::timestamptz <@ bn.validity;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
109
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
110 -- As we resolve the correct gauge data to use on runtime, we drop the
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
111 -- hard reference to the bottlenecks vaidity:
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
112 ALTER TABLE waterway.sounding_results DROP IF EXISTS bottleneck_validity;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
113
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
114 -- Note, we now use prefixed names, to ensure correct execution order
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
115 -- for the triggers...
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
116 DROP TRIGGER IF EXISTS a_sounding_results_reference_bottleneck
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
117 ON waterway.sounding_results;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
118 CREATE CONSTRAINT TRIGGER a_sounding_results_reference_bottleneck
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
119 AFTER INSERT OR UPDATE OF bottleneck_id ON waterway.sounding_results
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
120 FOR EACH ROW
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
121 EXECUTE FUNCTION check_valid_bottleneck_ts('bottleneck_id','date_info');
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
122
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
123 DROP TRIGGER IF EXISTS sounding_results_in_bn_area
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
124 ON waterway.sounding_results;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
125 DROP TRIGGER IF EXISTS b_sounding_results_in_bn_area
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
126 ON waterway.sounding_results;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
127 CREATE CONSTRAINT TRIGGER b_sounding_results_in_bn_area
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
128 AFTER INSERT OR UPDATE ON waterway.sounding_results
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
129 FOR EACH ROW EXECUTE FUNCTION check_sr_in_bn_area();
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
130
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
131
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
132 --
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
133 -- DROP NO LONGER NEEDED TRIGGER FUNCTION
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
134 --
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
135
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
136 -- This used to update foreign key references. As these references no
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
137 -- longer exist we dont need this magic any more...
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
138 DROP TRIGGER IF EXISTS move_referencing ON waterway.bottlenecks;
8c6bc85db711 WIP: Remove references to bottleneck validity in DB schema.
Sascha Wilde <wilde@intevation.de>
parents:
diff changeset
139 DROP FUNCTION IF EXISTS move_bottleneck_referencing();