Mercurial > gemma
diff 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 |
line wrap: on
line diff
--- /dev/null Thu Jan 01 00:00:00 1970 +0000 +++ b/schema/updates/1405/01.add_fm_validity.sql Wed Feb 26 12:18:15 2020 +0100 @@ -0,0 +1,51 @@ +CREATE TABLE waterway.fairway_marks_new ( + validity tstzrange NOT NULL DEFAULT tstzrange(current_timestamp, NULL) + CHECK (NOT isempty(validity)), + last_found timestamp with time zone NOT NULL DEFAULT current_timestamp, + LIKE waterway.fairway_marks +); + +DO LANGUAGE plpgsql +$$ +DECLARE table_suffix varchar; +BEGIN + /* Re-write tables inheriting from fairway_marks to include new columns + in correct position */ + FOREACH table_suffix IN ARRAY ARRAY[ + 'bcnlat', 'boycar', 'boylat', 'boysaw', 'boyspp', + 'daymar', 'lights', 'rtpbcn', 'topmar', 'notmrk'] + LOOP + EXECUTE format('CREATE TABLE waterway.fairway_marks_%s_new ' + '(LIKE waterway.fairway_marks_%1$s INCLUDING ALL) ' + 'INHERITS (waterway.fairway_marks_new)', table_suffix); + /* Drop index referring to wrong columns. See + https://www.postgresql.org/message-id/flat/16272-6e32da020e9a9381%40postgresql.org + */ + EXECUTE format('DROP INDEX waterway.fairway_marks_%s_new_row_idx', + table_suffix); + EXECUTE format('INSERT INTO waterway.fairway_marks_%s_new ' + 'SELECT tstzrange(current_timestamp, NULL), current_timestamp, * ' + 'FROM waterway.fairway_marks_%1$s', table_suffix); + EXECUTE format('DROP TABLE waterway.fairway_marks_%s CASCADE', + table_suffix); + EXECUTE format('ALTER TABLE waterway.fairway_marks_%s_new ' + 'RENAME TO fairway_marks_%1$s', table_suffix); + END LOOP; + + /* Recreate foreign key constraints */ + FOREACH table_suffix IN ARRAY ARRAY['bcnlat', 'daymar', 'notmrk'] + LOOP + EXECUTE format('ALTER TABLE waterway.fairway_marks_%1$s_dirimps ' + 'ADD FOREIGN KEY (fm_%1$s_id) ' + 'REFERENCES waterway.fairway_marks_%1$s', table_suffix); + END LOOP; +END; +$$; + +DROP TABLE waterway.fairway_marks; +ALTER TABLE waterway.fairway_marks_new RENAME TO fairway_marks; + +-- Restore GRANTs +GRANT SELECT on ALL tables in schema waterway TO waterway_user ; +GRANT INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA waterway + TO waterway_admin;