postgresql分区表-触发器

最新推荐文章于 2022-04-22 14:41:57 发布

魂醉

最新推荐文章于 2022-04-22 14:41:57 发布

阅读量1.7k

点赞数

CC 4.0 BY-SA版权

分类专栏： # PostgreSQL

本文链接：https://blog.csdn.net/dazuiba008/article/details/22786053

PostgreSQL 专栏收录该内容

206 篇文章

订阅专栏

近有个新需求，写只能写主表，查的话是查子表，分区表感觉还是oracle要先进一些，管理也更加方便。下面只是记录一下，以后扩分区可以直接copy用

1.建主表

create tbl_test(name int,createtime timestamp without time zone);

2.建子表

create table tbl_test_201403(check (createtime>='2014-3-1' and createtime<'2014-4-1')) inherits(tbl_test);
create table tbl_test_201404(check (createtime>='2014-4-1' and createtime<'2014-5-1')) inherits(tbl_test);
create table tbl_test_201405(check (createtime>='2014-5-1' and createtime<'2014-6-1')) inherits(tbl_test);
create table tbl_test_201406(check (createtime>='2014-6-1' and createtime<'2014-7-1')) inherits(tbl_test);
create table tbl_test_201407(check (createtime>='2014-7-1' and createtime<'2014-8-1')) inherits(tbl_test);
create table tbl_test_201408(check (createtime>='2014-8-1' and createtime<'2014-9-1')) inherits(tbl_test);
create table tbl_test_201409(check (createtime>='2014-9-1' and createtime<'2014-10-1')) inherits(tbl_test);
create table tbl_test_201410(check (createtime>='2014-10-1' and createtime<'2014-11-1')) inherits(tbl_test);
create table tbl_test_201411(check (createtime>='2014-11-1' and createtime<'2014-12-1')) inherits(tbl_test);
create table tbl_test_201412(check (createtime>='2014-12-1' and createtime<'2015-1-1')) inherits(tbl_test);
create table tbl_test_201501(check (createtime>='2015-1-1' and createtime<'2015-2-1')) inherits(tbl_test);
create table tbl_test_201502(check (createtime>='2015-2-1' and createtime<'2015-3-1')) inherits(tbl_test);
create table tbl_test_201503(check (createtime>='2015-3-1' and createtime<'2015-4-1')) inherits(tbl_test);
create table tbl_test_201504(check (createtime>='2015-4-1' and createtime<'2015-5-1')) inherits(tbl_test);
create table tbl_test_201505(check (createtime>='2015-5-1' and createtime<'2015-6-1')) inherits(tbl_test);
create table tbl_test_201506(check (createtime>='2015-6-1' and createtime<'2015-7-1')) inherits(tbl_test);
create table tbl_test_201507(check (createtime>='2015-7-1' and createtime<'2015-8-1')) inherits(tbl_test);
create table tbl_test_201508(check (createtime>='2015-8-1' and createtime<'2015-9-1')) inherits(tbl_test);
create table tbl_test_201509(check (createtime>='2015-9-1' and createtime<'2015-10-1')) inherits(tbl_test);
create table tbl_test_201510(check (createtime>='2015-10-1' and createtime<'2015-11-1')) inherits(tbl_test);
create table tbl_test_201511(check (createtime>='2015-11-1' and createtime<'2015-12-1')) inherits(tbl_test);
create table tbl_test_201512(check (createtime>='2015-12-1' and createtime<'2016-1-1')) inherits(tbl_test);
create table tbl_test_201601(check (createtime>='2016-1-1' and createtime<'2016-2-1')) inherits(tbl_test);
create table tbl_test_201602(check (createtime>='2016-2-1' and createtime<'2016-3-1')) inherits(tbl_test);
create table tbl_test_201603(check (createtime>='2016-3-1' and createtime<'2016-4-1')) inherits(tbl_test);
create table tbl_test_201604(check (createtime>='2016-4-1' and createtime<'2016-5-1')) inherits(tbl_test);
create table tbl_test_201605(check (createtime>='2016-5-1' and createtime<'2016-6-1')) inherits(tbl_test);
create table tbl_test_201606(check (createtime>='2016-6-1' and createtime<'2016-7-1')) inherits(tbl_test);
create table tbl_test_201607(check (createtime>='2016-7-1' and createtime<'2016-8-1')) inherits(tbl_test);
create table tbl_test_201608(check (createtime>='2016-8-1' and createtime<'2016-9-1')) inherits(tbl_test);

3.建相关使用索引

create index tbl_test_createtime_201403 on tbl_test_201403(createtime);
create index tbl_test_createtime_201404 on tbl_test_201404(createtime);
create index tbl_test_createtime_201405 on tbl_test_201405(createtime);
create index tbl_test_createtime_201406 on tbl_test_201406(createtime);
create index tbl_test_createtime_201407 on tbl_test_201407(createtime);
create index tbl_test_createtime_201408 on tbl_test_201408(createtime);
create index tbl_test_createtime_201409 on tbl_test_201409(createtime);
create index tbl_test_createtime_201410 on tbl_test_201410(createtime);
create index tbl_test_createtime_201411 on tbl_test_201411(createtime);
create index tbl_test_createtime_201412 on tbl_test_201412(createtime);
create index tbl_test_createtime_201501 on tbl_test_201501(createtime);
create index tbl_test_createtime_201502 on tbl_test_201502(createtime);
create index tbl_test_createtime_201503 on tbl_test_201503(createtime);
create index tbl_test_createtime_201504 on tbl_test_201504(createtime);
create index tbl_test_createtime_201505 on tbl_test_201505(createtime);
create index tbl_test_createtime_201506 on tbl_test_201506(createtime);
create index tbl_test_createtime_201507 on tbl_test_201507(createtime);
create index tbl_test_createtime_201508 on tbl_test_201508(createtime);
create index tbl_test_createtime_201509 on tbl_test_201509(createtime);
create index tbl_test_createtime_201510 on tbl_test_201510(createtime);
create index tbl_test_createtime_201511 on tbl_test_201511(createtime);
create index tbl_test_createtime_201512 on tbl_test_201512(createtime);
create index tbl_test_createtime_201601 on tbl_test_201601(createtime);
create index tbl_test_createtime_201602 on tbl_test_201602(createtime);
create index tbl_test_createtime_201603 on tbl_test_201603(createtime);
create index tbl_test_createtime_201604 on tbl_test_201604(createtime);
create index tbl_test_createtime_201605 on tbl_test_201605(createtime);
create index tbl_test_createtime_201606 on tbl_test_201606(createtime);
create index tbl_test_createtime_201607 on tbl_test_201607(createtime);
create index tbl_test_createtime_201608 on tbl_test_201608(createtime);

4.建立相关触发器函数，我这里只有insert，所以delete触发器就不写了

CREATE OR REPLACE FUNCTION tbl_test_insert_trigger()                      
RETURNS TRIGGER AS $$  
BEGIN  
    IF (NEW.createtime>='2014-3-1' and NEW.createtime<'2014-4-1') THEN  
        INSERT INTO tbl_test_201403 VALUES (NEW.*);  
    ELSIF (NEW.createtime>='2014-4-1' and NEW.createtime<'2014-5-1') THEN  
        INSERT INTO tbl_test_201404 VALUES (NEW.*);  
    ELSIF (NEW.createtime>='2014-5-1' and NEW.createtime<'2014-6-1') THEN  
        INSERT INTO tbl_test_201405 VALUES (NEW.*);    
    ELSIF (NEW.createtime>='2014-6-1' and NEW.createtime<'2014-7-1') THEN  
        INSERT INTO tbl_test_201406 VALUES (NEW.*);    
    ELSIF (NEW.createtime>='2014-7-1' and NEW.createtime<'2014-8-1') THEN  
        INSERT INTO tbl_test_201407 VALUES (NEW.*);    
    ELSIF (NEW.createtime>='2014-8-1' and NEW.createtime<'2014-9-1') THEN  
        INSERT INTO tbl_test_201408 VALUES (NEW.*);  
    ELSIF (NEW.createtime>='2014-9-1' and NEW.createtime<'2014-10-1') THEN  
        INSERT INTO tbl_test_201409 VALUES (NEW.*); 
    ELSIF (NEW.createtime>='2014-10-1' and NEW.createtime<'2014-11-1') THEN  
        INSERT INTO tbl_test_201410 VALUES (NEW.*);
    ELSIF (NEW.createtime>='2014-11-1' and NEW.createtime<'2014-12-1') THEN  
        INSERT INTO tbl_test_201411 VALUES (NEW.*);
    ELSIF (NEW.createtime>='2014-12-1' and NEW.createtime<'2015-1-1') THEN  
        INSERT INTO tbl_test_201412 VALUES (NEW.*);
    ELSIF (NEW.createtime>='2015-1-1' and NEW.createtime<'2015-2-1') THEN  
        INSERT INTO tbl_test_201501 VALUES (NEW.*);
    ELSIF (NEW.createtime>='2015-2-1' and NEW.createtime<'2015-3-1') THEN  
        INSERT INTO tbl_test_201502 VALUES (NEW.*);
    ELSIF (NEW.createtime>='2015-3-1' and NEW.createtime<'2015-4-1') THEN  
        INSERT INTO tbl_test_201503 VALUES (NEW.*);
    ELSIF (NEW.createtime>='2015-4-1' and NEW.createtime<'2015-5-1') THEN  
        INSERT INTO tbl_test_201504 VALUES (NEW.*);
    ELSIF (NEW.createtime>='2015-5-1' and NEW.createtime<'2015-6-1') THEN  
        INSERT INTO tbl_test_201505 VALUES (NEW.*);
    ELSIF (NEW.createtime>='2015-6-1' and NEW.createtime<'2015-7-1') THEN  
        INSERT INTO tbl_test_201506 VALUES (NEW.*);
    ELSIF (NEW.createtime>='2015-7-1' and NEW.createtime<'2015-8-1') THEN  
        INSERT INTO tbl_test_201507 VALUES (NEW.*);
    ELSIF (NEW.createtime>='2015-8-1' and NEW.createtime<'2015-9-1') THEN  
        INSERT INTO tbl_test_201508 VALUES (NEW.*);
    ELSIF (NEW.createtime>='2015-9-1' and NEW.createtime<'2015-10-1') THEN  
        INSERT INTO tbl_test_201509 VALUES (NEW.*);  
    ELSIF (NEW.createtime>='2015-10-1' and NEW.createtime<'2015-11-1') THEN  
        INSERT INTO tbl_test_201510 VALUES (NEW.*);
    ELSIF (NEW.createtime>='2015-11-1' and NEW.createtime<'2015-12-1') THEN  
        INSERT INTO tbl_test_201511 VALUES (NEW.*);
    ELSIF (NEW.createtime>='2015-12-1' and NEW.createtime<'2016-1-1') THEN  
        INSERT INTO tbl_test_201512 VALUES (NEW.*);
    ELSIF (NEW.createtime>='2016-1-1' and NEW.createtime<'2016-2-1') THEN  
        INSERT INTO tbl_test_201601 VALUES (NEW.*);
    ELSIF (NEW.createtime>='2016-2-1' and NEW.createtime<'2016-3-1') THEN  
        INSERT INTO tbl_test_201602 VALUES (NEW.*);
    ELSIF (NEW.createtime>='2016-3-1' and NEW.createtime<'2016-4-1') THEN  
        INSERT INTO tbl_test_201603 VALUES (NEW.*);
    ELSIF (NEW.createtime>='2016-4-1' and NEW.createtime<'2016-5-1') THEN  
        INSERT INTO tbl_test_201604 VALUES (NEW.*);
    ELSIF (NEW.createtime>='2016-5-1' and NEW.createtime<'2016-6-1') THEN  
        INSERT INTO tbl_test_201605 VALUES (NEW.*);
    ELSIF (NEW.createtime>='2016-6-1' and NEW.createtime<'2016-7-1') THEN  
        INSERT INTO tbl_test_201606 VALUES (NEW.*);
    ELSIF (NEW.createtime>='2016-7-1' and NEW.createtime<'2016-8-1') THEN  
        INSERT INTO tbl_test_201607 VALUES (NEW.*);
    ELSIF (NEW.createtime>='2016-8-1' and NEW.createtime<'2016-9-1') THEN  
        INSERT INTO tbl_test_201608 VALUES (NEW.*);                                                                
    ELSE  
        RAISE EXCEPTION 'Date out of range. Fix the tbl_test_insert_trigger() function!';  
    END IF;  
    RETURN NULL;  
END;  
$$  
LANGUAGE plpgsql;

5.使用触发器

CREATE TRIGGER insert_tbl_test_trigger BEFORE INSERT ON tbl_test
FOR EACH ROW EXECUTE PROCEDURE tbl_test_insert_trigger();