ITPub博客

首页 > 数据库 > NoSQL > 分区表

分区表

原创 NoSQL 作者:wzgchen 时间:2015-08-27 16:02:57 0 删除 编辑
分区表


create table maintb(id int,name char(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();




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

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/91975/viewspace-1784191/,如需转载,请注明出处,否则将追究法律责任。

上一篇: postgresql hot standby
请登录后发表评论 登录
全部评论

注册时间:2015-05-01

  • 博文量
    383
  • 访问量
    181119