Question of using trigger's OLD in EXECUTE

[Nim Li <nimli@xxxxxxxxxxxxxxx>]

Hello,

I'm new to PostgreSQL and wonder if anyone can help.

I'm creating an after-update-trigger for all tables, which copy the old 
records to a backup table.  In my test, the table with this trigger has 
only two columns - one BIGINT and one VARCHAR.

Also I'd like to pass the backup table's name through a parameter 
because I may reuse this function for more than one backup tables.

=====
CREATE OR REPLACE FUNCTION cp_tbl() RETURNS TRIGGER AS $proc$
   BEGIN
      EXECUTE 'INSERT INTO ' ||
      TG_ARGV[0]             ||
      ' SELECT '             ||
      OLD;
      RETURN NEW;
   END;	
$proc$ LANGUAGE plpgsql;
=====

At run-time, it prompts an error:

====
ERROR:  column "beginning" does not exist
LINE 1: INSERT INTO test_log SELECT (1,BEGINNING)
                                           ^
QUERY:  INSERT INTO test_log SELECT (1,BEGINNING)
CONTEXT:  PL/pgSQL function "cp_tbl" line 2 at EXECUTE statement
====

"beginning" is the actual data I stored in the second column of the table.

I think the issue is related to the use of OLD in the EXECUTE statement.

Does anyone have any idea how to fix it?

Many thanks!!

Nim

--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general