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

Question

According to the structure below, I would like that when it was updated I could memorize only the differences. Example in the table produto I have the fields:

prodcodigo = 1
proddescricao = 'FILTRO DE OLEO'
prodestoque = 33

If I do an update by changing the stock to 32 I would like in the difference column of my audit table only the stock that has changed. Today the before is like this:

ANTES: {"prodcodigo":1,"proddescricao":"FILTRO DE OLEO","prodestoque":33}
DEPOIS: {"prodcodigo":1,"proddescricao":"FILTRO DE OLEO","prodestoque ":32}
DIFERENÇA: {"prodestoque ":33} > {"prodestoque ":32}

--TABLE

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 text,
  depois text,
  diferenca text,
  CONSTRAINT auditoria_pkey PRIMARY KEY (id)
)

--TRIGGER FUNCTION

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, '', 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), '');     
    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;

-- TRIGGER

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

Answer 1

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

I created an example using the postgres 10 and data type resources jsonb, thus, I changed the data type of some table columns auditoria of text for 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 takes two jsonb, which would be the old and new records respectively. Then a loop is made on the json keys which would be the table fields. If in the comparison of the two objects there is a 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 to the Trigger function in the section corresponding to the field diferenca following the same logic but with some modifications for use of the 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 an SQL similar to: update produto set estoque=estoque+2;, records would be generated in the table auditoria 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 to according to the type of data return comparison information (greater than, smaller than, etc).

Another suggestion would be the table auditoria have only one jsonb field named modificacoes, for example, and in function auditoria() treat how the data would be entered as per each operation (INSERT, UPDATE or DELETE).

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

Answer 2

I managed to solve the problem in a slightly different way that 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 can get the differences and applies to all tables