From: | Scott Marlowe <scott(dot)marlowe(at)gmail(dot)com> |
---|---|
To: | "pgsql-general(at)postgresql(dot)org" <pgsql-general(at)postgresql(dot)org> |
Subject: | triggers and execute... |
Date: | 2009-04-27 20:32:22 |
Message-ID: | dcc563d10904271332q5d1f2274w26eef4d70a5626fd@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
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?
From | Date | Subject | |
---|---|---|---|
Next Message | Scott Marlowe | 2009-04-27 21:00:37 | Re: triggers and execute... |
Previous Message | Alvaro Herrera | 2009-04-27 20:19:28 | Re: PostgreSQL Object-Oriented Database? |