Transfer JSON table columns in Postgres Trigger

Asked

Viewed 328 times

1

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

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

1 answer

1

To perform this action you will need to use the function row_to_json. This function will transform the entire row (Row) into a json object: the "key" will be the column name and the "value" the record 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 field type jsonb (if your postgres version is older use json) on your table log_table, it is optimized and allows GIN-type indexing.

I hope I’ve helped :)

Browser other questions tagged json postgresql trigger

You are not signed in. Login or sign up in order to post.