postgres audit log trigger

This triggers will log before and after fields for a table in JSON format.

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

Last updated