How to audit records by saving changes in JSON

Question

I’m using a function to audit records at Insert, update, delete events for a Postgres 9.6.

I got the script on Git - Audit-Trigger

The script has been implemented and is working perfectly, but one of the results it brings me in the audit log is:

"id"=>"2", "ende"=>"Rua 2 de julho", "nome"=>"Amanda", "senha"=>"1234", "telefone"=>"069", "estaativo"=>"t", "ultimaalteracao"=>"2018-06-21 09:50:15.677835"

How to make the result a JSON:

{ 
    "id":2, 
    "ende":"Rua 2 de julho", 
    "nome":"Amanda", 
    "senha":"1234", 
    "telefone":"08799919991", 
    "estaativo":true, 
    "ultimaalteracao":"2018-06-21 09:50:15.677835"
}

I already tried to change the script obtained above using the function row_to_json, follows the amendment:

IF (TG_OP = 'UPDATE' AND TG_LEVEL = 'ROW') THEN
    --audit_row.row_data = hstore(OLD.*) - excluded_cols;
    audit_row.row_data = hstore(row_to_json(OLD)) - excluded_cols;
   -- audit_row.changed_fields =  (hstore(NEW.*) - audit_row.row_data) - excluded_cols;
    audit_row.changed_fields =  (hstore(row_to_json(NEW)) - audit_row.row_data) - excluded_cols;
    IF audit_row.changed_fields = hstore('') THEN
        -- All changed fields are ignored. Skip this update.
        RETURN NULL;
    END IF;
ELSIF (TG_OP = 'DELETE' AND TG_LEVEL = 'ROW') THEN
    --audit_row.row_data = hstore(OLD.*) - excluded_cols;
    audit_row.row_data = hstore(row_to_json(OLD)) - excluded_cols;
ELSIF (TG_OP = 'INSERT' AND TG_LEVEL = 'ROW') THEN
    --audit_row.row_data = hstore(NEW.*) - excluded_cols;
    audit_row.row_data = hstore(row_to_json(NEW)) - excluded_cols;
ELSIF (TG_LEVEL = 'STATEMENT' AND TG_OP IN ('INSERT','UPDATE','DELETE','TRUNCATE')) THEN
    audit_row.statement_only = 't';
ELSE
    RAISE EXCEPTION '[audit.if_modified_func] - Trigger func added as trigger for unhandled case: %, %',TG_OP, TG_LEVEL;
    RETURN NULL;
END IF;
INSERT INTO audit.logged_actions VALUES (audit_row.*);
RETURN NULL;

The error obtained is:

Answer 1

This field of audit log is certainly of the type hstore.

You can use the function hstore_to_json() to convert a field of the type hstore for JSON, look at you:

SELECT hstore_to_json('"id"=>"2", "ende"=>"Rua 2 de julho", "nome"=>"Amanda", "senha"=>"1234", "telefone"=>"069", "estaativo"=>"t", "ultimaalteracao"=>"2018-06-21 09:50:15.677835"')

Full example:

CREATE TABLE tb_foobar
(
    id BIGINT PRIMARY KEY,
    ende TEXT,
    nome TEXT,
    senha TEXT,
    telefone TEXT,
    estaativo BOOLEAN,
    ultimaalteracao TIMESTAMP
);

CREATE TABLE tb_log
(
    id BIGINT PRIMARY KEY,
    registro hstore
);

-- POPULANDO A TABELA tb_foobar
INSERT INTO tb_foobar ( id, ende, nome, senha, telefone, estaativo, ultimaalteracao ) VALUES
( 1, 'Casa do Chapeu, 87', 'MARIA SILVA', '12345678', '(11) 9999-9090', true, NOW() ),
( 2, 'Rua sem Nome, 214', 'JOAO NINGUEM', '01010101', '(11) 9999-7744', false, NOW() ),
( 3, 'Fim do Mundo, 30', 'JESUS DE NAZARE', 'A1B2C3', '(11) 9999-3344', true, NOW() ),
( 4, 'Terra do Nunca, 21', 'MICHAEL JACKSON', '9876543', '(11) 9999-4567', false, NOW() ),
( 5, 'Rua da Ladainha, 10', 'FULANO DE TAL', '666777666', '(11) 9999-1234', true, NOW() );

-- POPULANDO A tb_log COM REGISTROS DO TIPO hstore
INSERT INTO tb_log ( id, registro ) (SELECT id, hstore(*) FROM tb_foobar);


-- CONVERTENDO REGISTROS PARA JSON
SELECT hstore_to_json(registro) FROM tb_log;