Postgresql - How do I Iterate through individual fields of OLD records in Postgres plpgsql trigger?

Joe
September 21, 2023
171 views
0 votes
2 Answers

Is there a way to iterate through individual fields of OLD records in a Postgres plpgsql trigger?
I want to write a trigger function that logs all the changes that were made to the particular fields of a record. For example:

-- My Data Table
CREATE TABLE data (
id integer,
value integer
);

-- My Changelog table
CREATE TABLE changelog (
id integer,
changes text,
user text,
changed_on timestamp
);

CREATE OR REPLACE FUNCTION data_history() RETURNS TRIGGER AS
$BODY$
DECLARE
    changes text;
BEGIN
    changes := '''';
    IF old.value <> new.value THEN
    changes := CONCAT(changes, old.value, ':', new.value)
    INSERT INTO
            changelog(changes, user, changed_on)
            VALUES(changes, current_user, NOW());
    END IF
    RETURN NEW;
END;
$BODY$
language plpgsql;

CREATE OR REPLACE TRIGGER change_trigger AFTER UPDATE ON data FOR EACH ROW EXECUTE PROCEDURE data_history();

While this works as expected the function is completely table-dependent. I would like to make it more generic by comparing all individual fields of OLD and NEW in a loop. Is there any way to do that?

Answers

You could add a tracked_table column to your changelog table and set up a generic trigger function you can attach to any table you wish to track.

OLD and NEW records follow the structure of the table the trigger operates on so you can use TG_TABLE_NAME to find the list of column names in information_schema.columns then loop over them to run your comparisons in a dynamic SQL block. Demo:

CREATE OR REPLACE FUNCTION data_history() RETURNS TRIGGER AS $BODY$
DECLARE
    changes text;
    v_column_name text;
    v_diff_found boolean;
    v_old_currval text;
    v_new_currval text;
BEGIN
    FOR v_column_name IN select column_name 
                         from information_schema.columns 
                         where table_name=TG_TABLE_NAME 
    LOOP
        EXECUTE FORMAT(
            $dynamic_sql$
            select $1.%1$I is distinct from $2.%1$I, 
                  quote_literal($1.%1$I), 
                  quote_literal($2.%1$I);
            $dynamic_sql$,
            v_column_name) 
        USING OLD,NEW
        INTO v_diff_found, v_old_currval, v_new_currval;
        IF v_diff_found THEN
            changes := concat_ws(
                           '; ',
                           changes, 
                           v_column_name||':'||v_old_currval||' -> '||v_new_currval
                       );
        END IF;
    END LOOP;
    INSERT INTO changelog(tracked_table,changes, "user", changed_on)
    VALUES(TG_TABLE_NAME,changes, current_user, NOW());
    RETURN NEW;
END $BODY$ language plpgsql;

Depending on what and how closely you’re trying to track, you could consider pgaudit extension. If you want to record changes to move back and forth between versions, there’s Continuous Archiving and Point-in-Time Recovery.

Today, the most simple solution can be based on transformation to json type, and then iteration over json:

create or replace function trg_func()
returns trigger as $$
declare j json; k varchar; v varchar;
begin
  j := row_to_json(new);
  for k,v in select * from json_each_text(j)
  loop
    raise notice '% %', k, v;
  end loop;
  return new;
end;
$$ language plpgsql;

create table foo(a int, b int, c varchar, d date);

create trigger trg before insert on foo for each row execute function trg_func();

(2023-09-21 19:58:26) postgres=# insert into foo values(10,20,'ahoj', current_date);
NOTICE:  {"a":10,"b":20,"c":"ahoj","d":"2023-09-21"}
NOTICE:  a 10
NOTICE:  b 20
NOTICE:  c ahoj
NOTICE:  d 2023-09-21
INSERT 0 1

Iteration over jsonb type will be probably little bit more effective

create or replace function trg_func()
returns trigger as $$
declare j jsonb; k varchar;
begin
  j := to_jsonb(new);
  for k in select * from jsonb_object_keys(j)
  loop
    raise notice '% %', k, j[k];
  end loop;
  return new;
end;

The hstore type (from extension hstore) has very nice functionality for detection difference between two different values
see http://okbob.blogspot.com/2015/01/most-simply-implementation-of-history.html

Please signup or login to give your own answer.

Click here to cancel reply.

Postgresql – How do I Iterate through individual fields of OLD records in Postgres plpgsql trigger?

Answers