Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id E29259F971A for ; Wed, 15 Aug 2007 21:05:57 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 25071-01 for ; Wed, 15 Aug 2007 21:05:53 -0300 (ADT) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.5 Received: from an-out-0708.google.com (an-out-0708.google.com [209.85.132.250]) by postgresql.org (Postfix) with ESMTP id F15DA9F96EA for ; Wed, 15 Aug 2007 21:05:53 -0300 (ADT) Received: by an-out-0708.google.com with SMTP id c36so11838ana for ; Wed, 15 Aug 2007 17:05:52 -0700 (PDT) DKIM-Signature: a=rsa-sha1; c=relaxed/relaxed; d=gmail.com; s=beta; h=domainkey-signature:received:received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=QPRqq/gMFB2LiXX3SHGHYasHJbhXIDOi9aoTfUX0UNXsRIURGDjbxLjSQ1Ycex2bErGLfOnHv8okPWeoffnA2DHavZEoTtdx6Wl+Wy18tWdFAWDd807eCX000lMY1uccet1iz+P+WZijFRzDlNHOz4rYorpmXIMIeoWfN46xrcA= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta; h=received:message-id:date:from:to:subject:cc:in-reply-to:mime-version:content-type:references; b=K5X1RBncRvcl+Rxrh65HRScBzK6+G4z9gRzpxw97cl0TfxIGcT3IKpq34ySD+8Rfly18kjte0f0+5u4JUzrb+7rzQBltCPZZrocczS1Kh75ou+I5kpqrYQPqEfrrXyYohSfB0wSmYct+zzwTcbb2xXFROZlQVCQ0YvVu5E3p2sY= Received: by 10.70.46.1 with SMTP id t1mr1838275wxt.1187222752002; Wed, 15 Aug 2007 17:05:52 -0700 (PDT) Received: by 10.100.137.3 with HTTP; Wed, 15 Aug 2007 17:05:51 -0700 (PDT) Message-ID: <86d227fd0708151705g17206becm8a1794e4c9bbde6b@mail.gmail.com> Date: Wed, 15 Aug 2007 19:05:51 -0500 From: "Javier Fonseca V." To: Decibel! Subject: Re: Trigger Procedure Error: NEW used in query that is not in a rule Cc: pgsql-general@postgresql.org In-Reply-To: <20070815182833.GP54135@nasby.net> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_123537_1093250.1187222751964" References: <86d227fd0708110043q341a2908q67e87570bca89492@mail.gmail.com> <20070815182833.GP54135@nasby.net> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/892 X-Sequence-Number: 118741 ------=_Part_123537_1093250.1187222751964 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline The first time that I read it, the work-around sounded a little odd (extreme!) to me... but actually it would really work!. Thanks for the tip :) *Javier* On 8/15/07, Decibel! wrote: > > On Sat, Aug 11, 2007 at 02:43:30AM -0500, Javier Fonseca V. wrote: > > Hello. > > > > I'm doing a Trigger Procedure in pl/pgSQL. It makes some kind of > auditing. > > > > I think that it's working alright except for the next line: > > > > EXECUTE 'INSERT INTO ' || quote_ident(somedynamictablename) || ' SELECT > > new.*'; > > > > PostgreSQL keeps telling me: "ERROR: NEW used in query that is not in a > > rule". I think that this NEW problem is because of the scope of the > EXECUTE > > statement (outside the scope of the trigger), so it doesn't recognize > the > > NEW record. > > Sort-of... the issue is that EXECUTE hands the string off to the > backend, which has no clue what "NEW" is; only the trigger procedure > understands NEW. > > > Maybe I could fix it concatenating column names and the 'new' values but > I > > want to do my trigger as flexible as possible (I have several tables to > > audit). > > > > Somebody has any suggestion? > > You could theoretically make the trigger entirely dynamic by having it > pull the needed info out of the system catalogs... but I wouldn't want > to see the performance of that... If you care about performance *at > all*, I'd suggest writing some code that will generate the triggers for > a given table for you. I don't expect it'd be much harder than writing a > completely dynamic trigger. > -- > Decibel!, aka Jim Nasby decibel@decibel.org > EnterpriseDB http://enterprisedb.com 512.569.9461 (cell) > > ------=_Part_123537_1093250.1187222751964 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
The first time that I read it, the work-around sounded a little odd (extreme!) to me... but actually it would really work!.
 
Thanks for the tip :)
 
Javier

 
On 8/15/07, Decibel! <decibel@decibel.org> wrote:
On Sat, Aug 11, 2007 at 02:43:30AM -0500, Javier Fonseca V. wrote:
> Hello.
>
> I'm doing a Trigger Procedure in pl/pgSQL.  It makes some kind of auditing.
>
> I think that it's working alright except for the next line:
>
> EXECUTE 'INSERT INTO ' || quote_ident(somedynamictablename) || ' SELECT
> new.*';
>
> PostgreSQL keeps telling me: "ERROR: NEW used in query that is not in a
> rule".  I think that this NEW problem is because of the scope of the EXECUTE
> statement (outside the scope of the trigger), so it doesn't recognize the
> NEW record.

Sort-of... the issue is that EXECUTE hands the string off to the
backend, which has no clue what "NEW" is; only the trigger procedure
understands NEW.

> Maybe I could fix it concatenating column names and the 'new' values but I
> want to do my trigger as flexible as possible (I have several tables to
> audit).
>
> Somebody has any suggestion?

You could theoretically make the trigger entirely dynamic by having it
pull the needed info out of the system catalogs... but I wouldn't want
to see the performance of that... If you care about performance *at
all*, I'd suggest writing some code that will generate the triggers for
a given table for you. I don't expect it'd be much harder than writing a
completely dynamic trigger.
--
Decibel!, aka Jim Nasby                        decibel@decibel.org
EnterpriseDB      http://enterprisedb.com       512.569.9461 (cell)


------=_Part_123537_1093250.1187222751964--