Re: dynamic plpgsql question - Mailing list pgsql-general

On Wed, 13 Dec 2006, Erik Jones wrote:

> Marc Evans wrote:
>>
>> 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"
> Ah, sorry, I'd just arrived at work and wasn't quite away as of yet.  AFAIK,
> plpgsql doesn't have any facilities for variable substitution in variable
> names (called variable variables in some languages).  However, if plpgsql is
> your only procedural option (plperl, I've heard, does support this feature)
> and the possible values for column name are known to you, there is a hackish
> workaround:
>
> IF(column_name = 'foo')   THEN
>  EXECUTE 'SELECT ' || NEW.foo || ';' INTO data;
> ELSIF(column_name = 'bar') THEN
>  EXECUTE 'SELECT ' || NEW.bar || ';' INTO data;
> ELSIF
> .
> .
> .
>
> You get the picture...

Thanks for the suggestion. I would be quiet content to use plperl, if I
could figure out a way to do the equivilant of plpgsql's:

   EXECUTE 'INSERT INTO ' || table_name || ' VALUES(NEW.*)';

I suppsoe that in plperl I could walk the list of keys in $_TD->{new}
building a list of columns and values that are then placed in a
spi_prepare. Would that be the recommended technique?

- Marc