Postgres: How to compare json to create audit trigger and display only the differences

I imagine that the use of the trigger would be generic for several tables, in which case I think it is interesting to have a function to compare the json with the records. This way you can evolve the function of independent comparison of the function of trigger.

I created an example using postgres 10 and data type jsonb , so I changed the data type of some columns from table auditoria from text to jsonb .

CREATE TABLE auditoria
(
    id serial NOT NULL,
    operacao character varying(50) NOT NULL,
    data timestamp with time zone,
    usuario character varying(50),
    tabela character varying(50),
    antes jsonb,
    depois jsonb,
    diferenca jsonb,
    CONSTRAINT auditoria_pkey PRIMARY KEY (id)
);

create table produto (
    codigo integer,
    descricao text,
    estoque numeric
);

insert into produto(codigo, descricao, estoque)
values(1, 'Filtro de óleo', 53), (2, 'Cerveja 600ml', 100);

The function below receives two jsonb , which would be the old and new records respectively. Then the json keys that would be the table fields are looped. If in the comparison of the two objects there is difference it returns a new json.

CREATE or replace FUNCTION fn_compara_jsonb(antigo jsonb, novo jsonb)
RETURNS jsonb
LANGUAGE plpgsql
IMMUTABLE STRICT
AS $function$
declare
    keys record;
    jsonb_retorno jsonb = '{}'::jsonb;
begin
    for keys in
        select *
        from jsonb_object_keys($1)
    loop
        if $1 -> keys.jsonb_object_keys <> $2 -> keys.jsonb_object_keys then
            jsonb_retorno = jsonb_retorno || format('{"%s": "old: %s, new: %s"}', keys.jsonb_object_keys, $1 ->> keys.jsonb_object_keys, $2 ->> keys.jsonb_object_keys)::jsonb;
        end if;
    end loop;
    return jsonb_retorno;
end
$function$;

Finally the above function is added in the trigger function in the section corresponding to the field diferenca following the same logic but with some modifications for use of type jsonb .

CREATE OR REPLACE FUNCTION auditoria() RETURNS trigger AS $BODY$
BEGIN
    IF (TG_OP = 'INSERT') THEN 
        INSERT INTO auditoria(operacao, data, usuario, tabela, antes, depois, diferenca)
        VALUES (TG_OP, now(), user, TG_TABLE_NAME, '{}'::jsonb, row_to_json(NEW.*), '{}'::jsonb);   
    END IF;
    IF (TG_OP = 'UPDATE') THEN 
        INSERT INTO auditoria(operacao, data, usuario, tabela, antes, depois, diferenca)
        VALUES (TG_OP, now(), user, TG_TABLE_NAME, row_to_json(OLD.*), row_to_json(NEW.*), fn_compara_jsonb(to_jsonb(row_to_json(OLD.*)), to_jsonb(row_to_json(NEW.*))));     
    END IF;
    IF (TG_OP = 'DELETE') THEN 
        INSERT INTO auditoria(operacao, data, usuario, tabela, antes, depois, diferenca)
        VALUES (TG_OP, now(), user, TG_TABLE_NAME, row_to_json(OLD.*), '{}'::jsonb, '{}'::jsonb);       
    END IF;
    RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

CREATE TRIGGER audit AFTER INSERT OR UPDATE OR DELETE ON produto FOR EACH ROW  EXECUTE PROCEDURE auditoria();

After updating the records with a SQL similar to: update produto set estoque=estoque+2; , the records in the auditoria table will be generated as below:

stackoverflow=# select * from auditoria where operacao='UPDATE';
┌─[ RECORD 1 ]────────────────────────────────────────────────────────────┐
│ id        │ 10                                                          │
│ operacao  │ UPDATE                                                      │
│ data      │ 2018-10-01 21:18:14.928847-03                               │
│ usuario   │ postgres                                                    │
│ tabela    │ produto                                                     │
│ antes     │ {"codigo": 1, "estoque": 53, "descricao": "Filtro de óleo"} │
│ depois    │ {"codigo": 1, "estoque": 55, "descricao": "Filtro de óleo"} │
│ diferenca │ {"estoque": "old: 53, new: 55"}                             │
├─[ RECORD 2 ]────────────────────────────────────────────────────────────┤
│ id        │ 11                                                          │
│ operacao  │ UPDATE                                                      │
│ data      │ 2018-10-01 21:18:14.928847-03                               │
│ usuario   │ postgres                                                    │
│ tabela    │ produto                                                     │
│ antes     │ {"codigo": 2, "estoque": 100, "descricao": "Cerveja 600ml"} │
│ depois    │ {"codigo": 2, "estoque": 102, "descricao": "Cerveja 600ml"} │
│ diferenca │ {"estoque": "old: 100, new: 102"}                           │
└───────────┴─────────────────────────────────────────────────────────────┘

Tempo: 1,150 ms

The function fn_compara_jsonb() can be evolved according to the type of data to return information of the comparison (greater than, smaller than, etc.).

Another suggestion would be the auditoria table only has a jsonb field with name modificacoes , for example, and in the auditoria() function handle how the data would be inserted according to each operation (INSERT, UPDATE or DELETE) p>

I hope I have managed to give you a direction to solve your problem:)

I managed to solve the problem in a slightly different way, which in my view was good for the result I wanted.

CREATE OR REPLACE FUNCTION auditoria() RETURNS trigger AS $BODY$
DECLARE
    pCodigo integer;
BEGIN
    IF (TG_OP = 'INSERT') THEN 
        INSERT INTO auditoria(operacao, data, usuario, tabela, antes, depois, diferenca)
        VALUES (TG_OP, now(), user, TG_TABLE_NAME, '', row_to_json(NEW), '');   
    END IF;
    IF (TG_OP = 'UPDATE') THEN 
        INSERT INTO auditoria(operacao, data, usuario, tabela, antes, depois, diferenca)
        VALUES (TG_OP, now(), user, TG_TABLE_NAME, row_to_json(OLD), row_to_json(NEW), '') RETURNING ID INTO pCodigo;

        UPDATE auditoria SET diferenca = (  SELECT string_agg(a || ' >> ' || b, ',') FROM (
                                            SELECT 
                                            CAST(json_each(antes::json) AS CHARACTER VARYING) AS a, CAST(json_each(depois::json) AS CHARACTER VARYING) AS b
                                            FROM auditoria WHERE id = pCodigo 
                                            ) AS T
                                            WHERE a <> b) WHERE id = pCodigo; 
    END IF;
    IF (TG_OP = 'DELETE') THEN 
        INSERT INTO auditoria(operacao, data, usuario, tabela, antes, depois, diferenca)
        VALUES (TG_OP, now(), user, TG_TABLE_NAME, row_to_json(OLD), '', '');       
    END IF;
    RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE
COST 100;

This way with the update I get the differences and goes for all the tables