1.首先准备测试的父表:
2.创建子表:
create table info_2017(
check ( year = '2017' )
) INHERITS (info);
create table info_2018(
check ( year = '2018' )
) INHERITS (info);
3.创建各表索引
create index info_2017_index on info_2017(year);
create index info_2018_index on info_2018(year);
4.创建触发器函数
CREATE OR REPLACE FUNCTION info_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF ( NEW.year = '2017') THEN
INSERT INTO info_2017 VALUES (NEW.*);
ELSIF ( NEW.year = '2018' ) THEN
INSERT INTO info_2018 VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'Date out of range. Fix the tbl_partition_insert_trigger() function!';
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER my_info_trigger
BEFORE INSERT ON info
FOR EACH ROW EXECUTE PROCEDURE info_trigger();
完成后的结果会根据插入语句的year字段插入到分表,主表也会同时插入.据说会对性能有提升,还未测试;