Re: dynamic plpgsql question

Marc Evans wrote:
>
> 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
Sure, that'll work. Although, I'll admit, that with plperl I don't have
much experience so, if there's a better way of doing that, someone else
might know. Also, for a straight insert like that I don't really see
the need for using spi_prepare. Just feed the INSERT query string to
spi_exec_query.

--
erik jones <erik(at)myemma(dot)com>
software development
emma(r)