Thread: partition insert question

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

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