How store as JSON the new data inside a trigger in PostgreSQL?

Question

I try to do as explained in:

https://wiki.postgresql.org/wiki/Audit_trigger

Auditing values as JSON

For PostgreSQL 9.2, or 9.1 with the fantastic json_91 addon, you can log the old and new values in the table as structured json instead of flat text, giving you much more power to query your audit history. Just change the types of v_old_data, v_new_data, original_data and new_data from TEXT to json, then replace ROW(OLD.) and ROW(NEW.) with row_to_json(OLD) and row_to_json(NEW) respectively.

However this get me a error:

CREATE OR REPLACE FUNCTION add_log (name text, Action TEXT, data jsonb, OUT RETURNS BOOLEAN)
AS $$
BEGIN
    RETURNS = true;
END;
$$
LANGUAGE 'plpgsql';

CREATE OR REPLACE FUNCTION log_city() RETURNS TRIGGER AS 
$$
DECLARE
v_new_data jsonb;
BEGIN
    IF (TG_OP = 'UPDATE') THEN
        RETURN NEW;
    ELSIF (TG_OP = 'INSERT') THEN
        v_new_data := row_to_jsonb(NEW);
        EXECUTE add_log('City', 'City.New', v_new_data);
        RETURN NEW;
    END IF;
    RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
$$ LANGUAGE plpgsql;

INSERT INTO Location (city, state, country) values ('a', 'b' , 'c')

It say:

ERROR: function row_to_jsonb(location) does not exist

If I put v_new_data := row_to_jsonb(ROW(NEW)); then I get:

ERROR: function row_to_jsonb(record) does not exist

I try all again and yes, with json it work, but with jsonb not. — mamcx
– mamcx, Commented Jun 16, 2016 at 20:39

e4c5 · Accepted Answer · 2016-06-16 23:36:44Z

2

It's stated in the documentation that

Table 9-42 shows the functions that are available for creating json and jsonb values. (There are no equivalent functions for jsonb, of the row_to_json and array_to_json functions. However, the to_jsonb function supplies much the same functionality as these functions would.)

thus it's row_to_json that has to be used. a row_to_jsonb does not exists but row_to_json produces the desired result for the JSONB type as well.

answered Jun 16, 2016 at 23:36

e4c5

53.9k11 gold badges110 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

icc97 · Accepted Answer · 2023-09-19 07:19:44Z

That Audit trigger article was brilliant to combine with JSON.

Here's an implementation I did using Postgres 14 and JSON, so you don't need the ROW anymore.

I have updated_by and created_by on all tables that will be audited, so I use that instead of the database user for the user_id
I extract the id of the field being modified out into it's own record_id field
I'm not doing this in a separate schema

CREATE OR REPLACE
FUNCTION if_modified() RETURNS TRIGGER AS $body$
DECLARE
    v_old_record jsonb;
    v_new_record jsonb;

BEGIN
    IF (TG_OP = 'UPDATE') THEN
        /** {@link https://stackoverflow.com/q/37824289} */
        v_old_record := row_to_json(OLD);
        v_new_record := row_to_json(NEW);

        INSERT INTO audit_logs (action, table_name, user_id, record_id, old_record, new_record)
        VALUES (TG_OP, TG_TABLE_NAME::TEXT, NEW.updated_by, NEW.id::TEXT, v_old_record, v_new_record);

        RETURN NEW;

    ELSIF (TG_OP = 'DELETE') THEN
        v_old_record := row_to_json(OLD);

        INSERT INTO audit_logs (action, table_name, record_id, old_record)
        VALUES (TG_OP, TG_TABLE_NAME::TEXT, OLD.id::TEXT, v_old_record);

        RETURN OLD;

    ELSIF (TG_OP = 'INSERT') THEN
        v_new_record := row_to_json(NEW);

        INSERT INTO audit_logs (action, table_name, user_id, record_id, new_record)
        VALUES (TG_OP, TG_TABLE_NAME::TEXT, NEW.created_by, NEW.id::TEXT, v_new_record);

        RETURN NEW;
    ELSE
        RAISE WARNING '[IF_MODIFIED] - Other action occurred: %, at %', TG_OP, now();

        RETURN NULL;
    END IF;

    EXCEPTION

    WHEN data_exception THEN
        RAISE WARNING '[IF_MODIFIED] - UDF ERROR [DATA EXCEPTION] - SQLSTATE: %, SQLERRM: %', SQLSTATE, SQLERRM;
        RETURN NULL;

    WHEN unique_violation THEN
        RAISE WARNING '[IF_MODIFIED] - UDF ERROR [UNIQUE] - SQLSTATE: %, SQLERRM: %', SQLSTATE, SQLERRM;
        RETURN NULL;

    WHEN OTHERS THEN
        RAISE WARNING '[IF_MODIFIED] - UDF ERROR [OTHER] - SQLSTATE: %, SQLERRM: %', SQLSTATE, SQLERRM;
        RETURN NULL;
END;

$body$
LANGUAGE plpgsql SECURITY DEFINER;

Here's the table I create:

CREATE TABLE audit_logs (
    id serial PRIMARY KEY NOT NULL,
    action TEXT NOT NULL CHECK (action IN ('INSERT', 'DELETE', 'UPDATE')),
    table_name TEXT NOT NULL,
    record_id TEXT,
    old_record jsonb,
    new_record jsonb,
    user_id int,
    created_at timestamp WITH time ZONE NOT NULL DEFAULT current_timestamp
);

ALTER TABLE audit_logs
ADD CONSTRAINT fk_audit_logs_user_id
FOREIGN KEY (user_id)
REFERENCES users(id);

ALTER TABLE audit_logs
ADD CONSTRAINT check_old_record_new_record_not_null
CHECK (
    (old_record IS NOT NULL) OR
    (new_record IS NOT NULL)
);

Then an example trigger on the users table:

CREATE OR REPLACE TRIGGER users_audit
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW EXECUTE PROCEDURE if_modified();

Collectives™ on Stack Overflow

How store as JSON the new data inside a trigger in PostgreSQL?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related