From: Javier Fonseca V. <fonsecajavier@gmail.com>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: Pavel Stehule <pavel.stehule@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 13:45:47 -0500
Message-ID: <86d227fd0708111145o416fb6b3kb7ed1725bc9064c0@mail.gmail.com> (raw)
In-Reply-To: <2071.1186847441@sss.pgh.pa.us>
References: <86d227fd0708110043q341a2908q67e87570bca89492@mail.gmail.com>
<86d227fd0708110045t16e39442xd35746eb59296362@mail.gmail.com>
<162867790708110116h6825b5cj7a9f93a97f1328af@mail.gmail.com>
<2071.1186847441@sss.pgh.pa.us>
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
>
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: fonsecajavier@gmail.com, tgl@sss.pgh.pa.us, pavel.stehule@gmail.com
Subject: Re: Trigger Procedure Error: NEW used in query that is not in a rule
In-Reply-To: <86d227fd0708111145o416fb6b3kb7ed1725bc9064c0@mail.gmail.com>
* 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