Postgres Audit Log Trigger

 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

CREATE TABLE changelog
(
	id         serial NOT NULL,
	ts         timestamp DEFAULT NOW(),
	table_name text,
	operation  text,
	new_val    json,
	old_val    json
);

CREATE INDEX changelog_ts_table_name_index
	ON changelog (ts DESC, table_name ASC);

CREATE OR REPLACE FUNCTION logchange() RETURNS trigger AS
$$
BEGIN
	IF TG_OP = 'INSERT'
	THEN
		INSERT INTO changelog (table_name, operation, new_val)
		VALUES (TG_RELNAME, TG_OP, ROW_TO_JSON(NEW));
		RETURN NEW;
	ELSIF TG_OP = 'UPDATE'
	THEN
		INSERT INTO changelog (table_name, operation, new_val, old_val)
		VALUES (TG_RELNAME, TG_OP, ROW_TO_JSON(NEW), ROW_TO_JSON(OLD));
		RETURN NEW;
	ELSIF TG_OP = 'DELETE'
	THEN
		INSERT INTO changelog
			(table_name, operation, old_val)
		VALUES (TG_RELNAME, TG_OP, ROW_TO_JSON(OLD));
		RETURN OLD;
	END IF;
END;
$$ LANGUAGE 'plpgsql' SECURITY DEFINER;

CREATE TRIGGER users_changelog_biudt
	BEFORE INSERT OR UPDATE OR DELETE
	ON users
	FOR EACH ROW
EXECUTE PROCEDURE logchange();