oracle触发器分表,postgresql使用触发器进行分表操作

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字段插入到分表,主表也会同时插入.据说会对性能有提升,还未测试;