Postgres Table Audit Example

tableaudit.sql

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()
);