"OLD." || myColumnNameVar (How to generically access columns in a trigger's OLD or NEW records) - Mailing list pgsql-general

In PL/pgSQL, how does one generically access the fields of the OLD or NEW record?

I've tried code such as this:
  'NEW.' || quote_ident( myColumnNameVar ) || '::varchar'

But when run by an "EXECUTE" command, I get errors such as:
  ERROR:  missing FROM-clause entry for table "old"
  SQL state: 42P01

It seems that I cannot get PL/pgSQL to interpret the text of "NEW." + column name as text.

My goal is to loop each field in a trigger, comparing the "OLD." & "NEW." values of each field. If different I want to
logboth values in a history/audit-trail table. 

Is there some way to loop the fields of a trigger's Record? I've read other people's frustration at not being able to
getan array of fields from the Record. 

My approach is to fake it: Get the table's columns and data types by querying the meta-data tables (pg_attribute,
pg_class,pg_type). But I cannot get "NEW." || colNameVar to be interpreted. Perhaps there is a better approach. 

If anyone is curious, my source code is pasted below.

--Basil Bourque

------------
CREATE OR REPLACE FUNCTION "table_make_history_"() RETURNS "trigger" AS
$BODY$

DECLARE
    metadata_record RECORD;
    /* http://www.postgresql.org/docs/current/static/plpgsql-declarations.html#PLPGSQL-DECLARATION-RECORDS */
    table_dot_column VARCHAR;
    my_sql VARCHAR;
    column_is_loggable_var BOOLEAN;
    edited_var BOOLEAN;

BEGIN

IF (TG_OP = 'INSERT') THEN
    -- Ignore this case

ELSIF (TG_OP = 'UPDATE') THEN

    /* Get a list of column name, data type, and position with in table.
       attname = Name of column.
       atttypid = Data type of column (as an oid from pg_type.oid)
    */
    FOR metadata_record IN
     SELECT attname::varchar AS "nth_col_name", pg_type.typname::varchar as "nth_col_type", pg_attribute.attnum
     FROM pg_attribute, pg_class, pg_type
     WHERE attrelid = pg_class.oid
     AND pg_attribute.attisdropped = False
     AND relname = TG_TABLE_NAME
     AND attnum > 0
     AND atttypid = pg_type.oid
    LOOP -- For each table in the table calling this trigger.
        -- Now "metadata_record" has one record from resultset of SELECT query above.
        --table_dot_column := TG_TABLE_NAME::VARCHAR || '.' || metadata_record.nth_col_name ;
        column_is_loggable_var := position( '_x_' in metadata_record.nth_col_name ) < 1 ;
        IF column_is_loggable_var THEN
        -- The name of column in question does NOT contain "_x_". So, proceed to possibly log modified data.
        -- See if the NEW-OLD values are different.
            edited_var := true; -- Next line fails.
            --EXECUTE 'edited_var := ( OLD.' || quote_ident(metadata_record.nth_col_name) || ' = NEW.' ||
quote_ident(metadata_record.nth_col_name)|| ' );' ; 
            PERFORM 'edited_var := ( OLD.' || quote_ident(metadata_record.nth_col_name) || ' <> NEW.' ||
quote_ident(metadata_record.nth_col_name)|| ' );' ; 
            IF edited_var THEN
                EXECUTE 'INSERT INTO history_ ( operation_, table_oid_, table_name_, uuid_, column_name_, old_value_,
new_value_) ' 
                    || 'VALUES ( ' || quote_literal(TG_OP) || ', ' || TG_RELID || ', ' || quote_literal(TG_TABLE_NAME)
||', ' || quote_literal(OLD.pkey_) || '::uuid, '  
                    || quote_literal(metadata_record.nth_col_name)
                    || ', OLD.'
                    || quote_ident(metadata_record.nth_col_name) || '::varchar'
                    || ', NEW.'
                    || quote_ident(metadata_record.nth_col_name) || '::varchar'
                    || ' ); ' ;
            END IF;

        END IF;
    END LOOP;

    RETURN NEW;

ELSIF (TG_OP = 'DELETE') THEN
    -- ignore this case

END IF;

RETURN NULL; /* Should never reach this point. Perhaps we should raise an error here. */

END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE;
--------------