From: Tom Lane <tgl@sss.pgh.pa.us>
To: Pavel Stehule <pavel.stehule@gmail.com>
Cc: Javier Fonseca V. <fonsecajavier@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Trigger Procedure Error: NEW used in query that is not in a rule
Date: Sat, 11 Aug 2007 11:50:41 -0400
Message-ID: <2071.1186847441@sss.pgh.pa.us> (raw)
In-Reply-To: <162867790708110116h6825b5cj7a9f93a97f1328af@mail.gmail.com>
References: <86d227fd0708110043q341a2908q67e87570bca89492@mail.gmail.com>
<86d227fd0708110045t16e39442xd35746eb59296362@mail.gmail.com>
<162867790708110116h6825b5cj7a9f93a97f1328af@mail.gmail.com>
"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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: tgl@sss.pgh.pa.us, pavel.stehule@gmail.com, fonsecajavier@gmail.com
Subject: Re: Trigger Procedure Error: NEW used in query that is not in a rule
In-Reply-To: <2071.1186847441@sss.pgh.pa.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox