agora inbox for pgsql-sql@postgresql.org
help / color / mirror / Atom feedFrom: James Sharrett <jsharrett@tidemark.net>
To: David Johnston <polobo@yahoo.com>
Cc: " <pgsql-sql@postgresql.org>
Cc: " <pgsql-sql@postgresql.org>
Subject: Re: ERROR: missing FROM-clause entry for table "new"
Date: Fri, 14 Sep 2012 10:06:50 -0400
Message-ID: <CC78AFD8.3581%jsharrett@tidemark.net> (raw)
In-Reply-To: <4A8E4483-31D7-437C-8D4E-6D0DD2AF1B02@yahoo.com>
> 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
>
Please read all of:
http://www.postgresql.org/docs/9.2/interactive/plpgsql-statements.html#PLPGS
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 := 'Insert into A (col1,col2,colN) ';
> sql := sql || 'values($1,$2,$N )';
> Execute sql USING new.col1,new.col2new.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.col2new.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,$...
view thread (7+ messages) latest in thread
Message-ID: <CC78AFD8.3581%jsharrett@tidemark.net>
Permalink: ../CC78AFD8.3581%25jsharrett@tidemark.net/
Also on: postgresql.org/message-id/CC78AFD8.3581%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, polobo@yahoo.com
Subject: Re: ERROR: missing FROM-clause entry for table "new"
In-Reply-To: <CC78AFD8.3581%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