Thread: Accessing fields in RECORD data type using variables as field names

Hi,

I am trying to write a generic audit-trail trigger function which will
record changes on a field-by-field basis to a single table for all audited
tables.

However, I find that I can only access a field in OLD and NEW if I know the
name in advance.  Is there any way I can access the fields when I only have
a variable containing the name of the field?

If I can do this, I can make a really simple, schema-change-resistent, low
storage size audit trail for high read / low change databases.

Alistair

this is what I want to do:

    FOR mcolumn IN EXECUTE ''SELECT col FROM v_cols_tables WHERE tbl = '' ||
quote_literal(TG_RELNAME) LOOP
        vold = OLD.mcolumn.col;  <--THIS BREAKS
        vnew = NEW.mcolumn.col;  <--THIS TOO!`
        IF vold <> vnew THEN
            --do audit trail stuff in here...
        END IF;
    END LOOP;

On Wednesday 03 December 2003 13:36, Alistair Hopkins wrote:
> Hi,
>
> I am trying to write a generic audit-trail trigger function which will
> record changes on a field-by-field basis to a single table for all audited
> tables.
>
> However, I find that I can only access a field in OLD and NEW if I know the
> name in advance.  Is there any way I can access the fields when I only have
> a variable containing the name of the field?
>
> If I can do this, I can make a really simple, schema-change-resistent, low
> storage size audit trail for high read / low change databases.

Not in plpgsql, but I believe you can in pl/tcl, which I think is quite
mature.

--
  Richard Huxton
  Archonet Ltd