Обсуждение: dynamic plpgsql question

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?

For completeness, I am using version 8.2.0.

Thanks in advance - Marc

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)

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

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

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

Marc Evans <Marc@SoftwareHackery.Com> writes:
> In the trigger, TG_ARGV[0] is the name of a column that I want to
> evaluate.

This is effectively impossible in plpgsql, because it's a statically
typed language --- it wants to know the type of every expression in
advance, and so such a thing couldn't work.  Consider using one of the
other PLs instead.

            regards, tom lane

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

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@myemma.com>
software development
emma(r)

Marc Evans wrote:
>>> 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"

Writing an SP that fetches an array or a text string with column names
given a table name as input is not that hard to write.

Simply fetch "SELECT column_name FROM information_schema.columns WHERE
schema_name = $1 AND table_name = $2" into an array and use
array_to_string() or some such to make it a comma separated string of
columns. You can use that string in your dynamic query.

Cheers.
--
Alban Hertroys
alban@magproductions.nl

magproductions b.v.

T: ++31(0)534346874
F: ++31(0)534346876
M:
I: www.magproductions.nl
A: Postbus 416
   7500 AK Enschede

// Integrate Your World //