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
If I rewrite it to just write to that table:
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;
insert into page_access_20090427 values (new.*);
return null;
END;
$$ language plpgsql;
It works. So, how am I supposed to run it with dynamic table names?
