Mercurial > gemma

annotate schema/updates/1437/01.historicise_fairway_dimensions.sql @ 5539:90ba92820b26 aggregate-gm-import-logging

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Merged default into aggregate-gm-import-logging branch.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Tue, 26 Oct 2021 00:05:28 +0200
parents 1cb5fca140e2
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
rev   line source
5145
1cb5fca140e2 Avoid unnecessary trigger executions
Tom Gottfried <tom@intevation.de>
parents: 5144
diff changeset
1 -- Drop trigger first to avoid execution during following operations
1cb5fca140e2 Avoid unnecessary trigger executions
Tom Gottfried <tom@intevation.de>
parents: 5144
diff changeset
2 DROP TRIGGER fairway_dimensions_area_unique ON waterway.fairway_dimensions;
1cb5fca140e2 Avoid unnecessary trigger executions
Tom Gottfried <tom@intevation.de>
parents: 5144
diff changeset
3
5144
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
4 ALTER TABLE waterway.fairway_dimensions
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
5 ADD validity tstzrange NOT NULL DEFAULT tstzrange(current_timestamp, NULL)
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
6 CHECK (NOT isempty(validity));
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
7
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
8 -- Assume existing entries have been valid since last accepted import
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
9 WITH imps AS (
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
10 SELECT changed, CAST(summary AS jsonb)->'fd-area' AS fd_area
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
11 FROM import.imports
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
12 WHERE kind = 'fd' AND state = 'accepted'
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
13 )
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
14 UPDATE waterway.fairway_dimensions fd SET validity = tstzrange(
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
15 COALESCE(
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
16 (SELECT max(changed)
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
17 FROM imps
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
18 WHERE fd.id IN(
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
19 SELECT id
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
20 FROM jsonb_to_recordset(fd_area)
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
21 AS fd_area (id bigint, lat numeric, lon numeric))),
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
22 current_timestamp),
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
23 NULL);
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
24
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
25 CREATE CONSTRAINT TRIGGER fairway_dimensions_area_unique
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
26 AFTER INSERT OR UPDATE OF area, validity, staging_done
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
27 ON waterway.fairway_dimensions
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
28 FOR EACH ROW EXECUTE FUNCTION prevent_st_equals(
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
29 'area', 'validity WITH &&', 'staging_done');
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
30
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
31 CREATE INDEX fairway_dimensions_validity
f11b9b50fcc9 Keep historic data of fairway dimensions
Tom Gottfried <tom@intevation.de>
parents:
diff changeset
32 ON waterway.fairway_dimensions USING GiST (validity);