[GENERAL] partition insert question

[Marc Evans]

Hello -

I find myself trying to find a way to have the table name used with the 
insert command be generated on the fly in a rule. For example, consider 
the following:


create table foobars (
  id bigserial,
  created_at timestamp not null,
  name
);

create table foobars_200612 (
  check (created_at = timestamp '2006-12-01 00:00:00' and created_at  
timestamp '2007-01-01 00:00:00')
) inherits (foobars);

create table foobars_200701 (
  check (created_at = timestamp '2007-01-01 00:00:00' and created_at  
timestamp '2007-02-01 00:00:00')
) inherits (foobars);

create rule foobars_insert as
on insert to foobars do instead
  insert into (select 'foobars_' || extract(year from NEW.created_at) || 
extract(month from NEW.created_at))
  (created_at,name) values (now(),'hello');

I realize that I could craft a list of many ON INSERT TO foobars WHERE xxx 
constructs, but am trying to be a bit more maintainable than having each 
of the where clauses hard-defined. Any suggestions?


Thanks in advance - Marc

---(end of broadcast)---
TIP 2: Don't 'kill -9' the postmaster

Re: [GENERAL] partition insert question

[Marc Evans]

On Thu, 7 Dec 2006, Marc Evans wrote:


Hello -

I find myself trying to find a way to have the table name used with the 
insert command be generated on the fly in a rule. For example, consider the 
following:


create table foobars (
 id bigserial,
 created_at timestamp not null,
 name
);

create table foobars_200612 (
 check (created_at = timestamp '2006-12-01 00:00:00' and created_at  
timestamp '2007-01-01 00:00:00')

) inherits (foobars);

create table foobars_200701 (
 check (created_at = timestamp '2007-01-01 00:00:00' and created_at  
timestamp '2007-02-01 00:00:00')

) inherits (foobars);

create rule foobars_insert as
on insert to foobars do instead
 insert into (select 'foobars_' || extract(year from NEW.created_at) || 
extract(month from NEW.created_at))

 (created_at,name) values (now(),'hello');

I realize that I could craft a list of many ON INSERT TO foobars WHERE xxx 
constructs, but am trying to be a bit more maintainable than having each of 
the where clauses hard-defined. Any suggestions?


Thanks in advance - Marc


To answer my own question, I have found this to work. If anyone has 
suggestions for improvements, please let me know.


create or replace function foo_insert(TIMESTAMP,TEXT) returns void as $$
  begin
execute 'insert into foobars_' ||
  (select extract(year from $1) || extract(month from $1)) ||
  ' (created_at,name) values (\'' || $1 || '\',\'' || $2 || '\')';
  end;
$$ language plpgsql;
create rule foobars_insert as on insert to foobars
  do instead select foo_insert(NEW.created_at,NEW.name);

- Marc

---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?

  http://www.postgresql.org/docs/faq