pgsql使用积累系列_表分区（继承）

写在前面：这不是一个知识系列博客，这只是对本人使用过知识的一个记录，最好的资源应该是官方文档。

官网doc: https://www.postgresql.org/docs/current/static/plpgsql.html

-- 自定义rule性能比触发器好

-- 继承实现表分区
--创建主表：
CREATE TABLE mastermeasure (
    city_id         int not null,
    logdate         date not null,
    peaktemp        int,
    unitsales       int
);
-- 创建子表
CREATE TABLE mastermeasure_y2018m02 (
CHECK ( logdate >= DATE '2018-02-01' AND logdate < DATE '2018-03-01' )
) INHERITS (mastermeasure);

-- 给已存在并且有数据的表创建分区关系
CREATE TABLE users3 (firstname text, lastname text, id serial primary key);
alter table users3 add CONSTRAINT c_users
check (firstname='sun')；
insert into users3 select 'sun','testuser3', generate_series(51,60)；
alter table users3 inherit users；

-- 方式一 触发器实现
-- 创建触发器函数
CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF ( NEW.logdate >= DATE '2017-11-01' AND
         NEW.logdate < DATE '2017-12-01' ) THEN
        INSERT INTO mastermeasure_y2017m11 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2017-12-01' AND
            NEW.logdate < DATE '2018-01-01' ) THEN
        INSERT INTO mastermeasure_y2017m12 VALUES (NEW.*);
    ELSIF ( NEW.logdate >= DATE '2018-01-01' AND
            NEW.logdate < DATE '2018-02-01' ) THEN
        INSERT INTO mastermeasure_y2018m01 VALUES (NEW.*);
ELSIF ( NEW.logdate >= DATE '2018-02-01' AND
            NEW.logdate < DATE '2018-03-01' ) THEN
        INSERT INTO mastermeasure_y2018m02 VALUES (NEW.*);
    ELSE
        RAISE EXCEPTION 'Date out of range.  Fix the measurement_insert_trigger() function!';
    END IF;
    RETURN NULL;
END;
$$
LANGUAGE plpgsql;
-- 创建触发器
CREATE TRIGGER insert_measurement_trigger
    BEFORE INSERT ON mastermeasure
    FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();

-- 方式二 自定义规则实现
CREATE RULE mastermeasure_insert_y2017m11 AS
ON INSERT TO mastermeasure WHERE
    ( logdate >= DATE '2017-11-01' AND logdate < DATE '2017-12-01' )
DO INSTEAD
    INSERT INTO mastermeasure_y2017m11 VALUES (NEW.*);
CREATE RULE mastermeasure_insert_y2017m12 AS
ON INSERT TO mastermeasure WHERE
    ( logdate >= DATE '2017-12-01' AND logdate < DATE '2018-01-01' )
DO INSTEAD
    INSERT INTO mastermeasure_y2017m12 VALUES (NEW.*);
CREATE RULE mastermeasure_insert_y2018m01 AS
ON INSERT TO mastermeasure WHERE
    ( logdate >= DATE '2018-01-01' AND logdate < DATE '2018-02-01' )
DO INSTEAD
    INSERT INTO mastermeasure_y2018m01 VALUES (NEW.*);
CREATE RULE mastermeasure_insert_y2018m02 AS
ON INSERT TO mastermeasure WHERE
    ( logdate >= DATE '2018-02-01' AND logdate < DATE '2018-03-01' )
DO INSTEAD
    INSERT INTO mastermeasure_y2018m02 VALUES (NEW.*);