Re: triggers and execute... - Mailing list pgsql-general

From Scott Marlowe
Subject Re: triggers and execute...
Date
Msg-id dcc563d10904271400p47515c0dj1b4967ee4e0348bf@mail.gmail.com
Whole thread Raw
In response to triggers and execute...  (Scott Marlowe <scott.marlowe@gmail.com>)
List pgsql-general
On Mon, Apr 27, 2009 at 2:32 PM, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> OK, I'm hitting a wall here.  I've written this trigger for partitioning:
>
> create or replace function page_access_insert_trigger ()
> returns trigger as $$
> DECLARE
>        part text;
>        q text;
> BEGIN
>        part = to_char(new."timestamp",'YYYYMMDD');
>        q = 'insert into page_access_'||part||' values (new.*)';
>        execute q;
>        return null;
> END;
> $$ language plpgsql;
> drop trigger page_access_insert_trigger on page_access cascade;
> create trigger page_access_insert_trigger before insert or update on page_access
>        for each row execute procedure page_access_insert_trigger();
>
>
> When I create it and try to use it I get this error:
> ERROR:  NEW used in query that is not in a rule
> CONTEXT:  SQL statement "insert into page_access_20090427 values (new.*)"
> PL/pgSQL function "page_access_insert_trigger" line 7 at EXECUTE statement

OK, answering my own post here, but not really satisfied with the
answer.  If I create the trigger this way:

create or replace function page_access_insert_trigger ()
returns trigger as $$
DECLARE
        part text;
        q text;
BEGIN
        part = to_char(new."timestamp",'YYYYMMDD');
        q = 'insert into page_access_'||part||' values (
                '||new.paid||',
                '''||new.timestamp||''',
                '||new.total_time||',
                '''||new.http_host||''',
                '''||new.php_self||''',
                '''||new.query_string||''',
                '''||new.remote_addr||''',
                '''||new.logged_in||''',
                '||new.uid||',
                '''||new.http_user_agent||''',
                '''||new.server_addr||''',
                '''||new.notes||'''
        )';
        execute q;
--      insert into page_access_20090427 values (new.*);
        return null;
END;
$$ language plpgsql;

It now works.  I've tried a variety of constructs of new and || and '
and * and nothing easy like new.* seems to work.

Any suggestions greatly appreciated.  Til then, the explicitly named
fields seems to work well enough.

pgsql-general by date:

Previous
From: Scott Marlowe
Date:
Subject: triggers and execute...
Next
From: Grzegorz Jaśkiewicz
Date:
Subject: Re: Query organization question