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

From: Pavel Stehule <pavel(dot)stehule(at)gmail(dot)com>
To: Bruno Baguette <bruno(dot)baguette(at)gmail(dot)com>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: How can I get one OLD.* field in a dynamic query inside a trigger function ?
Date: 2009-10-24 04:46:47
Message-ID: 162867790910232146s7b363b5ej29c0d40856be8585@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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
>

In response to

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Scott Marlowe 2009-10-24 06:43:56 Re: auto-filling a field on insert
Previous Message Bruno Baguette 2009-10-24 03:58:37 How can I get one OLD.* field in a dynamic query inside a trigger function ?