triggers and execute...

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?

Responses

Browse pgsql-general by date

  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?