Re: dynamic plpgsql question

Marc Evans wrote:
> Hi -
>
> I am struggling with a trigger function in plpgsql, and am hoping that
> someone on this list can't show me a way to do what I need.
>
> In the trigger, TG_ARGV[0] is the name of a column that I want to
> evaluate. This code shows the concept, though is not functional:
>
> CREATE OR REPLACE FUNCTION foo() RETURNS TRIGGER AS $$
>   DECLARE
>     column_name TEXT := TG_ARGV[0];
>     data TEXT;
>   BEGIN
>     EXECUTE 'SELECT NEW.' || column_name INTO data;
>     -- ...
>   END;
> $$ LANGUAGE plpgsql;
>
> When I try to use that code, I receive:
>
> c3i=> insert into test_table values (1,1);
> ERROR:  NEW used in query that is not in a rule
> CONTEXT:  SQL statement "SELECT NEW.magic"
>
> How can I get the value of NEW.{column_name} (aka NEW.magic in this
> specific test case) into the variable data?
EXECUTE 'SELECT ' || NEW.column_name ';' INTO data;

--
erik jones <erik@myemma.com>
software development
emma(r)