Re: Trigger Procedure Error: NEW used in query that is not in a rule

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(at)sss(dot)pgh(dot)pa(dot)us> wrote:
>
> "Pavel Stehule" <pavel(dot)stehule(at)gmail(dot)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
>