1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
|
--
-- table_log () -- log changes to another table
--
--
-- see README.md for details
--
--
-- written by Andreas ' ads' Scherbaum (ads@pgug.de)
--
--
-- create function
CREATE FUNCTION table_log_basic()
RETURNS TRIGGER
AS 'MODULE_PATHNAME' LANGUAGE C;
CREATE FUNCTION table_log ()
RETURNS TRIGGER
AS 'MODULE_PATHNAME' LANGUAGE C;
CREATE FUNCTION "table_log_restore_table" (VARCHAR, VARCHAR, CHAR, CHAR, CHAR, TIMESTAMPTZ, CHAR, INT, INT)
RETURNS VARCHAR
AS 'MODULE_PATHNAME', 'table_log_restore_table' LANGUAGE C;
CREATE FUNCTION "table_log_restore_table" (VARCHAR, VARCHAR, CHAR, CHAR, CHAR, TIMESTAMPTZ, CHAR, INT)
RETURNS VARCHAR
AS 'MODULE_PATHNAME', 'table_log_restore_table' LANGUAGE C;
CREATE FUNCTION "table_log_restore_table" (VARCHAR, VARCHAR, CHAR, CHAR, CHAR, TIMESTAMPTZ, CHAR)
RETURNS VARCHAR
AS 'MODULE_PATHNAME', 'table_log_restore_table' LANGUAGE C;
CREATE FUNCTION "table_log_restore_table" (VARCHAR, VARCHAR, CHAR, CHAR, CHAR, TIMESTAMPTZ)
RETURNS VARCHAR
AS 'MODULE_PATHNAME', 'table_log_restore_table' LANGUAGE C;
CREATE OR REPLACE FUNCTION table_log_init(int, text, text, text, text, text DEFAULT 'SINGLE',
boolean DEFAULT false,
text[] DEFAULT '{INSERT, UPDATE, DELETE}'::text[]) RETURNS void AS
$table_log_init$
DECLARE
level ALIAS FOR $1;
orig_schema ALIAS FOR $2;
orig_name ALIAS FOR $3;
log_schema ALIAS FOR $4;
log_name ALIAS FOR $5;
partition_mode ALIAS FOR $6;
basic_mode ALIAS FOR $7;
log_actions ALIAS FOR $8;
do_log_user int = 0;
level_create text = '';
orig_qq text;
log_qq text;
log_part text[];
log_seq text;
num_log_tables integer;
trigger_func text := 'table_log';
trigger_actions text := '';
i integer;
BEGIN
-- Handle if someone doesn't want an explicit log table name
log_name := COALESCE(log_name, orig_name || '_log');
-- Quoted qualified names
orig_qq := quote_ident(orig_schema) || '.' || quote_ident(orig_name);
log_qq := quote_ident(log_schema) || '.' || quote_ident(log_name);
log_seq := quote_ident(log_schema) || '.' || quote_ident(log_name || '_seq');
log_part[0] := quote_ident(log_schema) || '.' || quote_ident(log_name || '_0');
log_part[1] := quote_ident(log_schema) || '.' || quote_ident(log_name || '_1');
-- Valid trigger actions?
IF (COALESCE(array_length(log_actions, 1), 0) = 0) THEN
RAISE EXCEPTION 'table_log_init: at least one trigger action must be specified';
END IF;
-- Valid partition mode ?
IF (partition_mode NOT IN ('SINGLE', 'PARTITION')) THEN
RAISE EXCEPTION 'table_log_init: unsupported partition mode %', partition_mode;
END IF;
IF level <> 3 THEN
--
-- Create a sequence used by trigger_id, if requested.
--
EXECUTE 'CREATE SEQUENCE ' || log_seq;
level_create := level_create
|| ', trigger_id BIGINT'
|| ' DEFAULT nextval($$' || log_seq || '$$::regclass)'
|| ' NOT NULL PRIMARY KEY';
IF level <> 4 THEN
level_create := level_create
|| ', trigger_user VARCHAR(32) NOT NULL';
do_log_user := 1;
IF level <> 5 THEN
RAISE EXCEPTION
'table_log_init: First arg has to be 3, 4 or 5.';
END IF;
END IF;
END IF;
IF (partition_mode = 'SINGLE') THEN
EXECUTE 'CREATE TABLE ' || log_qq
|| '(LIKE ' || orig_qq
|| ', trigger_mode VARCHAR(10) NOT NULL'
|| ', trigger_tuple VARCHAR(5) NOT NULL'
|| ', trigger_changed TIMESTAMPTZ NOT NULL'
|| level_create
|| ')';
ELSE
-- Partitioned mode requested...
EXECUTE 'CREATE TABLE ' || log_part[0]
|| '(LIKE ' || orig_qq
|| ', trigger_mode VARCHAR(10) NOT NULL'
|| ', trigger_tuple VARCHAR(5) NOT NULL'
|| ', trigger_changed TIMESTAMPTZ NOT NULL'
|| level_create
|| ')';
EXECUTE 'CREATE TABLE ' || log_part[1]
|| '(LIKE ' || orig_qq
|| ', trigger_mode VARCHAR(10) NOT NULL'
|| ', trigger_tuple VARCHAR(5) NOT NULL'
|| ', trigger_changed TIMESTAMPTZ NOT NULL'
|| level_create
|| ')';
EXECUTE 'CREATE VIEW ' || log_qq
|| ' AS SELECT * FROM ' || log_part[0] || ' UNION ALL '
|| 'SELECT * FROM ' || log_part[1] || '';
END IF;
--
-- Either use basic or full trigger mode
--
IF basic_mode THEN
trigger_func := 'table_log_basic';
END IF;
--
-- Build action string for trigger DDL
--
FOR i IN 1..array_length(log_actions, 1)
LOOP
trigger_actions := trigger_actions || log_actions[i];
IF i < array_length(log_actions, 1) THEN
trigger_actions := trigger_actions || ' OR ';
END IF;
END LOOP;
EXECUTE 'CREATE TRIGGER "table_log_trigger" AFTER ' || trigger_actions || ' ON '
|| orig_qq || ' FOR EACH ROW EXECUTE PROCEDURE ' || trigger_func || '('
|| quote_literal(log_name) || ','
|| do_log_user || ','
|| quote_literal(log_schema) || ','
|| quote_literal(partition_mode)
|| ')';
RETURN;
END;
$table_log_init$
LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION table_log_init(int, text) RETURNS void AS '
DECLARE
level ALIAS FOR $1;
orig_name ALIAS FOR $2;
BEGIN
PERFORM table_log_init(level, orig_name, current_schema());
RETURN;
END;
' LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION table_log_init(int, text, text) RETURNS void AS '
DECLARE
level ALIAS FOR $1;
orig_name ALIAS FOR $2;
log_schema ALIAS FOR $3;
BEGIN
PERFORM table_log_init(level, current_schema(), orig_name, log_schema);
RETURN;
END;
' LANGUAGE plpgsql;
CREATE OR REPLACE FUNCTION table_log_init(int, text, text, text) RETURNS void AS '
DECLARE
level ALIAS FOR $1;
orig_schema ALIAS FOR $2;
orig_name ALIAS FOR $3;
log_schema ALIAS FOR $4;
BEGIN
PERFORM table_log_init(level, orig_schema, orig_name, log_schema,
CASE WHEN orig_schema=log_schema
THEN orig_name||''_log'' ELSE orig_name END);
RETURN;
END;
' LANGUAGE plpgsql;
|
