继承

最新推荐文章于 2024-02-15 15:25:14 发布

bielidefeng

最新推荐文章于 2024-02-15 15:25:14 发布

阅读量607

点赞数

分类专栏： postgresql

本文链接：https://blog.csdn.net/bielidefeng/article/details/27649969

版权

postgresql 专栏收录该内容

33 篇文章 0 订阅

订阅专栏

继承：

http://www.postgresql.org/docs/9.3/static/ddl-inherit.html

分区表：

http://www.postgresql.org/docs/9.3/static/ddl-partitioning.html

分区表需要和trigger一起使用才可。


CREATE TABLE measurement (
    city_id         int not null,
    logdate         int,
    peaktemp        int,
    unitsales       int
);
CREATE TABLE measurement_y2006m02 (
    CHECK ( logdate >=   0 AND logdate <   20 )
) INHERITS (measurement);
CREATE TABLE measurement_y2006m03 (
    CHECK ( logdate >=   20 AND logdate <   40 )
) INHERITS (measurement);
CREATE TABLE measurement_y2007m12 (
    CHECK ( logdate >=   40 )
) INHERITS (measurement);
CREATE TABLE measurement_y2005m02 (
    CHECK ( logdate >=   -20 AND logdate <   0 )
) INHERITS (measurement);
CREATE OR REPLACE FUNCTION measurement_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
    IF ( NEW.logdate >=   0 AND
         NEW.logdate <   20 ) THEN
        INSERT INTO measurement_y2006m02 VALUES (NEW.*);
    ELSIF ( NEW.logdate >=   20 AND
            NEW.logdate <   40 ) THEN
        INSERT INTO measurement_y2006m03 VALUES (NEW.*);
    --...
    ELSIF ( NEW.logdate >=   -20 AND
            NEW.logdate <   0 ) THEN
        INSERT INTO measurement_y2005m02 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 measurement
    FOR EACH ROW EXECUTE PROCEDURE measurement_insert_trigger();
insert into measurement values(1,1,1,1);
select * from only measurement;
select * from only only measurement;
select * from measurement_y2006m02;