Mercurial > gemma

annotate schema/updates/1437/01.historicise_fairway_dimensions.sql @ 5560:f2204f91d286

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
Join the log lines of imports to the log exports to recover data from them. Used in SR export to extract information that where in the meta json but now are only found in the log.
author Sascha L. Teichmann <sascha.teichmann@intevation.de>
date Wed, 09 Feb 2022 18:34:40 +0100
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);