postgres audit log trigger
This triggers will log before and after fields for a table in JSON format.
1
CREATE TABLE changelog
2
(
3
id serial NOT NULL,
4
ts timestamp DEFAULT NOW(),
5
table_name text,
6
operation text,
7
new_val json,
8
old_val json
9
);
10
​
11
CREATE INDEX changelog_ts_table_name_index
12
ON changelog (ts DESC, table_name ASC);
13
​
14
CREATE OR REPLACE FUNCTION logchange() RETURNS trigger AS
15
$
16
BEGIN
17
IF TG_OP = 'INSERT'
18
THEN
19
INSERT INTO changelog (table_name, operation, new_val)
20
VALUES (TG_RELNAME, TG_OP, ROW_TO_JSON(NEW));
21
RETURN NEW;
22
ELSIF TG_OP = 'UPDATE'
23
THEN
24
INSERT INTO changelog (table_name, operation, new_val, old_val)
25
VALUES (TG_RELNAME, TG_OP, ROW_TO_JSON(NEW), ROW_TO_JSON(OLD));
26
RETURN NEW;
27
ELSIF TG_OP = 'DELETE'
28
THEN
29
INSERT INTO changelog
30
(table_name, operation, old_val)
31
VALUES (TG_RELNAME, TG_OP, ROW_TO_JSON(OLD));
32
RETURN OLD;
33
END IF;
34
END;
35
$ LANGUAGE 'plpgsql' SECURITY DEFINER;
36
​
37
CREATE TRIGGER users_changelog_biudt
38
BEFORE INSERT OR UPDATE OR DELETE
39
ON users
40
FOR EACH ROW
41
EXECUTE PROCEDURE logchange();
Copied!
Last modified 1mo ago
Copy link