Mercurial > gemma
view schema/updates/1405/01.add_fm_validity.sql @ 5650:cb3c4ba0f052 DQR adjustments
Merged default branch
author | Sascha Wilde <wilde@sha-bang.de> |
---|---|
date | Thu, 29 Jun 2023 13:18:54 +0200 |
parents | 7cc79c65a9e5 |
children |
line wrap: on
line source
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;