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

agora inbox for pgsql-sql@postgresql.org  
help / color / mirror / Atom feed

From: James Sharrett <jsharrett@tidemark.net>
To: pgsql-sql@postgresql.org
Subject: ERROR:  missing FROM-clause entry for table "new"
Date: Thu, 13 Sep 2012 20:40:29 -0400
Message-ID: <CC77F53D.3556%jsharrett@tidemark.net> (raw)

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.co2new.colN)

This doesn't:

sql := 'Insert into A (col1,col2,colN) ';
sql := sql || 'Select new.col1,new.co2new.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

view thread (7+ messages)  latest in thread

Message-ID: <CC77F53D.3556%jsharrett@tidemark.net>
Permalink:  ../CC77F53D.3556%25jsharrett@tidemark.net/
Also on:    postgresql.org/message-id/CC77F53D.3556%jsharrett@tidemark.net

reply

Reply instructions:

You may reply publicly to this message via plain-text email
using any one of the following methods:

* Reply to all the recipients using the --to and --cc options:
  reply via email

  To: pgsql-sql@postgresql.org
  Cc: jsharrett@tidemark.net
  Subject: Re: ERROR:  missing FROM-clause entry for table "new"
  In-Reply-To: <CC77F53D.3556%jsharrett@tidemark.net>

* Save the following mbox file, import it into your mail client,
  and reply-to-all from there: mbox

This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox