make_history.sql
7.27 KB
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
CREATE OR REPLACE FUNCTION make_history_()
RETURNS TRIGGER
LANGUAGE plpgsql
AS
$BODY$
/* Purpose: Make a history of changes to most fields in the table calling this trigger function.
This kind of history tracking is also known as an "audit trail".
This function works by detecting each change in value for important fields in a certain table.
This trigger function then calls another function to create a row in the "history_" table.
This kind of feature is often called an "audit trail" by software developers. I avoid using that term in this context as a real
audit trail in accounting terms involves more than this simple field change tracking.
*/
/* © 2011 Basil Bourque. This source code may be used freely forever by anyone taking full responsibility for doing so, without warranty.
Thanks so very much to John DeSoi of the pgsql-general@postgresql.org mailing list for pointing me to this crucial code example:
http://wiki.postgresql.org/wiki/PL/pgSQL_Dynamic_Triggers
Before reading that example, my previous efforts led me to conclude a generic history facility written in PL/pgSQL was impossible.
*/
/* We make these assumptions about any table using this function in its trigger:
• Has a primary key named "pkey_" of type uuid.
• Has a field tracking the datetime the record was last modified, named "record_modified_" of type timestamptz.
• Is in the default/current schema.
While it might be nice to rewrite this function to escape these assumptions, I've spent all my energies to get this far.
I welcome feedback from anyone who want to take this further.
*/
/*
For each table on which you want history, create a trigger by executing SQL like this:
CREATE TRIGGER XXX_trigger_history_
AFTER INSERT OR UPDATE OR DELETE ON XXX_
FOR EACH ROW EXECUTE PROCEDURE make_history_();
where XXX is the name of the specific table.
*/
/* Notes:
The 'OLD' and 'NEW' variables represent the entire row whose INSERT/UPDATE/DELETE caused this trigger to run.
The 'TG_xxx' variables are special variables created automatically by Postgres for the trigger function.
For example, TG_OP indicates which modification operation is happening: INSERT, UPDATE, or DELETE.
http://www.postgresql.org/docs/current/static/plpgsql-trigger.html
"clock_timestamp()" gets the actual time at the moment of execution. In contrast, most other timestamp
functions return the time when the current transaction began.
For more information, see: http://www.postgresql.org/docs/current/static/functions-datetime.html
The "history_" table also includes a column "transaction_began_" defaulting to "transaction_timestamp()". This timestamp can act
like a transaction identifier, to group multiple "history_" rows of the same transaction together. This is not foolproof, as
multiple transaction could possibly start in the same split second moment. Assuming the computer's clock has a fine resolution,
this chance of a coincidence should be quite miniscule. If someone knows a way to get a true transaction id, please share.
*/
/* History:
2011-04-31 • Published on the general Postgres mailing list.
2011-05-01 • Revised to not ignore the ".record_created_" field.
• Published on my blog at http://crafted-software.blogspot.com/.
*/
DECLARE
ri RECORD; -- About this data type "RECORD": http://www.postgresql.org/docs/current/static/plpgsql-declarations.html#PLPGSQL-DECLARATION-RECORDS
oldValue TEXT;
newValue TEXT;
isColumnSignificant BOOLEAN;
isValueModified BOOLEAN;
lexemeId INTEGER;
patternId INTEGER;
temp RECORD;
userId TEXT;
BEGIN
/*RAISE NOTICE E'\n Running function: make_history_ ----------------\n\n Operation: %\n Schema: %\n Table: %\n',
TG_OP,
TG_TABLE_SCHEMA,
TG_TABLE_NAME;*/
IF (TG_OP = 'DELETE') THEN
temp := OLD;
ELSE
temp := NEW;
END IF;
SELECT current_setting('var.user_id') INTO userId;
IF (userId = '0') THEN
RETURN temp;
END IF;
IF (TG_TABLE_NAME = 'leksemy') THEN
lexemeId := temp.id;
ELSIF (TG_TABLE_NAME IN ('odmieniasie', 'leksemy_w_slownikach')) THEN
lexemeId := temp.l_id;
ELSIF (TG_TABLE_NAME IN ('kwalifikatory_leksemow', 'wartosci_klasyfikacji_lexemes')) THEN
lexemeId := temp.lexeme_id;
ELSIF (TG_TABLE_NAME = 'odsylacze') THEN
lexemeId := temp.l_id_od;
ELSIF (TG_TABLE_NAME = 'kwalifikatory_odmieniasiow') THEN
SELECT l_id INTO lexemeId FROM odmieniasie WHERE id = temp.lexemeinflectionpattern_id;
END IF;
IF (TG_TABLE_NAME = 'wzory') THEN
patternId := temp.id;
ELSIF (TG_TABLE_NAME = 'zakonczenia') THEN
patternId := temp.w_id;
ELSIF (TG_TABLE_NAME = 'kwalifikatory_zakonczen') THEN
SELECT w_id INTO patternId FROM zakonczenia WHERE id = temp.ending_id;
END IF;
FOR ri IN
-- Fetch a ResultSet listing columns defined for this trigger's table.
SELECT ordinal_position, column_name, data_type
FROM information_schema.columns
WHERE
table_schema = quote_ident(TG_TABLE_SCHEMA)
AND table_name = quote_ident(TG_TABLE_NAME)
ORDER BY ordinal_position
LOOP
-- For each column in this trigger's table, copy the OLD & NEW values into respective variables.
-- NEW value
IF (TG_OP = 'DELETE') THEN
newValue := ''::varchar;
ELSE
EXECUTE 'SELECT ($1).' || ri.column_name || '::text' INTO newValue USING NEW;
END IF;
-- OLD value
IF (TG_OP = 'INSERT') THEN
oldValue := ''::varchar;
ELSE
EXECUTE 'SELECT ($1).' || ri.column_name || '::text' INTO oldValue USING OLD;
END IF;
-- Make noise for debugging.
/*RAISE NOTICE E'\n Column #: %\n Name: %\n Type: %\n Old: %\n New: %\n',
ri.ordinal_position,
ri.column_name,
ri.data_type,
oldValue,
newValue;*/
-- ToDo: Add code to throw an Exception if the primary key value is changing (other than from NULL on an INSERT).
-- ToDo: Add code to ignore columns whose data type does not cast well to TEXT/VARCHAR.
-- Ignore some columns:
-- • Those whose names are marked with a trailing x.
-- • The primary key.
-- • Our timestamp field recording the row's most recent modification.
isColumnSignificant := (position( '_x_' in ri.column_name ) < 1) AND (ri.column_name <> 'id') AND (ri.column_name <> 'data_modyfikacji');
IF isColumnSignificant THEN
isValueModified := oldValue <> newValue; -- If this nthField in the table was modified, make history.
IF isValueModified THEN
/*RAISE NOTICE E'Inserting history_ row.\n';*/
INSERT INTO history ( operation_, table_oid_, table_name_, id_, column_name_, ordinal_position_of_column_, old_value_, new_value_, lexeme_id_, pattern_id_ )
VALUES ( TG_OP, TG_RELID, TG_TABLE_NAME, temp.id, ri.column_name::VARCHAR, ri.ordinal_position, oldValue::text, newValue::text, lexemeId, patternId );
END IF;
END IF;
END LOOP;
RETURN temp;
END;
$BODY$;