Skip site navigation (1) Skip section navigation (2)

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

From: "Raphael Bauduin" <rblists(at)gmail(dot)com>
To: depesz(at)depesz(dot)com
Cc: pgsql-sql(at)postgresql(dot)org
Subject: Re: Trigger Procedure Error: NEW used in query that is not in a rule
Date: 2008-09-17 17:09:47
Message-ID: f5227160809171009o4fb23c1bl3e31f4b3645a4eef@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-generalpgsql-sql
On Wed, Sep 17, 2008 at 5:45 PM, hubert depesz lubaczewski
<depesz(at)depesz(dot)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(at)depesz(dot)com / aim:depeszhdl / skype:depesz_hdl / gg:6749007
>



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

In response to

pgsql-sql by date

Next:From: Emi LuDate: 2008-09-17 18:54:34
Subject: prepared query plan did not update
Previous:From: Richard BroersmaDate: 2008-09-17 16:20:44
Subject: Re: surrogate vs natural primary keys

pgsql-general by date

Next:From: Mike DiehlDate: 2008-09-17 17:29:15
Subject: Design question
Previous:From: Greg SmithDate: 2008-09-17 16:36:06
Subject: Re: FAQ: how to read EXPLAYN ANALYZE and act upon

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group