Question 192586 in PostgreSQL Question posted in
The official documentation can be found here.

How do I get the set of changed columns in a PostgreSQL UPDATE trigger function?

I have a simple function triggered on any updates to a PostgreSQL 14 table:

CREATE OR REPLACE FUNCTION track_changes() RETURNS TRIGGER AS $body$

BEGIN
    raise notice 'old is: "%"', OLD;
    raise notice 'new is: "%"', NEW;
END;
$body$
LANGUAGE plpgsql
;

CREATE TRIGGER update_trigger
AFTER UPDATE ON students
FOR EACH ROW EXECUTE PROCEDURE track_changes();

However, the function is meant to be used with many different tables – i.e. I don’t know which columns are available. How can I, within the trigger function, calculate the set of columns which differ between OLD and NEW – e.g. as a record or row or even JSONB value?

Tags:

2

Answers


  1. 0

    You’ll have to query the metadata:

    CREATE OR REPLACE FUNCTION changed_columns() RETURNS trigger
   LANGUAGE plpgsql AS
$$DECLARE
   col name;
   changed_cols text[] := ARRAY[]::text[];
   changed boolean;
BEGIN
   FOR col IN
      /* get the columns of the table */
      SELECT attname
      FROM pg_attribute
      WHERE attrelid = TG_RELID
        AND attnum > 0
        AND NOT attisdropped
   LOOP
      /* compare the column in OLD and NEW */
      EXECUTE
         format(
            'SELECT (($1::%1$s).%2$I) IS DISTINCT FROM (($2::%1$s).%2$I)',
            TG_RELID::regclass,
            col
         )
         USING OLD, NEW
         INTO changed;

      IF changed THEN
         changed_cols := changed_cols || col::text;
      END IF;
   END LOOP;

   RAISE NOTICE 'changed columns: %',
                array_to_string(changed_cols, ', ');

   /* proceed with the UPDATE */
   RETURN NEW;
END;$$;
    Login or Signup to reply.
  2. 0

    Here is a JSONB-based suggestion. Convert new and old to JSONB so that you could extract the keys (these are the table column names) and iterate over them.

    create or replace function changed_columns()
returns trigger language plpgsql as
$function$
declare
  new_jb jsonb := to_jsonb(new); 
  old_jb jsonb := to_jsonb(old); 
  running_column text;
  columns_list text[] := '{}';
begin
 for running_column in select jsonb_object_keys(new_jb) loop
   if (new_jb -> running_column <> old_jb -> running_column) then
     columns_list := columns_list || running_column;
   end if;
 end loop;
 raise notice 'changed columns: %', array_to_string(columns_list, ',');
 -- or use columns_list for something more useful
 return null; -- use it in an after update trigger
end;
$function$;
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search