B5UvQTeF

1BEGIN;
2
3CREATE OR REPLACE FUNCTION pgst_suffix_table_name(table_name TEXT, suffix TEXT) RETURNS TEXT AS
4$$
5    SELECT TEXT (table_name || '_pgst_' || suffix);
6$$
7LANGUAGE sql IMMUTABLE;
8
9
10CREATE OR REPLACE FUNCTION pgst_start_for_table(table_name TEXT) RETURNS void AS
11$outer$
12DECLARE
13	data_table_name TEXT;
14	track_table_name TEXT;
15	track_table_name_func TEXT;
16BEGIN
17	data_table_name := pgst_suffix_table_name(table_name, 'data');
18	track_table_name := pgst_suffix_table_name(table_name, 'track');
19	track_table_name_func := track_table_name || '_func';
20
21	EXECUTE 'ALTER TABLE ' || quote_ident(table_name) || ' RENAME TO ' || quote_ident(data_table_name);
22
23	-- EXECUTE 'CREATE TABLE ' || quote_ident(track_table_name) || '(tracktime TIMESTAMP DEFAULT now(), table_row ' || quote_ident(data_table_name) || ')';
24	EXECUTE 'CREATE TABLE ' || quote_ident(track_table_name) || '(LIKE ' || quote_ident(data_table_name) || ' INCLUDING ALL)';
25
26	EXECUTE '
27		CREATE OR REPLACE FUNCTION ' || quote_ident(track_table_name_func) || '(table_row ' || quote_ident(data_table_name) || ') RETURNS integer AS
28		$inner$
29		BEGIN
30			BEGIN
31				INSERT INTO ' || quote_ident(track_table_name) || ' VALUES (table_row.*);
32			EXCEPTION WHEN unique_violation THEN
33				-- Do nothing
34			END;
35			RETURN 1;
36		END;
37		$inner$
38		LANGUAGE plpgsql VOLATILE COST 10000;
39	';
40
41	EXECUTE format('CREATE VIEW %1$I AS SELECT %2$I.*, %3$I(%2$I.*) FROM %2$I', table_name, data_table_name, track_table_name_func);
42END;
43$outer$ LANGUAGE plpgsql VOLATILE;
44
45
46CREATE OR REPLACE FUNCTION pgst_stop_for_table(table_name TEXT, delete_track_tables BOOLEAN) RETURNS void AS
47$$
48DECLARE
49	data_table_name TEXT;
50	track_table_name TEXT;
51	track_table_name_func TEXT;
52BEGIN
53	data_table_name := pgst_suffix_table_name(table_name, 'data');
54	track_table_name := pgst_suffix_table_name(table_name, 'track');
55	track_table_name_func := track_table_name || '_func';
56
57	EXECUTE 'DROP VIEW IF EXISTS ' || quote_ident(table_name);
58
59	EXECUTE 'DROP FUNCTION IF EXISTS ' || quote_ident(track_table_name_func) || '(table_row ' || quote_ident(data_table_name) || ')';
60
61    EXECUTE 'ALTER TABLE IF EXISTS ' || quote_ident(data_table_name) || ' RENAME TO ' || quote_ident(table_name);
62
63    IF delete_track_tables THEN
64        EXECUTE 'DROP TABLE IF EXISTS ' || quote_ident(track_table_name);
65    END IF;
66END;
67$$ LANGUAGE plpgsql VOLATILE;
68
69
70CREATE OR REPLACE FUNCTION pgst_start_for_all_tables() RETURNS void AS
71$$
72DECLARE
73	table_names CURSOR IS SELECT table_name AS name
74		FROM information_schema.tables
75		WHERE table_schema = 'public'
76		AND table_type = 'BASE TABLE'
77        	AND table_name NOT IN ('content_download', 'download_pack',  'content_download_pack', 'entitlement')
78		AND table_name NOT LIKE pgst_suffix_table_name('%', 'track');
79BEGIN
80	FOR tbl IN table_names LOOP
81        PERFORM pgst_start_for_table(tbl.name);
82	END LOOP;
83END;
84$$ LANGUAGE plpgsql VOLATILE;
85
86
87CREATE OR REPLACE FUNCTION pgst_stop_for_all_tables(delete_track_tables BOOLEAN) RETURNS void AS
88$$
89DECLARE
90	view_names CURSOR IS SELECT table_name AS name
91		FROM information_schema.tables
92		WHERE table_schema = 'public'
93		AND table_type = 'VIEW';
94BEGIN
95	FOR tbl IN view_names LOOP
96        PERFORM pgst_stop_for_table(tbl.name, delete_track_tables);
97	END LOOP;
98END;
99$$ LANGUAGE plpgsql VOLATILE;
100
101
102CREATE OR REPLACE FUNCTION pgst_swap_tracked_tables() RETURNS void AS
103$$
104DECLARE
105	track_table_names CURSOR IS SELECT table_name AS name
106		FROM information_schema.tables
107		WHERE table_schema = 'public'
108		AND table_name LIKE pgst_suffix_table_name('%', 'track');
109    regular_table_name TEXT;
110    tmp_table_name TEXT;
111BEGIN
112	FOR tbl IN track_table_names LOOP
113        regular_table_name := REPLACE(tbl.name, pgst_suffix_table_name('', 'track'), '');
114        tmp_table_name := pgst_suffix_table_name(regular_table_name, 'tmp');
115        EXECUTE 'ALTER TABLE IF EXISTS ' || quote_ident(regular_table_name) || ' RENAME TO ' || quote_ident(tmp_table_name);
116        EXECUTE 'ALTER TABLE IF EXISTS ' || quote_ident(tbl.name) || ' RENAME TO ' || quote_ident(regular_table_name);
117        EXECUTE 'ALTER TABLE IF EXISTS ' || quote_ident(tmp_table_name) || ' RENAME TO ' || quote_ident(tbl.name);
118	END LOOP;
119END;
120$$ LANGUAGE plpgsql VOLATILE;
121
122COMMIT;