pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedTrigger Procedure Error: NEW used in query that is not in a rule
11+ messages / 7 participants
[nested] [flat]
* Trigger Procedure Error: NEW used in query that is not in a rule
@ 2007-08-11 07:43 Javier Fonseca V. <fonsecajavier@gmail.com>
2007-08-11 07:45 ` Trigger Procedure Error: NEW used in query that is not in a rule Javier Fonseca V. <fonsecajavier@gmail.com>
2007-08-12 11:33 ` Re: Trigger Procedure Error: NEW used in query that is not in a rule Lew <lew@lewscanon.com>
2007-08-15 18:28 ` Re: Trigger Procedure Error: NEW used in query that is not in a rule Decibel! <decibel@decibel.org>
2011-09-21 15:32 ` Re: Trigger Procedure Error: NEW used in query that is not in a rule jablonov <jablonov@hotmail.com>
0 siblings, 4 replies; 11+ messages in thread
From: Javier Fonseca V. @ 2007-08-11 07:43 UTC (permalink / raw)
To: pgsql-general@postgresql.org
Hello.
I'm doing a Trigger Procedure in pl/pgSQL. It makes some kind of auditing.
I think that it's working alright except for the next line:
EXECUTE 'INSERT INTO ' || quote_ident(somedynamictablename) || ' SELECT
new.*';
PostgreSQL keeps telling me: "ERROR: NEW used in query that is not in a
rule". I think that this NEW problem is because of the scope of the EXECUTE
statement (outside the scope of the trigger), so it doesn't recognize the
NEW record.
Maybe I could fix it concatenating column names and the 'new' values but I
want to do my trigger as flexible as possible (I have several tables to
audit).
Somebody has any suggestion?
Thanks a lot,
Javier
^ permalink raw reply [nested|flat] 11+ messages in thread
* Trigger Procedure Error: NEW used in query that is not in a rule
2007-08-11 07:43 Trigger Procedure Error: NEW used in query that is not in a rule Javier Fonseca V. <fonsecajavier@gmail.com>
@ 2007-08-11 07:45 ` Javier Fonseca V. <fonsecajavier@gmail.com>
2007-08-11 08:16 ` Re: Trigger Procedure Error: NEW used in query that is not in a rule Pavel Stehule <pavel.stehule@gmail.com>
2007-08-11 19:07 ` Re: Trigger Procedure Error: NEW used in query that is not in a rule hubert depesz lubaczewski <depesz@depesz.com>
3 siblings, 2 replies; 11+ messages in thread
From: Javier Fonseca V. @ 2007-08-11 07:45 UTC (permalink / raw)
To: pgsql-sql
Hello.
I'm doing a Trigger Procedure in pl/pgSQL. It makes some kind of auditing.
I think that it's working alright except for the next line:
EXECUTE 'INSERT INTO ' || quote_ident(somedynamictablename) || ' SELECT
new.*';
PostgreSQL keeps telling me: "ERROR: NEW used in query that is not in a
rule". I think that this NEW problem is because of the scope of the EXECUTE
statement (outside the scope of the trigger), so it doesn't recognize the
NEW record.
Maybe I could fix it concatenating column names and the 'new' values but I
want to do my trigger as flexible as possible (I have several tables to
audit).
Somebody has any suggestion?
Thanks a lot,
Javier
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Trigger Procedure Error: NEW used in query that is not in a rule
2007-08-11 07:43 Trigger Procedure Error: NEW used in query that is not in a rule Javier Fonseca V. <fonsecajavier@gmail.com>
2007-08-11 07:45 ` Trigger Procedure Error: NEW used in query that is not in a rule Javier Fonseca V. <fonsecajavier@gmail.com>
@ 2007-08-11 08:16 ` Pavel Stehule <pavel.stehule@gmail.com>
2007-08-11 15:50 ` Re: Trigger Procedure Error: NEW used in query that is not in a rule Tom Lane <tgl@sss.pgh.pa.us>
1 sibling, 1 reply; 11+ messages in thread
From: Pavel Stehule @ 2007-08-11 08:16 UTC (permalink / raw)
To: Javier Fonseca V. <fonsecajavier@gmail.com>; +Cc: pgsql-sql
NEW is only plpgsql variable. It isn't visible on SQL level. You
cannot use new.*, you can:
execute 'INSERT ...' || 'VALUES(' || new.a || ',' || new.b ....
regards
Pavel
2007/8/11, Javier Fonseca V. <fonsecajavier@gmail.com>:
>
>
> Hello.
>
> I'm doing a Trigger Procedure in pl/pgSQL. It makes some kind of auditing.
>
> I think that it's working alright except for the next line:
>
> EXECUTE 'INSERT INTO ' || quote_ident(somedynamictablename)
> || ' SELECT new.*';
>
> PostgreSQL keeps telling me: "ERROR: NEW used in query that is not in a
> rule". I think that this NEW problem is because of the scope of the EXECUTE
> statement (outside the scope of the trigger), so it doesn't recognize the
> NEW record.
>
> Maybe I could fix it concatenating column names and the 'new' values but I
> want to do my trigger as flexible as possible (I have several tables to
> audit).
>
> Somebody has any suggestion?
>
> Thanks a lot,
>
> Javier
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Trigger Procedure Error: NEW used in query that is not in a rule
2007-08-11 07:43 Trigger Procedure Error: NEW used in query that is not in a rule Javier Fonseca V. <fonsecajavier@gmail.com>
2007-08-11 07:45 ` Trigger Procedure Error: NEW used in query that is not in a rule Javier Fonseca V. <fonsecajavier@gmail.com>
2007-08-11 08:16 ` Re: Trigger Procedure Error: NEW used in query that is not in a rule Pavel Stehule <pavel.stehule@gmail.com>
@ 2007-08-11 15:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-08-11 16:10 ` Re: Trigger Procedure Error: NEW used in query that is not in a rule Pavel Stehule <pavel.stehule@gmail.com>
2007-08-11 18:45 ` Re: Trigger Procedure Error: NEW used in query that is not in a rule Javier Fonseca V. <fonsecajavier@gmail.com>
0 siblings, 2 replies; 11+ messages in thread
From: Tom Lane @ 2007-08-11 15:50 UTC (permalink / raw)
To: Pavel Stehule <pavel.stehule@gmail.com>; +Cc: Javier Fonseca V. <fonsecajavier@gmail.com>; 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
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Trigger Procedure Error: NEW used in query that is not in a rule
2007-08-11 07:43 Trigger Procedure Error: NEW used in query that is not in a rule Javier Fonseca V. <fonsecajavier@gmail.com>
2007-08-11 07:45 ` Trigger Procedure Error: NEW used in query that is not in a rule Javier Fonseca V. <fonsecajavier@gmail.com>
2007-08-11 08:16 ` Re: Trigger Procedure Error: NEW used in query that is not in a rule Pavel Stehule <pavel.stehule@gmail.com>
2007-08-11 15:50 ` Re: Trigger Procedure Error: NEW used in query that is not in a rule Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-08-11 16:10 ` Pavel Stehule <pavel.stehule@gmail.com>
1 sibling, 0 replies; 11+ messages in thread
From: Pavel Stehule @ 2007-08-11 16:10 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Javier Fonseca V. <fonsecajavier@gmail.com>; pgsql-sql
2007/8/11, Tom Lane <tgl@sss.pgh.pa.us>:
> "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
>
I know it Tom. But original question contains
EXECUTE 'INSERT INTO ' || quote_ident(SOMEDYNAMICNTABLENAME) || ' SELECT new.*';
and then he needs EXECUTE (propably).
but new.* in insert is nice feature.
Regards
Pavel Stehule
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Trigger Procedure Error: NEW used in query that is not in a rule
2007-08-11 07:43 Trigger Procedure Error: NEW used in query that is not in a rule Javier Fonseca V. <fonsecajavier@gmail.com>
2007-08-11 07:45 ` Trigger Procedure Error: NEW used in query that is not in a rule Javier Fonseca V. <fonsecajavier@gmail.com>
2007-08-11 08:16 ` Re: Trigger Procedure Error: NEW used in query that is not in a rule Pavel Stehule <pavel.stehule@gmail.com>
2007-08-11 15:50 ` Re: Trigger Procedure Error: NEW used in query that is not in a rule Tom Lane <tgl@sss.pgh.pa.us>
@ 2007-08-11 18:45 ` Javier Fonseca V. <fonsecajavier@gmail.com>
1 sibling, 0 replies; 11+ messages in thread
From: Javier Fonseca V. @ 2007-08-11 18:45 UTC (permalink / raw)
To: Tom Lane <tgl@sss.pgh.pa.us>; +Cc: Pavel Stehule <pavel.stehule@gmail.com>; pgsql-sql
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
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Trigger Procedure Error: NEW used in query that is not in a rule
2007-08-11 07:43 Trigger Procedure Error: NEW used in query that is not in a rule Javier Fonseca V. <fonsecajavier@gmail.com>
2007-08-11 07:45 ` Trigger Procedure Error: NEW used in query that is not in a rule Javier Fonseca V. <fonsecajavier@gmail.com>
@ 2007-08-11 19:07 ` hubert depesz lubaczewski <depesz@depesz.com>
1 sibling, 0 replies; 11+ messages in thread
From: hubert depesz lubaczewski @ 2007-08-11 19:07 UTC (permalink / raw)
To: Javier Fonseca V. <fonsecajavier@gmail.com>; +Cc: pgsql-sql
On Sat, Aug 11, 2007 at 02:45:09AM -0500, Javier Fonseca V. wrote:
> I think that it's working alright except for the next line:
doing this in plpgsql is very complicated (or even impossible assuming
that any table can have the same trigger). i would rather suggest using
pl/perl - writing something like this in pl/perl is very simple.
depesz
--
quicksil1er: "postgres is excellent, but like any DB it requires a
highly paid DBA. here's my CV!" :)
http://www.depesz.com/ - blog dla ciebie (i moje CV)
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Trigger Procedure Error: NEW used in query that is not in a rule
2007-08-11 07:43 Trigger Procedure Error: NEW used in query that is not in a rule Javier Fonseca V. <fonsecajavier@gmail.com>
@ 2007-08-12 11:33 ` Lew <lew@lewscanon.com>
3 siblings, 0 replies; 11+ messages in thread
From: Lew @ 2007-08-12 11:33 UTC (permalink / raw)
To: pgsql-general@postgresql.org
Javier Fonseca V. wrote:
> Hello.
>
> I'm doing a Trigger Procedure in pl/pgSQL. It makes some kind of auditing.
>
> I think that it's working alright except for the next line:
>
> EXECUTE 'INSERT INTO ' || quote_ident(somedynamictablename) || ' SELECT
> new.*';
>
> PostgreSQL keeps telling me: "ERROR: NEW used in query that is not in a
> rule". I think that this NEW problem is because of the scope of the
> EXECUTE statement (outside the scope of the trigger), so it doesn't
> recognize the NEW record.
>
> Maybe I could fix it concatenating column names and the 'new' values but
> I want to do my trigger as flexible as possible (I have several tables
> to audit).
>
> Somebody has any suggestion?
You got some suggestions in response to your multipost of this question in
pgsql.sql.
--
Lew
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Trigger Procedure Error: NEW used in query that is not in a rule
2007-08-11 07:43 Trigger Procedure Error: NEW used in query that is not in a rule Javier Fonseca V. <fonsecajavier@gmail.com>
@ 2007-08-15 18:28 ` Decibel! <decibel@decibel.org>
2007-08-16 00:05 ` Re: Trigger Procedure Error: NEW used in query that is not in a rule Javier Fonseca V. <fonsecajavier@gmail.com>
3 siblings, 1 reply; 11+ messages in thread
From: Decibel! @ 2007-08-15 18:28 UTC (permalink / raw)
To: Javier Fonseca V. <fonsecajavier@gmail.com>; +Cc: pgsql-general@postgresql.org
On Sat, Aug 11, 2007 at 02:43:30AM -0500, Javier Fonseca V. wrote:
> Hello.
>
> I'm doing a Trigger Procedure in pl/pgSQL. It makes some kind of auditing.
>
> I think that it's working alright except for the next line:
>
> EXECUTE 'INSERT INTO ' || quote_ident(somedynamictablename) || ' SELECT
> new.*';
>
> PostgreSQL keeps telling me: "ERROR: NEW used in query that is not in a
> rule". I think that this NEW problem is because of the scope of the EXECUTE
> statement (outside the scope of the trigger), so it doesn't recognize the
> NEW record.
Sort-of... the issue is that EXECUTE hands the string off to the
backend, which has no clue what "NEW" is; only the trigger procedure
understands NEW.
> Maybe I could fix it concatenating column names and the 'new' values but I
> want to do my trigger as flexible as possible (I have several tables to
> audit).
>
> Somebody has any suggestion?
You could theoretically make the trigger entirely dynamic by having it
pull the needed info out of the system catalogs... but I wouldn't want
to see the performance of that... If you care about performance *at
all*, I'd suggest writing some code that will generate the triggers for
a given table for you. I don't expect it'd be much harder than writing a
completely dynamic trigger.
--
Decibel!, aka Jim Nasby decibel@decibel.org
EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Trigger Procedure Error: NEW used in query that is not in a rule
2007-08-11 07:43 Trigger Procedure Error: NEW used in query that is not in a rule Javier Fonseca V. <fonsecajavier@gmail.com>
2007-08-15 18:28 ` Re: Trigger Procedure Error: NEW used in query that is not in a rule Decibel! <decibel@decibel.org>
@ 2007-08-16 00:05 ` Javier Fonseca V. <fonsecajavier@gmail.com>
0 siblings, 0 replies; 11+ messages in thread
From: Javier Fonseca V. @ 2007-08-16 00:05 UTC (permalink / raw)
To: Decibel! <decibel@decibel.org>; +Cc: pgsql-general@postgresql.org
The first time that I read it, the work-around sounded a little odd
(extreme!) to me... but actually it would really work!.
Thanks for the tip :)
*Javier*
On 8/15/07, Decibel! <decibel@decibel.org> wrote:
>
> On Sat, Aug 11, 2007 at 02:43:30AM -0500, Javier Fonseca V. wrote:
> > Hello.
> >
> > I'm doing a Trigger Procedure in pl/pgSQL. It makes some kind of
> auditing.
> >
> > I think that it's working alright except for the next line:
> >
> > EXECUTE 'INSERT INTO ' || quote_ident(somedynamictablename) || ' SELECT
> > new.*';
> >
> > PostgreSQL keeps telling me: "ERROR: NEW used in query that is not in a
> > rule". I think that this NEW problem is because of the scope of the
> EXECUTE
> > statement (outside the scope of the trigger), so it doesn't recognize
> the
> > NEW record.
>
> Sort-of... the issue is that EXECUTE hands the string off to the
> backend, which has no clue what "NEW" is; only the trigger procedure
> understands NEW.
>
> > Maybe I could fix it concatenating column names and the 'new' values but
> I
> > want to do my trigger as flexible as possible (I have several tables to
> > audit).
> >
> > Somebody has any suggestion?
>
> You could theoretically make the trigger entirely dynamic by having it
> pull the needed info out of the system catalogs... but I wouldn't want
> to see the performance of that... If you care about performance *at
> all*, I'd suggest writing some code that will generate the triggers for
> a given table for you. I don't expect it'd be much harder than writing a
> completely dynamic trigger.
> --
> Decibel!, aka Jim Nasby decibel@decibel.org
> EnterpriseDB http://enterprisedb.com 512.569.9461 (cell)
>
>
^ permalink raw reply [nested|flat] 11+ messages in thread
* Re: Trigger Procedure Error: NEW used in query that is not in a rule
2007-08-11 07:43 Trigger Procedure Error: NEW used in query that is not in a rule Javier Fonseca V. <fonsecajavier@gmail.com>
@ 2011-09-21 15:32 ` jablonov <jablonov@hotmail.com>
3 siblings, 0 replies; 11+ messages in thread
From: jablonov @ 2011-09-21 15:32 UTC (permalink / raw)
To: pgsql-general@postgresql.org
Here is other option using functionality of ROW and EXECUTE USING.
IF EXISTS(SELECT relname FROM pg_class WHERE relname = tablename)
THEN sql := 'INSERT INTO ' || tablename || ' VALUES (($1).*)';
ELSE sql := 'INSERT INTO ' || tablexception || ' VALUES (($1).*)';
END IF;
tablerow := ROW(NEW.*)::public.measurement;
EXECUTE sql USING tablerow;
RETURN new;
--
View this message in context: http://postgresql.1045698.n5.nabble.com/Trigger-Procedure-Error-NEW-used-in-query-that-is-not-in-a-r...
Sent from the PostgreSQL - general mailing list archive at Nabble.com.
^ permalink raw reply [nested|flat] 11+ messages in thread
end of thread, other threads:[~2011-09-21 15:32 UTC | newest]
Thread overview: 11+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2007-08-11 07:43 Trigger Procedure Error: NEW used in query that is not in a rule Javier Fonseca V. <fonsecajavier@gmail.com>
2007-08-11 07:45 ` Javier Fonseca V. <fonsecajavier@gmail.com>
2007-08-11 08:16 ` Pavel Stehule <pavel.stehule@gmail.com>
2007-08-11 15:50 ` Tom Lane <tgl@sss.pgh.pa.us>
2007-08-11 16:10 ` Pavel Stehule <pavel.stehule@gmail.com>
2007-08-11 18:45 ` Javier Fonseca V. <fonsecajavier@gmail.com>
2007-08-11 19:07 ` hubert depesz lubaczewski <depesz@depesz.com>
2007-08-12 11:33 ` Lew <lew@lewscanon.com>
2007-08-15 18:28 ` Decibel! <decibel@decibel.org>
2007-08-16 00:05 ` Javier Fonseca V. <fonsecajavier@gmail.com>
2011-09-21 15:32 ` jablonov <jablonov@hotmail.com>
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