FAQ
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

Search Discussions

  • Pavel Stehule at Aug 11, 2007 at 8:17 am
    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
  • Tom Lane at Aug 11, 2007 at 3:50 pm

    "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
  • Pavel Stehule at Aug 11, 2007 at 4:10 pm

    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
  • Javier Fonseca V. at Aug 11, 2007 at 6:45 pm
    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 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
  • Hubert depesz lubaczewski at Aug 11, 2007 at 7:07 pm

    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)
  • Raphael Bauduin at Sep 17, 2008 at 3:08 pm
    HI,

    On Sat, Aug 11, 2007 at 9:07 PM, hubert depesz lubaczewski
    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
  • Raphael Bauduin at Sep 17, 2008 at 5:27 pm

    On Wed, Sep 17, 2008 at 5:45 PM, hubert depesz lubaczewski 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

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouppgsql-sql @
categoriespostgresql
postedAug 11, '07 at 7:45a
activeSep 17, '08 at 5:27p
posts8
users5
websitepostgresql.org
irc#postgresql

People

Translate

site design / logo © 2021 Grokbase