[SQL] ERROR: missing FROM-clause entry for table "new"

[James Sharrett]

I'm trying to define a trigger function that looks for changes in table A
(table the trigger for the function is on) and write a delta record into
table B.  So if a record has a value of 100 in table A, and it is updated to
50, the function should write ­50 in table B. I can get the trigger to work
with static SQL statements but for the actual code, I need to use dynamic
SQL because I need to alter the insert statement to B depending on what
column in table A is altered.  I can get the correct SQL generated but when
I execute the string inside the trigger function I get an error because it
doesn't seem to be able to see the NEW table when it's run with EXECUTE.

So, this works in the trigger function:

Insert into A (col1,col2,ŠcolN)
Select new.col1,new.co2Šnew.colN)

This doesn't:

sql := 'Insert into A (col1,col2,ŠcolN) ';
sql := sql || 'Select new.col1,new.co2Šnew.colN)';
Execute sql;

ERROR:  missing FROM-clause entry for table "new"



There is nothing wrong with the resulting code from sql because if I output
the string and put it in as static SQL in my trigger it works.



How do I build the string within the trigger and execute it with a reference
to NEW?


Thanks in advance for the help,
James