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

Regards
Pavel Stehule

>
> Many thanks in advance !
>
> Kind regards,
>
> --
> Bruno Baguette - bruno(dot)baguette(at)gmail(dot)com
>
> --
> Sent via pgsql-general mailing list (pgsql-general(at)postgresql(dot)org)
> To make changes to your subscription:
> http://www.postgresql.org/mailpref/pgsql-general
>