Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TCWXu-0006fd-G5 for pgsql-sql@postgresql.org; Fri, 14 Sep 2012 14:07:02 +0000 Received: from mail-pz0-f46.google.com ([209.85.210.46]) by magus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TCWXq-0007MA-5H for pgsql-sql@postgresql.org; Fri, 14 Sep 2012 14:07:01 +0000 Received: by dady13 with SMTP id y13so2441730dad.19 for ; Fri, 14 Sep 2012 07:06:56 -0700 (PDT) X-Google-DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=google.com; s=20120113; h=user-agent:date:subject:from:to:cc:message-id:thread-topic :in-reply-to:mime-version:content-type:x-gm-message-state; bh=nMikbbgrDjX4yaB1Af0D+HgbNWn/7gzAWS3dqMv7TNU=; b=F8uAxeWsYiBKgd39XAecZBziE21hHWkCgm9J9xCo15T0oVAvBY6149o9CqCoOyUUMk 7xz2u//VE0w5zl9aXdStpbp94uIlUG+G4TKCrVWnpWV3HEAG/oEW0RH3vhdShSprHvXq EA5zOs4P0ZuZMuor5dZqASiss4I9MFhY4oc4WXQhM35Dnq1AadbNEjZ/iRmylxm2POrb p7dLvBPVxrsu+fhDWAfTzusKaxLgKSU5CwVC5PiOlAwXX2vhzN7TIHVpWLYZikrV5Wq0 /2hFWHC+DZu6b/WQ+HCb4nykObTFmwDxqS7a5F1FkuGGU7VPFIhiC0mIu2e2dkcaYjrC kldA== Received: by 10.68.218.100 with SMTP id pf4mr5156370pbc.122.1347631616017; Fri, 14 Sep 2012 07:06:56 -0700 (PDT) Received: from [10.150.155.213] ([12.177.140.2]) by mx.google.com with ESMTPS id kj10sm1027493pbc.72.2012.09.14.07.06.53 (version=SSLv3 cipher=OTHER); Fri, 14 Sep 2012 07:06:55 -0700 (PDT) User-Agent: Microsoft-MacOutlook/14.2.3.120616 Date: Fri, 14 Sep 2012 10:06:50 -0400 Subject: Re: ERROR: missing FROM-clause entry for table "new" From: James Sharrett To: David Johnston CC: "" Message-ID: Thread-Topic: [SQL] ERROR: missing FROM-clause entry for table "new" In-Reply-To: <4A8E4483-31D7-437C-8D4E-6D0DD2AF1B02@yahoo.com> Mime-version: 1.0 Content-type: multipart/alternative; boundary="B_3430462014_73772" X-Gm-Message-State: ALoCoQkQKrRDv+LyA3VO/3bi/X/OgQVDv+RHEHp4mCZ79vQwUI0iE2s7UorFERiE6UYm/b5++/Tz X-Pg-Spam-Score: -2.6 (--) X-Archive-Number: 201209/41 X-Sequence-Number: 36843 > This message is in MIME format. Since your mail reader does not understand this format, some or all of this message may not be legible. --B_3430462014_73772 Content-type: text/plain; charset="ISO-8859-1" Content-transfer-encoding: quoted-printable > 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 =AD50 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 colum= n in > table A is altered. I can get the correct SQL generated but when I execu= te > 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. >=20 > So, this works in the trigger function: >=20 > Insert into A (col1,col2,=8AcolN) > Select new.col1,new.co2=8Anew.colN) >=20 > This doesn't: >=20 > sql :=3D 'Insert into A (col1,col2,=8AcolN) '; > sql :=3D sql || 'Select new.col1,new.co2=8Anew.colN)'; > Execute sql; >=20 > ERROR: missing FROM-clause entry for table "new" >=20 >=20 >=20 > There is nothing wrong with the resulting code from sql because if I outp= ut > the string and put it in as static SQL in my trigger it works. >=20 >=20 >=20 > How do I build the string within the trigger and execute it with a refere= nce > to NEW? >=20 >=20 > Thanks in advance for the help, > James >=20 Please read all of: http://www.postgresql.org/docs/9.2/interactive/plpgsql-statements.html#PLPG= S QL-STATEMENTS-EXECUTING-DYN 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 :=3D 'Insert into A (col1,col2,=8AcolN) '; > sql :=3D sql || 'values($1,$2,=8A$N )'; > Execute sql USING new.col1,new.col2=8Anew.colN But that still leaves me with the problem that new.col1 =AD 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 =3D new.col1,new.col2=8Anew.colN > sql :=3D 'Insert into A (col1,col2,=8AcolN) '; > sql :=3D sql || 'values($1,$2,=8A$N )'; > Execute sql USING list >=20 > But that gives the error: > ERROR: there is no parameter $2 LINE 1: ...endcategory_id,time_id,metric,amount) values ($1,$2,$3,$4,$... --B_3430462014_73772 Content-type: text/html; charset="ISO-8859-1" Content-transfer-encoding: quoted-printable <= div>
I'm trying= to define a trigger function that looks for changes in table A (table the t= rigger 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 f= unction should write –50 in table B. I can get the trigger to work wit= h static SQL statements but for the actual code, I need to use dynamic SQL b= ecause 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 ex= ecute 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 :=3D 'Insert into A (col1,col2,…colN) ';
sql := =3D sql || 'Select new.col1,new.co2…new.colN)';
Execute sql;

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


There is nothing wrong wi= th 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 exec= ute it with a reference to NEW?


Thanks in advan= ce for the help,
James


Please read all of:

But especially 39.= 5.4

You want to make use of format and/or USI= NG to pass in the values to a parameterized dynamic statement.
Note I linked to 9.2 but any recent version should have the beha= vior, if different section numbers.

In short the wh= ole "NEW.name" is a variable and you need to build the statement the same wa= y 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 f= ollowing syntax

sql :=3D= 'Insert into A (col1,col2,…colN) ';
sql :=3D sql || 'values($= 1,$2,…$N )';
Execute sql USING new.col1,new.col2…= new.colN
But that still leaves me with the problem that new.col= 1 – colN aren't known till runtime.  My list of columns could var= y from 5 to 50 depending on the specific update scenario.  Inside the s= ql string I can dynamically build $1 - $N using a counter in my loop that ge= ts 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 co= lumns as such:

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

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

LINE 1: ...e= ndcategory_id,time_id,metric,amount)  values ($1,$2,$3,$4,$...


 --B_3430462014_73772--