Mercurial > gemma
annotate schema/updates/1437/01.historicise_fairway_dimensions.sql @ 5259:680be197844d
Merged branch new-fwa.
author | Sascha Wilde <wilde@intevation.de> |
---|---|
date | Wed, 13 May 2020 11:28:34 +0200 |
parents | 1cb5fca140e2 |
children |
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); |