Re: dynamic plpgsql question

On Wed, 13 Dec 2006, Erik Jones wrote:

> 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;

Thanks for the suggestion. Unfortunately, it does not work:

CREATE OR REPLACE FUNCTION foo() RETURNS TRIGGER AS $$
   DECLARE
     column_name TEXT := TG_ARGV[0];
     data TEXT;
   BEGIN
     EXECUTE 'SELECT ' || NEW.column_name || ';' INTO date;
     -- ...
   END;
$$ LANGUAGE plpgsql;

c3i=> insert into test_table values (1,1);
ERROR:  record "new" has no field "column_name"

- Marc