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.