| 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: | Whole Thread | Raw Message | 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? |