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

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

--
Sent via pgsql-sql mailing list (pgsql-sql@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-sql