Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id B77559F9994 for ; Sat, 11 Aug 2007 15:45:51 -0300 (ADT) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.184]) (amavisd-maia, port 10024) with ESMTP id 31148-07 for ; Sat, 11 Aug 2007 15:45:45 -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 7B67B9F9963 for ; Sat, 11 Aug 2007 15:45:48 -0300 (ADT) Received: by an-out-0708.google.com with SMTP id c36so184195ana for ; Sat, 11 Aug 2007 11:45:47 -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=g5Cz+jGKmu2eDXhT8CDFq9BuIANVVU4UzAIpCRlAYavbclfGEaNkp28Z8RZ69GYVQ3/KST7Zx7OcqCG1dXSZ1R3Eg/UEzMiAVNppwJDSHBUoz2jpzGoQt9Z+X4B3hNKzXcLNy1QTmgPkDW9Y8uIzqKfSXzAQ4AjK9xRUK30NPYM= 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=kok+sfIogHHw8UvQW22MVdrZpi5AWL4IqEfS/7OCVDv5M6BvH9VRKdnnyqIFyUA8/OInkWu2an6QaDg1DGPp52/PpSx0cIzGPHUf9h3vX/fRK9ptJTyMJFaEKTTDAfmJt9nnQPolffbMwzmavhIkgIX4TqpALfiUZ03vtiEVCFc= Received: by 10.100.106.5 with SMTP id e5mr3906522anc.1186857947070; Sat, 11 Aug 2007 11:45:47 -0700 (PDT) Received: by 10.100.137.3 with HTTP; Sat, 11 Aug 2007 11:45:47 -0700 (PDT) Message-ID: <86d227fd0708111145o416fb6b3kb7ed1725bc9064c0@mail.gmail.com> Date: Sat, 11 Aug 2007 13:45:47 -0500 From: "Javier Fonseca V." To: "Tom Lane" Subject: Re: Trigger Procedure Error: NEW used in query that is not in a rule Cc: "Pavel Stehule" , pgsql-sql@postgresql.org In-Reply-To: <2071.1186847441@sss.pgh.pa.us> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_43086_7097184.1186857947016" References: <86d227fd0708110043q341a2908q67e87570bca89492@mail.gmail.com> <86d227fd0708110045t16e39442xd35746eb59296362@mail.gmail.com> <162867790708110116h6825b5cj7a9f93a97f1328af@mail.gmail.com> <2071.1186847441@sss.pgh.pa.us> X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/98 X-Sequence-Number: 29036 ------=_Part_43086_7097184.1186857947016 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline Yes Tom, you're right, but the real problem is that I need to use an EXECUTE statement because my table name is dynamic. In your example, you used logt as a static table name, and that doesn't need an EXECUTE statement. So I think that I'll have to rewrite a Trigger Procedure for each table and then for each column name in that table, and finally concatenate the values from the NEW record. That's what Pavel tried to explain, and that's what I was afraid of ... ... unless somebody gives me another option :) ... Anybody? Thanks for all your responses. Javier On 8/11/07, Tom Lane wrote: > > "Pavel Stehule" writes: > > NEW is only plpgsql variable. It isn't visible on SQL level. > > Correct, but: > > > You cannot use new.*, you can: > > execute 'INSERT ...' || 'VALUES(' || new.a || ',' || new.b .... > > You're both overthinking the problem. In recent releases (at least > since 8.2) you can do it without any EXECUTE. Like this: > > regression=# create table mytab (f1 int, f2 text); > CREATE TABLE > regression=# create table logt (f1 int, f2 text, ts timestamptz); > CREATE TABLE > regression=# create function myt() returns trigger as $$ > regression$# begin > regression$# insert into logt values(new.*, now()); > regression$# return new; > regression$# end$$ language plpgsql; > CREATE FUNCTION > regression=# create trigger t1 before insert on mytab for each row > regression-# execute procedure myt(); > CREATE TRIGGER > regression=# insert into mytab values(1, 'foo'); > INSERT 0 1 > regression=# insert into mytab values(2, 'bar'); > INSERT 0 1 > regression=# select * from logt; > f1 | f2 | ts > ----+-----+------------------------------- > 1 | foo | 2007-08-11 11:46:51.0286-04 > 2 | bar | 2007-08-11 11:46:57.406638-04 > (2 rows) > > > regards, tom lane > ------=_Part_43086_7097184.1186857947016 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
Yes Tom, you're right, but the real problem is that I need to use an EXECUTE statement because my table name is dynamic.  In your example, you used logt as a static table name, and that doesn't need an EXECUTE statement.
 
So I think that I'll have to rewrite a Trigger Procedure for each table and then for each column name in that table, and finally concatenate the values from the NEW record.  That's what Pavel tried to explain, and that's what I was afraid of ...
 
... unless somebody gives me another option :) ...  Anybody?
 
Thanks for all your responses.
 
Javier
 

On 8/11/07, Tom Lane <tgl@sss.pgh.pa.us> wrote:
"Pavel Stehule" <pavel.stehule@gmail.com> writes:
> NEW is only plpgsql variable. It isn't visible on SQL level.

Correct, but:

> You cannot use new.*, you can:
> execute 'INSERT ...' || 'VALUES(' || new.a || ',' || new.b ....

You're both overthinking the problem.  In recent releases (at least
since 8.2) you can do it without any EXECUTE.  Like this:

regression=# create table mytab (f1 int, f2 text);
CREATE TABLE
regression=# create table logt  (f1 int, f2 text, ts timestamptz);
CREATE TABLE
regression=# create function myt() returns trigger as $$
regression$# begin
regression$#   insert into logt values(new.*, now());
regression$#   return new;
regression$# end$$ language plpgsql;
CREATE FUNCTION
regression=# create trigger t1 before insert on mytab for each row
regression-# execute procedure myt();
CREATE TRIGGER
regression=# insert into mytab values(1, 'foo');
INSERT 0 1
regression=# insert into mytab values(2, 'bar');
INSERT 0 1
regression=# select * from logt;
f1 | f2  |              ts
----+-----+-------------------------------
1 | foo | 2007-08-11 11:46:51.0286-04
2 | bar | 2007-08-11 11:46:57.406638-04
(2 rows)


                       regards, tom lane

------=_Part_43086_7097184.1186857947016--