Last active
December 5, 2022 04:01
-
-
Save jalallinux/6054ed3366c60e6af78f51cc1e8285a6 to your computer and use it in GitHub Desktop.
The PostgreSQL trigger script is recording all events with their changed values.
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
-- Define audit_logs table | |
CREATE TABLE IF NOT EXISTS public.audit_logs | |
( | |
uuid uuid NOT NULL, | |
operation character varying(10) COLLATE pg_catalog."default" NOT NULL, | |
userid text COLLATE pg_catalog."default" NOT NULL, | |
tablename text COLLATE pg_catalog."default" NOT NULL, | |
changed json NOT NULL, | |
stamp timestamp without time zone NOT NULL | |
); | |
-- Define jsonb_diff_val() function | |
CREATE OR REPLACE FUNCTION jsonb_diff_val(val1 JSONB, val2 JSONB) | |
RETURNS JSONB AS | |
$$ | |
DECLARE | |
result JSONB; | |
v RECORD; | |
BEGIN | |
result = val1; | |
FOR v IN SELECT * FROM jsonb_each(val2) | |
LOOP | |
IF result @> jsonb_build_object(v.key, v.value) | |
THEN | |
result = result - v.key; | |
ELSIF result ? v.key THEN | |
CONTINUE; | |
ELSE | |
result = result || jsonb_build_object(v.key, 'null'); | |
END IF; | |
END LOOP; | |
RETURN result; | |
END; | |
$$ LANGUAGE plpgsql; | |
-- Define logging logging() function | |
CREATE OR REPLACE FUNCTION logging() RETURNS TRIGGER AS | |
$log_recorder$ | |
BEGIN | |
IF (TG_OP = 'DELETE') THEN | |
INSERT INTO audit_logs SELECT OLD.uuid, TG_OP, user, tg_table_name, row_to_json(OLD.*), now(); | |
RETURN OLD; | |
ELSIF (TG_OP = 'UPDATE') THEN | |
INSERT INTO audit_logs | |
SELECT NEW.uuid, | |
TG_OP, | |
user, | |
tg_table_name, | |
jsonb_diff_val(row_to_json(NEW.*)::jsonb, row_to_json(OLD.*)::jsonb)::json, | |
now(); | |
RETURN NEW; | |
ELSIF (TG_OP = 'INSERT') THEN | |
INSERT INTO audit_logs SELECT NEW.uuid, TG_OP, user, tg_table_name, row_to_json(NEW.*), now(); | |
RETURN NEW; | |
END IF; | |
RETURN NULL; | |
END; | |
$log_recorder$ LANGUAGE plpgsql; | |
-- Register logging() trigger function on tables except audit_logs | |
do | |
$$ | |
declare | |
f record; | |
begin | |
for f in select table_name | |
from information_schema.tables | |
WHERE table_type = 'BASE TABLE' | |
and table_schema = 'public' | |
and table_name not in ('audit_logs') | |
loop | |
Execute 'DROP TRIGGER IF EXISTS change_events_' || f.table_name || ' ON ' || f.table_name; | |
Execute 'CREATE OR REPLACE TRIGGER change_events_' || f.table_name || ' AFTER INSERT or UPDATE or DELETE ON ' || f.table_name || ' FOR EACH ROW EXECUTE PROCEDURE logging()'; | |
end loop; | |
end; | |
$$; |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment