Hi -
I am trying to make use of table partitions. In doing so I would like to
use a rule to call a functioning which inserts the data into the proper
partition. To do so, I believe that I need to find a way to opaquely pass
NEW from the rule to a function which then passes it to INSERT. (Well, I
could spell out all of the columns in the table as arguments to the
function, but that is not as maintainable, e.g. every time the table
columns change, so to the function and rule change.)
I am not finding any way to do this in the proceedural languages. That
said, I would happily believe that I am just missing something, and am
hoping that someone on this list has already figured out an answer.
For consideration, here is an example:
create table foobars (
id bigserial,
created_at timestamp not null,
name text
);
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);
-- Warning, pseudo code follows (e.g. NEW):
create or replace function foo_insert(NEW) returns void as $$
begin
execute 'insert into foobars_' ||
(select extract(year from $1) || extract(month from $1)) ||
' values (' || NEW || ')';
end;
$$ language plpgsql;
create rule foobars_insert as on insert to foobars
do instead select foo_insert(NEW);
The key to my success for the above is to find a way for NEW to be used
something like the pseudo code shown. Suggestions?
- Marc
