| 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: | Whole Thread | Raw Message | 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 |