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: (view raw, whole thread or download thread mbox)
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 , 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:

    current_time timestamp := now();
    suffix text := date_part('month', now())||'_'||date_part('day', now()) ;
    RAISE NOTICE '%', suffix;
    execute 'insert into t1_'||suffix||' values( NEW.* )';
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.


> Best regards,
> depesz
> --
> Linkedin:  /  blog:
> jid/gtalk: depesz(at)depesz(dot)com / aim:depeszhdl / skype:depesz_hdl / gg:6749007

Web database:
Free Software Developers Meeting:

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-2017 The PostgreSQL Global Development Group