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

<span class="gmail_quote"></span><p>Hello.<p>I'm doing a Trigger Procedure in pl/pgSQL.  It makes some kind of
auditing.<p>Ithink that it's working alright except for the next line:<p>EXECUTE 'INSERT INTO ' ||
quote_ident(somedynamictablename)|| ' SELECT new.*';<p>PostgreSQL keeps telling me: "ERROR: NEW used in query that is
notin a rule".  I think that this NEW problem is because of the scope of the EXECUTE statement (outside the scope of
thetrigger), so it doesn't recognize the NEW record. <p>Maybe I could fix it concatenating column names and the 'new'
valuesbut I want to do my trigger as flexible as possible (I have several tables to audit).<p>Somebody has any
suggestion?<p>Thanksa lot,<span class="sg"><p>Javier</span> 

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

"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

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)

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