Skip to content

Instantly share code, notes, and snippets.

@jalallinux
Last active December 5, 2022 04:01
Show Gist options
  • Save jalallinux/6054ed3366c60e6af78f51cc1e8285a6 to your computer and use it in GitHub Desktop.
Save jalallinux/6054ed3366c60e6af78f51cc1e8285a6 to your computer and use it in GitHub Desktop.
The PostgreSQL trigger script is recording all events with their changed values.
-- 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