Question of using trigger's OLD in EXECUTE

From: Nim Li <nimli(at)sri(dot)utoronto(dot)ca>
To: pgsql-general(at)postgresql(dot)org
Subject: Question of using trigger's OLD in EXECUTE
Date: 2009-10-23 15:27:11
Message-ID: 4AE1CB4F.4020603@sri.utoronto.ca
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Alvaro Herrera 2009-10-23 15:29:21 Re: pg 8.4 (Auto)-vacuumlo
Previous Message Tom Lane 2009-10-23 15:24:33 Re: How to check whether a data type can be cast to another