How to audit records by saving changes in JSON

Question

How to audit records by saving changes in JSON

Navigation

#1 by (2 votes)

1

I'm using a function to audit records in insert, update, delete events for a PostGres 9.6 table .

I took the script from the 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 have tried to change the script obtained above using the row_to_json function, it follows the change:

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 you get is:

postgresql psql

asked by anonymous 21.06.2018 / 16:29

1 answer

Remove dynamic inputs Foreign key with 3 primary keys

score 2 · Accepted Answer

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

You can use hstore_to_json() to convert a field of type hstore to JSON , see:

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"')

Complete 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;