Received: from makus.postgresql.org ([98.129.198.125]) by malur.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TCJxY-0001Tq-Jt for pgsql-sql@postgresql.org; Fri, 14 Sep 2012 00:40:40 +0000 Received: from mail-pb0-f46.google.com ([209.85.160.46]) by makus.postgresql.org with esmtp (Exim 4.72) (envelope-from ) id 1TCJxV-0007tu-Ob for pgsql-sql@postgresql.org; Fri, 14 Sep 2012 00:40:39 +0000 Received: by pbbrr13 with SMTP id rr13so4797294pbb.19 for ; Thu, 13 Sep 2012 17:40:36 -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:message-id:thread-topic :mime-version:content-type:x-gm-message-state; bh=B+gp7t+9ujrdEjCytlP2oYb20VsZNL8wQbQ+oKCaSn0=; b=f3nl3fVrRADNmNJOOV8qpAsC5mGs5c+Y6TLl1TiHN9JpeHTIrIqHraZK9v0OyqUt5U LxFx7LW4GDmZHrUUzK0gfLyQcVOizpXNKCG9JMBa9F+IyalBovFIy32oR95WuhXWfk4z rDA6f0MEGRW5qvDRQRpf0oY45QMN6+y4N8TUSlKTYGp/vdQDIIKomMtJB4FyEJWUk54g cGXXkCvc+hdH6h2XXulRvmA+kX2tpCDUBsQpnTvU7dIjkAyoTXQo1BURli9fKo0zNA2Q wl9ZkUUBlvsK641lBOahkaxiSkWPs+oRSAyooIMosViEb9swiC+33Twg/WWIAcxDiqd/ UX0g== Received: by 10.68.138.169 with SMTP id qr9mr2271086pbb.27.1347583236465; Thu, 13 Sep 2012 17:40:36 -0700 (PDT) Received: from [10.32.0.158] (66-7-234-38.static-ip.telepacific.net. [66.7.234.38]) by mx.google.com with ESMTPS id ko8sm138903pbc.40.2012.09.13.17.40.33 (version=SSLv3 cipher=OTHER); Thu, 13 Sep 2012 17:40:34 -0700 (PDT) User-Agent: Microsoft-MacOutlook/14.2.3.120616 Date: Thu, 13 Sep 2012 20:40:29 -0400 Subject: ERROR: missing FROM-clause entry for table "new" From: James Sharrett To: Message-ID: Thread-Topic: ERROR: missing FROM-clause entry for table "new" Mime-version: 1.0 Content-type: multipart/alternative; boundary="B_3430413633_2009253" X-Gm-Message-State: ALoCoQkz51J/JCAbJ8jkeJv+lJd+TZ6z0sc1PaoA8dm5PpcvwBBIjwqnwkDAwJtD/vtGHzEByQi4 X-Pg-Spam-Score: -2.6 (--) X-Archive-Number: 201209/38 X-Sequence-Number: 36840 > 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_3430413633_2009253 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 t= o 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 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,=8AcolN) Select new.col1,new.co2=8Anew.colN) This doesn't: sql :=3D 'Insert into A (col1,col2,=8AcolN) '; sql :=3D sql || 'Select new.col1,new.co2=8Anew.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 referenc= e to NEW? Thanks in advance for the help, James --B_3430413633_2009253 Content-type: text/html; charset="ISO-8859-1" Content-transfer-encoding: quoted-printable

I'm trying to define a trigg= er function that looks for changes in table A (table the trigger for the fun= ction 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 wri= te –50 in table B. I can get the trigger to work with static SQL state= ments but for the actual code, I need to use dynamic SQL because I need to a= lter the insert statement to B depending on what column in table A is altere= d. I can get the correct SQL generated but when I execute the string i= nside 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 n= ew.col1,new.co2…new.colN)';

Execute sql;

ERROR:&= nbsp; missing FROM-clause entry for table "new"

There is nothing wrong with the resulting c= ode from sql because if I output the string and put it in as static SQL in m= y trigger it works.

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

Thanks in advance for the help,

James

--B_3430413633_2009253--