Mercurial > gemma

annotate schema/updates/1405/01.add_fm_validity.sql @ 5267:aca4bf7af270

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
client: remove mapState from import statement
author Fadi Abbud <fadi.abbud@intevation.de>
date Wed, 10 Jun 2020 16:33:10 +0200
parents 7cc79c65a9e5
children
Ignore whitespace changes - Everywhere: Within whitespace: At end of lines:
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;