Get values from varying columns in a generic trigger

Question

I am new to PostgreSQL and found a trigger which serves my purpose completely except for one little thing. The trigger is quite generic and runs across different tables and logs different field changes. I found here.

What I now need to do is test for a specific field which changes as the tables change on which the trigger fires. I thought of using substr as all the column will have the same name format e.g. XXX_cust_no but the XXX can change to 2 or 4 characters. I need to log the value in theXXX_cust_no field with every record that is written to the history_ / audit table. Using a bunch of IF / ELSE statements to accomplish this is not something I would like to do.

The trigger as it now works logs the table_name, column_name, old_value, new_value. I however need to log the XXX_cust_no of the record that was changed as well.

Kiernan

Maybe you want this trigger: wiki.postgresql.org/wiki/Audit_trigger_91plus – Plumbery

IIRC you can now use EXECUTE to extract fields from OLD or NEW dynamically. – Taxidermy

Answer 1

Basically you need dynamic SQL for dynamic column names. format helps to format the DML command. Pass values from NEW and OLD with the USING clause.

Given these tables:

CREATE TABLE tbl (
  t_id serial PRIMARY KEY
 ,abc_cust_no text
);

CREATE TABLE log (
  id          int
 ,table_name  text
 ,column_name text
 ,old_value   text
 ,new_value   text
);

It could work like this:

CREATE OR REPLACE FUNCTION trg_demo()
  RETURNS TRIGGER AS
$func$
BEGIN

EXECUTE format('
   INSERT INTO log(id, table_name, column_name, old_value, new_value)
   SELECT ($2).t_id
         , $3
         , $4
         ,($1).%1$I
         ,($2).%1$I', TG_ARGV[0])
USING OLD, NEW, TG_RELNAME, TG_ARGV[0];

RETURN NEW;

END
$func$ LANGUAGE plpgsql;

CREATE TRIGGER demo
BEFORE UPDATE ON tbl
FOR EACH ROW EXECUTE PROCEDURE trg_demo('abc_cust_no'); -- col name here.

SQL Fiddle.

Related answer on dba.SE:

• How to access NEW or OLD field given only the field's name?

List of special variables visible in plpgsql trigger functions in the manual.

Arrington