On Sep 13, 2012, at 20:40, James Sharrett <jsharr...@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: http://www.postgresql.org/docs/9.2/interactive/plpgsql-statements.html#PLPGSQL-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.
