Received: from localhost (maia-3.hub.org [200.46.204.184]) by postgresql.org (Postfix) with ESMTP id 5C2279F96D5 for ; Sat, 11 Aug 2007 12:50:48 -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 90320-09 for ; Sat, 11 Aug 2007 12:50:43 -0300 (ADT) X-Greylist: from auto-whitelisted by SQLgrey-1.7.5 Received: from sss.pgh.pa.us (sss.pgh.pa.us [66.207.139.130]) by postgresql.org (Postfix) with ESMTP id 85B5E9F96CF for ; Sat, 11 Aug 2007 12:50:43 -0300 (ADT) Received: from sss2.sss.pgh.pa.us (tgl@localhost [127.0.0.1]) by sss.pgh.pa.us (8.14.1/8.14.1) with ESMTP id l7BFofP4002072; Sat, 11 Aug 2007 11:50:41 -0400 (EDT) To: "Pavel Stehule" cc: "Javier Fonseca V." , pgsql-sql@postgresql.org Subject: Re: Trigger Procedure Error: NEW used in query that is not in a rule In-reply-to: <162867790708110116h6825b5cj7a9f93a97f1328af@mail.gmail.com> References: <86d227fd0708110043q341a2908q67e87570bca89492@mail.gmail.com> <86d227fd0708110045t16e39442xd35746eb59296362@mail.gmail.com> <162867790708110116h6825b5cj7a9f93a97f1328af@mail.gmail.com> Comments: In-reply-to "Pavel Stehule" message dated "Sat, 11 Aug 2007 10:16:47 +0200" Date: Sat, 11 Aug 2007 11:50:41 -0400 Message-ID: <2071.1186847441@sss.pgh.pa.us> From: Tom Lane X-Virus-Scanned: Maia Mailguard 1.0.1 X-Archive-Number: 200708/96 X-Sequence-Number: 29034 "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