Dynamic Assignment

2011/3/23 Andy Chambers <achambers@mcna.net>:

Hi All,

In a trigger function, I'm trying to set the variable "pkey" to be one of
the columns
in the automatic variable "NEW".  Which one depends on some metadata that is
available at
run-time.  I'm having a hard time using an automatic variable in a dynamic
execute command.

I get the error "missing FROM-clause entry for table "new""

Here's my function

create or replace function refresh_row () returns trigger as $$

declare
 pkey bigint;
begin
 execute 'select NEW.esid' into pkey;
end;
$$ language plpgsql

Obviously this particular code could be re-written as a simple assignment
but I need the
"esid" part to be dynamic.  Is this possible?

yes, it's possible

EXECUTE 'SELECT $1.' || quote_ident(attr_name) INTO pkey USING NEW;

Regards

Pavel Stehule

http://okbob.blogspot.com/2008/06/execute-using-feature-in-postgresql-84.html