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