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
|
-- Remove old version of table_log_init()
DROP FUNCTION table_log_init(int, text, text, text, text);
CREATE FUNCTION table_log_basic()
RETURNS TRIGGER
AS 'MODULE_PATHNAME' 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;
|
