Automatic history tracking in postgres.

history_tracking.sql

-- create a separate schema to hold our history table
CREATE SCHEMA IF NOT EXISTS logging;
CREATE TABLE IF NOT EXISTS logging.t_history (
        id         serial,
        tstamp     timestamp DEFAULT now(),
        schemaname text,
        tabname    text,
        operation  text,
        who        text DEFAULT current_user,
        new_val    json,
        old_val    json
);

-- function to clone records into our history table
CREATE OR REPLACE FUNCTION  change_trigger() RETURNS trigger AS $$
BEGIN
    IF TG_OP = 'INSERT'
    THEN
        INSERT INTO logging.t_history (tabname, schemaname, operation, new_val)
                VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(NEW));
        RETURN NEW;
    ELSIF TG_OP = 'UPDATE'
    THEN
        INSERT INTO logging.t_history (tabname, schemaname, operation, new_val, old_val)
            VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP,
                    row_to_json(NEW), row_to_json(OLD));
        RETURN NEW;
    ELSIF TG_OP = 'DELETE'
    THEN
        INSERT INTO logging.t_history (tabname, schemaname, operation, old_val)
            VALUES (TG_RELNAME, TG_TABLE_SCHEMA, TG_OP, row_to_json(OLD));
        RETURN OLD;
    END IF;
END;
$$ LANGUAGE 'plpgsql' SECURITY DEFINER;

-- attach the fn to triggers on mutation ops
DO $$ DECLARE
    r RECORD;
BEGIN
    FOR r IN (SELECT * FROM pg_tables WHERE schemaname = current_schema()) LOOP
        EXECUTE 'DROP TRIGGER IF EXISTS t ON ' || r.tablename;
        EXECUTE 'CREATE TRIGGER t BEFORE INSERT OR UPDATE OR DELETE ON '
        || r.tablename || ' FOR EACH ROW EXECUTE PROCEDURE change_trigger()';
    END LOOP;
END $$;