Skip to content

Instantly share code, notes, and snippets.

@notxcain
Created February 6, 2019 11:43
Show Gist options
  • Save notxcain/84deb22a564f6b301c4150c06b089df6 to your computer and use it in GitHub Desktop.
Save notxcain/84deb22a564f6b301c4150c06b089df6 to your computer and use it in GitHub Desktop.
Postgres Table Audit Example
CREATE OR REPLACE FUNCTION auditlogfunc RETURNS TRIGGER AS $example_table$
DECLARE
row_key JSONB;
affected_row JSON;
operation char;
BEGIN
IF TG_OP IN('INSERT', 'UPDATE') THEN
affected_row := row_to_json(NEW);
ELSE
affected_row := row_to_json(OLD);
END IF;
--Get PK columns
--You may want to extract this to a SQL function
WITH pk_columns (attname) AS (
SELECT
CAST(a.attname AS TEXT)
FROM
pg_index i
JOIN pg_attribute a ON a.attrelid = i.indrelid AND a.attnum = ANY(i.indkey)
WHERE
i.indrelid = TG_RELID
AND i.indisprimary
)
SELECT
json_object_agg(key, value) INTO row_key
FROM
json_each_text(affected_row)
WHERE
key IN(SELECT attname FROM pk_columns);
IF TG_OP = 'INSERT' THEN
insert into test_table_log (key, operation, value) values (row_key, 'i', affected_row);
ELSIF TG_OP = 'UPDATE' THEN
insert into test_table_log (key, operation, value) values (row_key, 'u', affected_row);
ELSE
insert into test_table_log (key, operation) values (row_key, 'd');
END IF;
RETURN NEW;
END;
$example_table$ LANGUAGE plpgsql;
CREATE TRIGGER tg_audit_cadprodu_row AFTER INSERT OR UPDATE OR DELETE
ON public.test_table FOR EACH ROW EXECUTE PROCEDURE public.auditlogfunc();
--A simple test
INSERT INTO test_table VALUES (CAST((random() * 10000) AS INTEGER), 'Test');
CREATE TABLE public.test_table
(
id BIGINT NOT NULL,
a_column TEXT NOT NULL,
CONSTRAINT test_tablepkey PRIMARY KEY (id)
);
CREATE TABLE test_table_log
(
id BIGSERIAL PRIMARY KEY,
key JSONB NOT NULL,
operation text NOT NULL,
value JSONB,
timestamp timestamp NOT NULL DEFAULT now()
);
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment