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

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

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>:

"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

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

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 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)

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

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)

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*

HI,

On Sat, Aug 11, 2007 at 9:07 PM, hubert depesz lubaczewski
<depesz@depesz.com> wrote:

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.

I am in the same situation where I would like to execute a query similar to
EXECUTE 'INSERT INTO ' || quote_ident(SOMEDYNAMICNTABLENAME) || '
SELECT new.*';

I've looked at the plperl documentation, and experimented a bit, but
I'm not even sure how to start this in pl/perl. I hoped to extract
columns from $_TD->{new} but it doesn't seem to work.
Would you have a little example on how you would do it?

Thanks in advance!

Raph

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)

---------------------------(end of broadcast)---------------------------
TIP 3: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

--
Web database: http://www.myowndb.com
Free Software Developers Meeting: http://www.fosdem.org

On Wed, Sep 17, 2008 at 5:45 PM, hubert depesz lubaczewski
<depesz@depesz.com> wrote:

On Wed, Sep 17, 2008 at 05:08:39PM +0200, Raphael Bauduin wrote:

Would you have a little example on how you would do it?

show us what you have done - it will be easier to find/fix/explain than
to write code for you.

Well, I experimented a lot but didn't come to any useful result.
Actually I'm working on table partitioning as described at
http://www.postgresql.org/docs/8.3/static/ddl-partitioning.html , and
I wanted to write a trigger that would insert the data in the correct
table, and so I got the same problem with plpsql's NEW.* not usable in
a dynamically created query to be run by EXECUTE:

CREATE OR REPLACE FUNCTION part_test() RETURNS TRIGGER AS $$
DECLARE
current_time timestamp := now();
suffix text := date_part('month', now())||'_'||date_part('day', now()) ;
BEGIN
RAISE NOTICE '%', suffix;
execute 'insert into t1_'||suffix||' values( NEW.* )';
RETURN NULL;
END;
$$
LANGUAGE plpgsql;

I searched the archives here and after reading your previous mail in
this thread, I started to look at plperl, with which I have no
experience at all.
As $_TD{new}{column} gives the value of field column, I thought to
extract all columns from keys($_TD{new}), but it doesn't seem to see
$_TD{new} as a hash:

Type of arg 1 to keys must be hash (not hash element)

And that's where I'm at now.

Raph

Best regards,

depesz

--
Linkedin: http://www.linkedin.com/in/depesz / blog: http://www.depesz.com/
jid/gtalk: depesz@depesz.com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

--
Web database: http://www.myowndb.com
Free Software Developers Meeting: http://www.fosdem.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-rule-tp1886197p4826781.html
Sent from the PostgreSQL - general mailing list archive at Nabble.com.