Mercurial > gemma
annotate schema/updates/1405/01.add_fm_validity.sql @ 5540:4d815f295e57 aggregate-gm-import-logging
WIP: Removed more TODOs.
author | Sascha L. Teichmann <sascha.teichmann@intevation.de> |
---|---|
date | Tue, 26 Oct 2021 01:15:30 +0200 |
parents | 7cc79c65a9e5 |
children |
rev | line source |
---|---|
4956
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
1 CREATE TABLE waterway.fairway_marks_new ( |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
2 validity tstzrange NOT NULL DEFAULT tstzrange(current_timestamp, NULL) |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
3 CHECK (NOT isempty(validity)), |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
4 last_found timestamp with time zone NOT NULL DEFAULT current_timestamp, |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
5 LIKE waterway.fairway_marks |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
6 ); |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
7 |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
8 DO LANGUAGE plpgsql |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
9 $$ |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
10 DECLARE table_suffix varchar; |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
11 BEGIN |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
12 /* Re-write tables inheriting from fairway_marks to include new columns |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
13 in correct position */ |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
14 FOREACH table_suffix IN ARRAY ARRAY[ |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
15 'bcnlat', 'boycar', 'boylat', 'boysaw', 'boyspp', |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
16 'daymar', 'lights', 'rtpbcn', 'topmar', 'notmrk'] |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
17 LOOP |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
18 EXECUTE format('CREATE TABLE waterway.fairway_marks_%s_new ' |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
19 '(LIKE waterway.fairway_marks_%1$s INCLUDING ALL) ' |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
20 'INHERITS (waterway.fairway_marks_new)', table_suffix); |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
21 /* Drop index referring to wrong columns. See |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
22 https://www.postgresql.org/message-id/flat/16272-6e32da020e9a9381%40postgresql.org |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
23 */ |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
24 EXECUTE format('DROP INDEX waterway.fairway_marks_%s_new_row_idx', |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
25 table_suffix); |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
26 EXECUTE format('INSERT INTO waterway.fairway_marks_%s_new ' |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
27 'SELECT tstzrange(current_timestamp, NULL), current_timestamp, * ' |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
28 'FROM waterway.fairway_marks_%1$s', table_suffix); |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
29 EXECUTE format('DROP TABLE waterway.fairway_marks_%s CASCADE', |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
30 table_suffix); |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
31 EXECUTE format('ALTER TABLE waterway.fairway_marks_%s_new ' |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
32 'RENAME TO fairway_marks_%1$s', table_suffix); |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
33 END LOOP; |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
34 |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
35 /* Recreate foreign key constraints */ |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
36 FOREACH table_suffix IN ARRAY ARRAY['bcnlat', 'daymar', 'notmrk'] |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
37 LOOP |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
38 EXECUTE format('ALTER TABLE waterway.fairway_marks_%1$s_dirimps ' |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
39 'ADD FOREIGN KEY (fm_%1$s_id) ' |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
40 'REFERENCES waterway.fairway_marks_%1$s', table_suffix); |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
41 END LOOP; |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
42 END; |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
43 $$; |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
44 |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
45 DROP TABLE waterway.fairway_marks; |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
46 ALTER TABLE waterway.fairway_marks_new RENAME TO fairway_marks; |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
47 |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
48 -- Restore GRANTs |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
49 GRANT SELECT on ALL tables in schema waterway TO waterway_user ; |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
50 GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA waterway |
7cc79c65a9e5
Keep the history of fairway marks
Tom Gottfried <tom@intevation.de>
parents:
diff
changeset
|
51 TO waterway_admin; |