Transfer table columns in JSON in Postgres Trigger

Question

Transfer table columns in JSON in Postgres Trigger

Navigation

#1 by (0 votes)

0

I am creating a trigger in Postgres and would like to turn all columns into JSON to insert into a single column of the 'LOG' table, I would like to for example 'OLD. *' in JSON and add it in the oldvalue column 'of the LOG table.

create table log_table (
    tablename varchar(200),
    oldvalue varchar(200),
    newvalue varchar(200),
    operation varchar(10),
    user_id integer
);
CREATE OR REPLACE FUNCTION teste_log() RETURNS TRIGGER AS $$
    BEGIN
        IF (TG_OP = 'DELETE') THEN
            INSERT INTO log_table VALUES(TG_TABLE_NAME, TG_OP, user, OLD.*);
            RETURN OLD;
        ELSIF (TG_OP = 'UPDATE') THEN
            INSERT INTO log_table VALUES(TG_TABLE_NAME, TG_OP, user, NEW.*);
            RETURN NEW;
        ELSIF (TG_OP = 'INSERT') THEN
            INSERT INTO log_table VALUES(TG_TABLE_NAME, TG_OP, user, NEW.*);
            RETURN NEW;
        END IF;
    END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER teste_log
AFTER INSERT OR UPDATE OR DELETE ON public.*
    FOR EACH ROW EXECUTE PROCEDURE teste_log();

json postgresql trigger

asked by anonymous 12.12.2016 / 19:34

1 answer

UWP Horizontal alignment of flhos in a RelativePanel Calling page using Class in Wordpress

score 0 · Answer 1

To perform this action you need to use the row_to_json function. This function will transform the entire row (row) into a json object: the "key" will be the column name and the "value" the registry value.

In your function you can use the code as below:

CREATE OR REPLACE FUNCTION teste_log() RETURNS TRIGGER AS $$
BEGIN
    IF (TG_OP = 'DELETE') THEN
        INSERT INTO log_table VALUES(TG_TABLE_NAME, TG_OP, user, row_to_json(OLD.*));
        RETURN OLD;
    ELSIF (TG_OP = 'UPDATE') THEN
        INSERT INTO log_table VALUES(TG_TABLE_NAME, TG_OP, user, row_to_json(NEW.*));
        RETURN NEW;
    ELSIF (TG_OP = 'INSERT') THEN
        INSERT INTO log_table VALUES(TG_TABLE_NAME, TG_OP, user, row_to_json(NEW.*));
        RETURN NEW;
    END IF;
END;
$$ LANGUAGE plpgsql;

A note about json, use the jsonb field type (if your version of postgres is older use json ) in your log_table table , it is optimized and allows GIN type indexing.

I hope I have helped:)