Re: Trigger Procedure Error: NEW used in query that is not in a rule - Mailing list pgsql-sql

"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