Re: triggers and execute...

On Mon, Apr 27, 2009 at 2:32 PM, Scott Marlowe <scott(dot)marlowe(at)gmail(dot)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.