postgresql 中分区表的创建及使用样例

create table maintb(id int,name varchar(10));


create table childtb_1 (CHECK ( id BETWEEN 1 AND 200)) inherits(maintb);
create table childtb_2 (CHECK ( id BETWEEN 201 AND 400)) inherits(maintb);
create table childtb_3 (CHECK ( id BETWEEN 401 AND 600)) inherits(maintb);
create table childtb_4 (CHECK ( id BETWEEN 601 AND 800)) inherits(maintb);
create table childtb_5 (CHECK ( id BETWEEN 801 AND 1000)) inherits(maintb);


CREATE INDEX childtb_idx_1 ON childtb_1 (id);
CREATE INDEX childtb_idx_2 ON childtb_2 (id);
CREATE INDEX childtb_idx_3 ON childtb_3 (id);
CREATE INDEX childtb_idx_4 ON childtb_4 (id);
CREATE INDEX childtb_idx_5 ON childtb_5 (id);


--创建存储过程
CREATE OR REPLACE FUNCTION maintb_insert_trigger() 
 RETURNS TRIGGER AS $$ 
 BEGIN
     IF( NEW.id BETWEEN 1 AND 200 ) THEN 
         INSERT INTO childtb_1 VALUES (NEW.*); 
     ELSIF( NEW.id BETWEEN 201 AND 400 ) THEN 
         INSERT INTO childtb_2 VALUES (NEW.*); 
     ELSIF( NEW.id BETWEEN 401 AND 600 ) THEN 
         INSERT INTO childtb_3 VALUES (NEW.*); 
     ELSIF( NEW.id BETWEEN 601 AND 800 ) THEN 
         INSERT INTO childtb_4 VALUES (NEW.*); 
     ELSIF( NEW.id BETWEEN 801 AND 1000 ) THEN 
         INSERT INTO childtb_5 VALUES (NEW.*); 
     ELSE 
         RAISE EXCEPTION 'id out of range!'; 
     END IF; 
     RETURN NULL; 
 END; 
 $$ 
 LANGUAGE plpgsql;


--创建触发器
 CREATE TRIGGER insert_maintb_trigger 
     BEFORE INSERT ON maintb 
     FOR EACH ROW EXECUTE PROCEDURE maintb_insert_trigger();


CREATE TRIGGER insert_maintb_trigger 
     BEFORE INSERT ON maintb 
     FOR EACH ROW EXECUTE PROCEDURE maintb_insert_trigger();


insert into maintb values(1,'abcde12345');
insert into maintb values(300,'abcde300');
explain (format json) select * from maintb where id=2;