Re: How can I get one OLD.* field in a dynamic query inside a trigger function ?

2009/10/24 Bruno Baguette <bruno(dot)baguette(at)gmail(dot)com>:
> Le 24/10/09 06:46, Pavel Stehule a écrit :
>>
>> 2009/10/24 Bruno Baguette <bruno(dot)baguette(at)gmail(dot)com>:
>>>
>>> Hello !
>>>
>>> I'm trying to write a little trigger function with variable arguments
>>> quantity (at least one, but can be 2,3,4 arguments).
>>> Theses arguments are fields name, so only varchar variable.
>>>
>>> Since it is a dynamic query, I use the EXECUTE statement as explained on
>>>
>>> <http://www.postgresql.org/docs/8.3/interactive/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN>
>>>
>>> CREATE OR REPLACE FUNCTION delete_acl_trigger() RETURNS trigger AS
>>> $delete_acl_trigger$
>>> DECLARE
>>> BEGIN
>>>  FOR i IN 0 .. TG_NARGS LOOP
>>>   EXECUTE 'SELECT delete_acl(OLD.' || TG_ARGV[i] || ');';
>>>  END LOOP;
>>>  RETURN OLD;
>>> END;
>>> $delete_acl_trigger$ LANGUAGE plpgsql;
>>>
>>> But, when the trigger is triggered, I receive this error message :
>>> "Query failed: ERROR: OLD used in query that is not in a rule"
>>>
>>> How can I get the value of the OLD.' || TG_ARGV[i] field ?
>>
>> OLD is variable only in PLpgSQL procedure, - outside doesn't exists.
>> If you have a 8.4, you can use USING clause
>>
>> EXPLAIN 'SELECT $1.' || TG_ARGV[i] INTO somevar USING OLD;
>>
>>
>> http://www.postgresql.org/docs/8.4/static/plpgsql-statements.html#PLPGSQL-STATEMENTS-EXECUTING-DYN
>
> Hello Pavel,
>
> Thanks for your answer !
>
> Unfortunately, I'm running PostgreSQL 8.3.7 (PostgreSQL 8.3.7 on
> i586-mandriva-linux-gnu, compiled by GCC i586-mandriva-linux-gnu-gcc (GCC)
> 4.2.3 (4.2.3-6mnb1)).
>
> Since 8.4.1 is not available for Mandriva 2009.1, I can only have this
> PostgreSQL version. (I don't have root access on that server).
>
> Is there another way, usable in PostgreSQL 8.3.7, to solve my problem ?

you can use plperl or plpython for this task.

Pavel

>
> Many thanks in advance !
>
> Kind Regards,
>
> --
> Bruno Baguette - bruno(dot)baguette(at)gmail(dot)com
>