分区表

分区表


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/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/91975/viewspace-1784191/