Re: Dynamic Assignment

2011/3/23 Andy Chambers <achambers(at)mcna(dot)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(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>