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