Skip to content

Instantly share code, notes, and snippets.

Show Gist options
  • Save RhodiumToad/b82aac9aa4e3fbdda967d89b1e418aa4 to your computer and use it in GitHub Desktop.
Save RhodiumToad/b82aac9aa4e3fbdda967d89b1e418aa4 to your computer and use it in GitHub Desktop.
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
Copy link
Author

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