partition insert question

From: Marc Evans <Marc(at)SoftwareHackery(dot)Com>
To: pgsql-general(at)postgresql(dot)org
Subject: partition insert question
Date: 2006-12-07 13:58:34
Message-ID: 20061207084937.N58969@me.softwarehackery.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

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

Responses

Browse pgsql-general by date

  From Date Subject
Next Message Bill Moran 2006-12-07 14:01:22 Re: VACUUM and transactions in different databases
Previous Message Oleg Bartunov 2006-12-07 13:52:20 Re: Tsearch2 / PG 8.2 Which stemmer files?