第一步,建表 父表:tablename
create table z_tablename_0 (check (fenqu=0)) INHERITS(tablename);
create table z_tablename_1 (check (fenqu=1)) INHERITS(tablename);
create table z_tablename_2 (check (fenqu=2)) INHERITS(tablename);
create table z_tablename_3 (check (fenqu=3)) INHERITS(tablename);
create table z_tablename_4 (check (fenqu=4)) INHERITS(tablename);
create table z_tablename_5 (check (fenqu=5)) INHERITS(tablename);
create table z_tablename_6 (check (fenqu=6)) INHERITS(tablename);
create table z_tablename_7 (check (fenqu=7)) INHERITS(tablename);
create table z_tablename_8 (check (fenqu=8)) INHERITS(tablename);
create table z_tablename_9 (check (fenqu=9)) INHERITS(tablename);
第二步,创建触发器函数
CREATE OR REPLACE FUNCTION tablename_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF (NEW.fenqu=0) THEN
INSERT INTO z_tablename_0 VALUES (NEW.*);
ELSIF (NEW.fenqu=1) THEN
INSERT INTO z_tablename_1 VALUES (NEW.*);
ELSIF (NEW.fenqu=2) THEN
INSERT INTO z_tablename_2 VALUES (NEW.*);
ELSIF (NEW.fenqu=3) THEN
INSERT INTO z_tablename_3 VALUES (NEW.*);
ELSIF (NEW.fenqu=4) THEN
INSERT INTO z_tablename_4 VALUES (NEW.*);
ELSIF (NEW.fenqu=5) THEN
INSERT INTO z_tablename_5 VALUES (NEW.*);
ELSIF (NEW.fenqu=6) THEN
INSERT INTO z_tablename_6 VALUES (NEW.*);
ELSIF (NEW.fenqu=7) THEN
INSERT INTO z_tablename_7 VALUES (NEW.*);
ELSIF (NEW.fenqu=8) THEN
INSERT INTO z_tablename_8 VALUES (NEW.*);
ELSIF (NEW.fenqu=9) THEN
INSERT INTO z_tablename_9 VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'Date out of range. Fix the tablename_insert_trigger() function!';
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
第三步, 创建触发器
CREATE TRIGGER insert_tablename_trigger
BEFORE INSERT ON tablename
FOR EACH ROW EXECUTE PROCEDURE tablename_insert_trigger();
好了,分区建立完成,但是这个时候插入数据的时候就会报错:ERROR [org.Hibernate.event.def.AbstractFlushingEventListener] -org.Hibernate.StaleStateException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1
解决方法,在tablename的pojo上加 @SQLInsert(sql = "INSERT INTO comm_commentall(ziduan1,ziduan2) VALUES (?,?,?,?,?,?,?,?,?,?)", check=ResultCheckStyle.NONE)注意字段的循序,需要按hibernate默认的循序排列。 即show_sql为true的时候hibernate的排列循序。