Thread: Dynamic Assignment

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?

Cheers,
Andy

--
Andy Chambers

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


>
> Cheers,
> Andy
>
> --
> Andy Chambers
>
> --
> Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>