Received: from localhost (maia-5.hub.org [200.46.204.182])
	by postgresql.org (Postfix) with ESMTP id 275569F9756
	for <pgsql-sql-postgresql.org@postgresql.org>;
	Sat, 11 Aug 2007 04:45:15 -0300 (ADT)
Received: from postgresql.org ([200.46.204.71])
	by localhost (mx1.hub.org [200.46.204.182]) (amavisd-maia, port 10024)
	with ESMTP id 93356-03 for <pgsql-sql-postgresql.org@postgresql.org>;
	Sat, 11 Aug 2007 04:45:08 -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.246])
	by postgresql.org (Postfix) with ESMTP id BF03D9F9745
	for <pgsql-sql@postgresql.org>; Sat, 11 Aug 2007 04:45:11 -0300 (ADT)
Received: by an-out-0708.google.com with SMTP id c36so169796ana
	for <pgsql-sql@postgresql.org>; Sat, 11 Aug 2007 00:45:10 -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:in-reply-to:mime-version:content-type:references;
	b=c3SuYEfleKfQrFuVV/3y2ntoqHu+NNwyBnKAQNaWwpVrtO2dgoO5Scjy+8Z2/OPOACD2Wo+ABjy8NAeFVHMB08D/PNsQnBAns3WK4PPwIveDSHyeQyr0TJLm7tQtxALzWuV7dEV07AP98BkQucnhhNlHZtCw7PwXtPKOTZ2cXe8=
DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=beta;
	h=received:message-id:date:from:to:subject:in-reply-to:mime-version:content-type:references;
	b=QjQxTVUgb7t4ensKcTWsllAR6CAKSYdSH83F+1oUrpmC51w70cWzXOTgro5+1oSKh14goYTn/QeQOHRLDWgolENFuJOzx0XS+3rXH6VhunIxeVfJSA6T+aa+tS1Gf5NKVXdK/XayIkN23SyzFdHxoXcdkSzloCNTGtBs9bM5Uo8=
Received: by 10.100.138.2 with SMTP id l2mr493882and.1186818309954;
	Sat, 11 Aug 2007 00:45:09 -0700 (PDT)
Received: by 10.100.137.3 with HTTP; Sat, 11 Aug 2007 00:45:09 -0700 (PDT)
Message-ID: <86d227fd0708110045t16e39442xd35746eb59296362@mail.gmail.com>
Date: Sat, 11 Aug 2007 02:45:09 -0500
From: "Javier Fonseca V." <fonsecajavier@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Trigger Procedure Error: NEW used in query that is not in a rule
In-Reply-To: <86d227fd0708110043q341a2908q67e87570bca89492@mail.gmail.com>
MIME-Version: 1.0
Content-Type: multipart/alternative;
	boundary="----=_Part_39507_9015860.1186818309878"
References: <86d227fd0708110043q341a2908q67e87570bca89492@mail.gmail.com>
X-Virus-Scanned: Maia Mailguard 1.0.1
X-Archive-Number: 200708/92
X-Sequence-Number: 29030

------=_Part_39507_9015860.1186818309878
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

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.

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?

Thanks a lot,

Javier

------=_Part_39507_9015860.1186818309878
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
Content-Disposition: inline

<span class="gmail_quote"></span>
<p>Hello.</p>
<p>I&#39;m doing a Trigger Procedure in pl/pgSQL.&nbsp; It makes some kind of auditing.</p>
<p>I think that it&#39;s working alright except for the next line:</p>
<p>EXECUTE &#39;INSERT INTO &#39; || quote_ident(somedynamictablename) || &#39; SELECT new.*&#39;;</p>
<p>PostgreSQL keeps telling me: &quot;ERROR: NEW used in query that is not in a rule&quot;.&nbsp; I think that this NEW problem is because of the scope of the EXECUTE statement (outside the scope of the trigger), so it doesn&#39;t recognize the NEW record. 
</p>
<p>Maybe I could fix it concatenating column names and the &#39;new&#39; values but I want to do my trigger as flexible as possible (I have several tables to audit).</p>
<p>Somebody has any suggestion?</p>
<p>Thanks a lot,</p><span class="sg">
<p>Javier</p></span>

------=_Part_39507_9015860.1186818309878--