I would like to create a basic audit log trigger in postgres that I can use across various tables.

My only requirement is that the audit log show each updated value as a separate entry. i.e. if an INSERT is performed into a table with 5 rows, I receive 5 entries in the audit log table (one for each value added) or if that row is deleted from the table, I receive 5 entries (one for each value removed)

I have looked at various examples and am still having trouble getting the output correct, especially when the operation is UPDATE.

Here is the basic trigger flow.

-- 1. Making the Audit Table

CREATE TABLE audit_table

-- The audit table which will show: 
-- Table, the ID of whats being changed, new columns, old columns, 
-- user, operation(insert update delete), and timestamp

(
"src_table" TEXT NOT NULL,
"src_id" INT NOT NULL,
"col_new"  TEXT,
"col_old" TEXT,
"user" TEXT DEFAULT current_user,
"action" TEXT NOT NULL,
"when_changed" TIMESTAMP
);

-- 2. Creating the base audit trigger function, the engine for processing changes --

CREATE OR REPLACE FUNCTION audit_trigger() 
 RETURNS trigger AS 
 
$$

BEGIN
    IF TG_OP = 'INSERT' --Only shows new values
    THEN
            INSERT INTO audit_table ( "src_table", "src_id", "col_new", "user", "action", "when_changed")
                VALUES(TG_TABLE_NAME, TG_RELID, row_to_json(NEW), current_user, TG_OP, current_timestamp);
        RETURN NEW;
        
    ELSIF TG_OP = 'UPDATE' --Shows new and old values
    THEN
            INSERT INTO audit_table ("src_table", "src_id", "col_new", "col_old", "user", "action", "when_changed")
                VALUES(TG_TABLE_NAME, TG_RELID, row_to_json(NEW), row_to_json(OLD), current_user, TG_OP, current_timestamp);
        RETURN NEW;
        
    ELSIF TG_OP = 'DELETE' --Only shows old values
    THEN
            INSERT INTO audit_table ("src_table", "src_id", "col_old", "user", "action", "when_changed")
                VALUES(TG_TABLE_NAME, TG_RELID, row_to_json(OLD), current_user, TG_OP, current_timestamp);
        RETURN OLD;
    END IF;
END

$$

LANGUAGE 'plpgsql';

-- 3. Basic logic for calling audit trigger on tables, works for any insert, update, delete

CREATE TRIGGER test_audit_trigger
 BEFORE INSERT OR UPDATE OR DELETE 
 
 ON test_table
 
 FOR EACH ROW EXECUTE PROCEDURE audit_trigger();

 

The issue:

• row_to_json returns the entire old or new payload as one column. I would like to return each change as a separate entry row in the audit_table, using for example column_name old_value new_value as a schema. What is the simplest way of achieving this? Having it be json is not a requirement.

Also not required:

• Having it be BEFORE keyword, if AFTER or a combination of the two is better.
• Having one trigger do all 3 functions, it can be a separate trigger or function per action
• Returning values that are not changed, for example if an UPDATE only changes 1 value, I do not need the unchanged values in the log.

CodePudding user response：

Here is a possible solution based on yours. One AFTER row trigger for insert, update and delete. The structure of audit_table is a bit different. Old and new values are saved as their text representation. You may wish to change the behaviour under insert and delete as they seem to be too verbose.

Unrelated but under your requirements the audit table has to implement the EAV (entity, attribute, value) antipattern.

drop table if exists audit_table;
create table audit_table
(
 src_table text not null,
 col_name text not null,
 v_old text,
 v_new text,
 "user" text not null default current_user,
 action text not null,
 when_changed timestamptz not null default current_timestamp
);

create or replace function audit_tf() returns trigger language plpgsql as 
$$
declare 
  k text;
  v text;
  j_new jsonb := to_jsonb(new);
  j_old jsonb := to_jsonb(old);
begin
    if TG_OP = 'INSERT' then -- only shows new values
        for k, v in select * from jsonb_each_text(j_new) loop
            insert into audit_table (src_table, col_name, v_new, action) 
            values (TG_TABLE_NAME, k, v, TG_OP);
        end loop;      
    elsif TG_OP = 'UPDATE' then -- shows new and old values 
        for k, v in select * from jsonb_each_text(j_new) loop
            if (v <> j_old ->> k) then
                insert into audit_table (src_table, col_name, v_new, v_old, action) 
                values (TG_TABLE_NAME, k, v, j_old ->> k, TG_OP);
            end if;
         end loop;  
    elsif TG_OP = 'DELETE' then -- only shows old values
        for k, v in select * from jsonb_each_text(j_old) loop
            insert into audit_table (src_table, col_name, v_old, action) 
            values (TG_TABLE_NAME, k, v, TG_OP);
        end loop;      
    end if;
    return null;
end;
$$;
LANGUAGE 'plpgsql';

-- Demo

drop table if exists delme;
create table delme (x integer, y integer, z text, t timestamptz default now());

create trigger test_audit_trigger
after insert or update or delete on delme
for each row execute procedure audit_tf();

insert into delme (x, y, z) values (2, 2, 'two');
update delme set x = 1, y = 10 where x = 2;
delete from delme where x = 1;
select src_table, col_name, v_old, v_new, action from audit_table;