Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
create table mydata_real (id serial, date date, value text);
create table mydata_real_y2015 (check (date >= '2015-01-01' and date < '2016-01-01')) inherits (mydata_real);
create table mydata_real_y2016 (check (date >= '2016-01-01' and date < '2017-01-01')) inherits (mydata_real);
create function mydata_nope() returns trigger language plpgsql
as $f$ begin raise exception 'insert on wrong table'; return NULL; end; $f$;
create trigger mydata_nope before insert on mydata_real execute procedure mydata_nope();
create view mydata as select * from mydata_real;
-- need to copy any defaults from mydata_real to the view
alter view mydata alter column id set default nextval('mydata_real_id_seq');
create function mydata_partition() returns trigger language plpgsql
as $f$
begin
case extract(year from NEW.date)
when 2015
then insert into mydata_real_y2015 select NEW.*;
when 2016
then insert into mydata_real_y2016 select NEW.*;
else
raise exception 'date % out of range', NEW.date;
end case;
return NEW;
end;
$f$;
create trigger mydata_partition instead of insert on mydata
for each row execute procedure mydata_partition();
insert into mydata(date,value) values ('2015-01-10','foo'),('2015-12-12','bar'),('2016-02-02','baz') returning id;
@RhodiumToad

This comment has been minimized.

Copy link
Owner Author

@RhodiumToad RhodiumToad commented May 6, 2016

Note that the "mydata" view also works for ordinary selects, updates and deletes too—there's no need to access "mydata_real" directly.

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment