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

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

From
James Sharrett
Date:
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

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

From
David Johnston
Date:
On Sep 13, 2012, at 20:40, James Sharrett <jsharrett@tidemark.net> wrote:

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


Please read all of:

But especially 39.5.4

You want to make use of format and/or USING to pass in the values to a parameterized dynamic statement.

Note I linked to 9.2 but any recent version should have the behavior, if different section numbers.

In short the whole "NEW.name" is a variable and you need to build the statement the same way you would with any user-defined variable.

David J.

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

From
James Sharrett
Date:
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


Please read all of:

But especially 39.5.4

You want to make use of format and/or USING to pass in the values to a parameterized dynamic statement.

Note I linked to 9.2 but any recent version should have the behavior, if different section numbers.

In short the whole "NEW.name" is a variable and you need to build the statement the same way you would with any user-defined variable.

David J.


---------------------------------

Thanks for the reference David.  I'm now able to get the sql statement to run as dynamic sql with the following syntax

sql := 'Insert into A (col1,col2,…colN) ';
sql := sql || 'values($1,$2,…$N )';
Execute sql USING new.col1,new.col2…new.colN
But that still leaves me with the problem that new.col1 – colN aren't known till runtime.  My list of columns could vary from 5 to 50 depending on the specific update scenario.  Inside the sql string I can dynamically build $1 - $N using a counter in my loop that gets the appropriate column list but how do I dynamically build the USING list?  I tried put in a text variable that contained a delimited list of columns as such:

list = new.col1,new.col2…new.colN
sql := 'Insert into A (col1,col2,…colN) ';
sql := sql || 'values($1,$2,…$N )';
Execute sql USING list

But that gives the error:
  ERROR:  there is no parameter $2

LINE 1: ...endcategory_id,time_id,metric,amount)  values ($1,$2,$3,$4,$...