Marc Evans <Marc@xxxxxxxxxxxxxxxxxxx> writes:
> 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.
Basically, you're guaranteeing yourself large amounts of pain by
insisting on using a rule for this. I'd suggest using a trigger
instead. A "BEFORE INSERT" trigger on the parent table can redirect
the data to the appropriate place and then return NULL to prevent the
insertion into the parent.
Given your example, I'd do something like
create or replace function foobars_insert() returns trigger
language plpgsql as $$
begin
if new.created_at >= timestamp '2006-12-01 00:00:00' and
new.created_at < timestamp '2007-01-01 00:00:00' then
insert into foobars_200612 values(new.*);
elsif new.created_at >= timestamp '2007-01-01 00:00:00' and
new.created_at < timestamp '2007-02-01 00:00:00' then
insert into foobars_200701 values(new.*);
elsif ...
else
raise exception 'No partition for timestamp %', new.created_at;
end if;
return null;
end$$;
create trigger foobars_insert before insert on foobars
for each row execute procedure foobars_insert();
Obviously you have to adjust the function definition every time
you add or remove a partition, but you'll have a script for that
anyway, no?
BTW, I think using "new.*" this way only works as of 8.2.
regards, tom lane
