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
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 |