Get access to OLD.myLoopVariable / NEW.myLoopVariable in a plpgsql trigger function?

Question

I try to get access to a loop variable that is a column name of a table, to get old and new data of each column.

CREATE FUNCTION my_function() RETURNS TRIGGER AS $emp_stamp$
DECLARE
  current_column_name text;
BEGIN
  FOR current_column_name IN
    SELECT column_name 
    FROM information_schema.Columns 
    WHERE table_schema = TG_TABLE_SCHEMA AND table_name = TG_TABLE_NAME 
    LOOP
      IF (OLD."current_column_name" <> NEW."current_column_name")
      then ...
      END IF;
    END LOOP;
  RETURN NEW;
END;
$emp_stamp$ LANGUAGE plpgsql;

CREATE TRIGGER my_trigger
AFTER UPDATE ON my_table FOR EACH ROW
EXECUTE PROCEDURE my_function();

And I have a next error: ERROR: record "old" has no field "current_column_name"
I tried without quotes (""), but got the same result.

How can I get access to old and new column data?
I need to save all changed data, like a history with fields: column_name, old_value and new_value.

If you want to check if _any_ column has changed, just use `if (old is distinct from new)` no need to loop through all columns. But you will however not know which column(s) _were_ different — , Mar 07 '17 at 22:36
no, I exactly want to save all changed data, like a history with fields: column_name, old_value, new_value — ProgM, Mar 07 '17 at 22:40
See here: https://wiki.postgresql.org/wiki/Audit_trigger_91plus or here http://okbob.blogspot.co.uk/2015/01/most-simply-implementation-of-history.html or here http://8kb.co.uk/blog/2015/01/19/copying-pavel-stehules-simple-history-table-but-with-the-jsonb-type/ for some sample implementations of that — , Mar 08 '17 at 06:48

score 0 · Answer 1 · edited May 23 '17 at 11:47

You cannot parameterize identifiers in plain SQL. You would need dynamic SQL for that, i.e.: build a query string dynamically, then execute it, which can be cumbersome and error-prone. There is a simpler alternative for your case, especially since you need to cast all columns to a common data type anyway, the natural choice being text.

It's also not possible to loop through fields of a record or row type. That's why you are currently reading metadata from the information schema to walk through columns, but you can have that simpler, too.

Use the extension hstore and its tools. Install it once per database:

Key value pair in PostgreSQL

Convert a row to hstore with the obvious function hstore():

hstore(OLD)

It returns a type hstore, which is a key/value store with all values converted to text. Now you have various options. Probably simplest: unnest witheach()` and join:

CREATE FUNCTION my_function()
  RETURNS TRIGGER AS
$func$
DECLARE
   _col text;
   _old_val text;
   _new_val text;
BEGIN
   FOR       _col, _old_val, _new_val
      SELECT key , o.value , n.value
      FROM   each(hstore(OLD)) o  -- (key, value)
      JOIN   each(hstore(NEW)) n USING (key)
      WHERE  o.value IS DISTINCT FROM n.value  -- only changed columns
   LOOP
     -- do something
   END LOOP;

   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

If all you want is to insert changed columns into an audit table, make it a single SQL command and no looping at all:

CREATE FUNCTION my_function()
  RETURNS TRIGGER AS
$func$
BEGIN
   INSERT INTO audit_table
         (tbl_schema     , tbl          , col , oldval , newval)
   SELECT TG_TABLE_SCHEMA, TG_TABLE_NAME, key , o.value, n.value
   FROM   each(hstore(OLD)) o  -- (key, value)
   JOIN   each(hstore(NEW)) n USING (key)
   WHERE  o.value IS DISTINCT FROM n.value;

   RETURN NEW;
END
$func$  LANGUAGE plpgsql;

Or you can convert hstore to a 2-dimensional key/value array with %# hstore(OLD) and loop through it like instructed here:

Postgres - array for loop

For more sophisticated needs there are audit solutions, like a_horse commented.

Get access to OLD.myLoopVariable / NEW.myLoopVariable in a plpgsql trigger function?

1 Answers1