Capture columns in plpgsql during UPDATE

Question

I am writing a trigger in plpgsql for Postgres 9.1. I need to be able to capture the column names that were issued in the SET clause of an UPDATE so I can record the specified action in an audit table. The examples in the Postgres documentation are simple and inadequate for my needs. I have searched the internet for days and I am unable to find any other examples that try to achieve what I want to do here.

I am on a tight schedule to resolve this soon. I don't know Tcl so pl/Tcl is out of the question for me at this point. pl/Perl may work but I don't know where to start with it. Also I wanted to find a way to accomplish this in pl/pgsql if at all possible for portability and maintenance. If someone can recommend a pl/Perl solution to this I would be grateful.

Here is the table structure of the target table that will be audited:

Note: There are many other columns in the record table but I have not listed them here in order to keep things simple. But the trigger should be able to record changes to any of the columns in the row.

CREATE TABLE record (
   record_id    integer NOT NULL PRIMARY KEY,
   lastname     text,
   frstname     text,
   dob          date,
   created      timestamp default NOW(),
   created_by   integer,
   inactive     boolean default false
);
create sequence record_record_id_seq;
alter table record alter record_id set default nextval('record_record_id_seq');

Here is my audit table:

CREATE TABLE record_audit (
   id                integer NOT NULL PRIMARY KEY,
   operation         char(1) NOT NULL, -- U, I or D
   source_column     text,
   source_id         integer,
   old_value         text,
   new_value         text,
   created_date      timestamp default now(),
   created_by        integer
);
create sequence record_audit_id_seq;
alter table record_audit alter id set default nextval('record_audit_id_seq');

My goal is to record INSERTS and UPDATES to the record table in the record_audit table that will detail not only what the target record_id was (source_id) that was updated and what column was updated (source_column), but also the old_value and the new_value of the column.

I understand that the column values will have to be CAST() to a type of text. I believe I can access the old_value and new_value by accessing NEW and OLD but I am having difficulty figuring out how to obtain the column names used in the SET clause of the UPDATE query. I need the trigger to add a new record to the record_audit table for every column specified in the SET clause. Note, there are not DELETE actions as records are simply UPDATED to inactive = 't' (and thus recorded in the audit table)

Here is my trigger so far (obviously incomplete). Please forgive me, I am learning pl/pgsql as I go.

-- Trigger function for record_audit table
CREATE OR REPLACE FUNCTION audit_record() RETURNS TRIGER AS $$
   DECLARE
      insert_table  text;
      ref_col       text; --how to get the referenced column name??

   BEGIN
      --
      -- Create a new row in record_audit depending on the operation (TG_OP)
      --
      IF (TG_OP = 'INSERT') THEN

         -- old_value and new_value are meaningless for INSERTs. Just record the new ID.
         INSERT INTO record_audit 
            (operation,source_id,created_by) 
         VALUES
            ('I', NEW.record_id, NEW.created_by);


      ELSIF (TG_OP = 'UPDATE') THEN

         FOR i in 1 .. TG_ARGV[0] LOOP
            ref_col := TG_ARGV[i].column; -- I know .column doesn't exist but what to use?

            INSERT INTO record_audit 
               (operation, source_column, source_id, old_value, new_value, created_by) 
            VALUES 
               ('U', ref_col, NEW.record_id, OLD.ref_col, NEW.ref_col, NEW.created_by); 
         END LOOP;

      END IF;
      RETURN NULL; -- result is ignored anyway since this is an AFTER trigger
   END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER record_audit_trig
AFTER INSERT OR UPDATE on record
   FOR EACH ROW EXECUTE PROCEDURE audit_record();

Thanks for reading this long and winding question!

Answer 1

you cannot to get this information - not in PL level - probably it is possible in C.

Good enough solution is based on changed fields in records NEW and OLD. You can get list of fields from system tables ~ are related to table that is joined to trigger.