postgresql分区

--创建主表
CREATE TABLE "public"."test" (
  "id" varchar(255) COLLATE "pg_catalog"."default" NOT NULL,
  "createtime" timestamptz(6) NOT NULL,
  CONSTRAINT "test_pkey" PRIMARY KEY ("id")
);


--创建子表
create table test_1904 check ( createtime >= date'2019-04-01' and createtime < date'2019-05-01' ) inherits (test);
create table test_1905 check ( createtime >= date'2019-05-01' and createtime < date'2019-06-01' ) inherits (test);


--插入函数
create or replace function test_insert_function()
returns trigger as $$
begin
	if( new.createtime >= date'2019-04-01' and new.createtime < date'2019-05-01' ) then
		insert into test_1904 values (new.*);
	elsif ( new.createtime >= date'2019-05-01' and new.createtime <date'2019-06-01') then
		insert into test_1905 values( new.* );
	end if;
	return null;
end;
$$
language plpgsql;
 
 
--插入触发器
create trigger test_insert_trigger before insert on test for each row execute procedure test_insert_function();


--子表更新函数(每一个字表都要有这个函数,寿命应和字表一致)
create or REPLACE FUNCTION testupdate1905() returns trigger as $$
begin
if ( new.createtime < DATE'2019-05-01' or new.createtime >= DATE'2019-06-01' ) then
	delete from test where id = new.id;
	INSERT INTO test(id, createtime) VALUES (new.*);
end if;
	return null;
end $$ language plpgsql;


create or REPLACE FUNCTION testupdate1904() returns trigger as $$
begin
if ( new.createtime < DATE'2019-04-01' or new.createtime >= DATE'2019-05-01' ) then
	delete from test where id = new.id;
	INSERT INTO test(id, createtime) VALUES (new.*);
end if;
	return null;
end $$ language plpgsql;



--子表更新触发器(同子表更新函数)
create trigger updatetest1905 before update of  createtime on public.test_1905 for each row execute procedure testupdate1905();

create trigger updatetest1904 before update of  createtime on public.test_1904 for each row execute procedure testupdate1904();



--测试数据
INSERT INTO "public"."test"("id", "createtime") VALUES ('3', '2019-05-23 00:00:00+08');


update test set createtime = '2019-04-12 00:00:00' where id = '3'