[GENERAL] triggers and execute...

[Scott Marlowe]

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?

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