postgresql继承表和分区

-- 1. 创建主表结构, 表名称 tbl_partition, 其中的时间字段名: gather_time

CREATE TABLE tbl_partition

(

id integer,

data VARCHAR,

serial INTEGER,

site_no VARCHAR,

packet_time timestamp

);

-- 2. 创建可重用的触发器函数: auto_insert_into_tbl_partition( time_column_name )

-- 代码如下:

CREATE OR REPLACE FUNCTION auto_insert_into_tbl_partition()

RETURNS trigger AS

$BODY$

DECLARE

serial_new text ; -- 父表中用于分区的时间字段的名称[必须首先初始化!!]

site_no_new text ; -- 父表中用于分区的时间字段的名称[必须首先初始化!!]

time_column_name text ; -- 父表中用于分区的时间字段的名称[必须首先初始化!!]

curMM varchar(6); -- 'YYYYMM'字串,用做分区子表的后缀

serial text;

site_no text;

isExist_serial boolean; -- 分区子表,是否已存在

isExist_site_no boolean; -- 分区子表,是否已存在

isExist boolean; -- 分区子表,是否已存在

startTime text;

endTime text;

strSQL text;

serialStr text;

siteNoStr text;

BEGIN

-- 调用前,必须首先初始化(时间字段名):time_column_name [直接从调用参数中获取!!]

serial_new := TG_ARGV[0];

site_no_new := TG_ARGV[1];

time_column_name := TG_ARGV[2];

EXECUTE 'SELECT $1.'||serial_new INTO serialStr USING NEW;

serial := cast(serialStr as VARCHAR);

EXECUTE 'SELECT $1.'||site_no_new INTO siteNoStr USING NEW;

site_no :=siteNoStr;

EXECUTE 'SELECT $1.'||time_column_name INTO strSQL USING NEW;

curMM := to_char( strSQL::timestamp , 'YYYYMM' );

-- 判断对应分区表 是否已经存在?

select count(*) INTO isExist_serial from pg_class where relname = (TG_RELNAME||'_'||serial);

-- 若不存在, 则插入前需 先创建子分区

IF ( isExist_serial = false ) THEN

-- 创建子分区表

strSQL := 'CREATE TABLE IF NOT EXISTS '||TG_RELNAME||'_'||serial||

' ( CHECK(serial= '|| serial ||' )) INHERITS ('||TG_RELNAME||') ;' ;

EXECUTE strSQL;

-- 创建索引

strSQL := 'CREATE INDEX '||TG_RELNAME||'_'||serial||'_INDEX_'||time_column_name||' ON '

||TG_RELNAME||'_'||serial||' ('||time_column_name||');' ;

EXECUTE strSQL;

END IF;

-- 判断对应分区表 是否已经存在?

select count(*) INTO isExist_site_no from pg_class where relname = (TG_RELNAME||'_'||serial||'_'||site_no);

-- 若不存在, 则插入前需 先创建子分区

IF ( isExist_site_no = false ) THEN

-- 创建子分区表

strSQL := 'CREATE TABLE IF NOT EXISTS '||TG_RELNAME||'_'||serial||'_'||site_no||

' ( CHECK(site_no ='''|| siteNoStr ||''' ) ) INHERITS ('||TG_RELNAME||'_'||serial||') ;' ;

EXECUTE strSQL;

-- 创建索引

strSQL := 'CREATE INDEX '||TG_RELNAME||'_'||serial||'_'||site_no||'_INDEX_'||time_column_name||' ON '

||TG_RELNAME||'_'||serial||'_'||site_no||' ('||time_column_name||');' ;

EXECUTE strSQL;

END IF;

 

select count(*) INTO isExist from pg_class where relname = (TG_RELNAME||'_'||serial||'_'||site_no||'_'||curMM);

-- 若不存在, 则插入前需 先创建子分区

IF ( isExist = false ) THEN

-- 创建子分区表

startTime := curMM||'01 00:00:00.000';

endTime := to_char( startTime::timestamp + interval '1 month', 'YYYY-MM-DD HH24:MI:SS.MS');

strSQL := 'CREATE TABLE IF NOT EXISTS '||TG_RELNAME||'_'||serial||'_'||site_no||'_'||curMM||

' ( CHECK('||time_column_name||'>='''|| startTime ||''' AND '

||time_column_name||'< '''|| endTime ||''' )

) INHERITS ('||TG_RELNAME||'_'||serial||'_'||site_no ||') ;' ;

EXECUTE strSQL;

-- 创建索引

strSQL := 'CREATE INDEX '||TG_RELNAME||'_'||serial||'_'||site_no||'_'||curMM||'_INDEX_'||time_column_name||' ON '

||TG_RELNAME||'_'||serial||'_'||site_no||'_'||curMM||' ('||time_column_name||');' ;

EXECUTE strSQL;

END IF;

-- 插入数据到子分区!

strSQL := 'INSERT INTO '||TG_RELNAME||'_'||serial||'_'||site_no||'_'||curMM||' SELECT $1.*' ;

EXECUTE strSQL USING NEW;

RETURN NULL;

END

$BODY$

LANGUAGE plpgsql;

-- 3. 为主表创建触发器, 其中,调用了触发器函数 auto_insert_into_tbl_partition('gather_time') 

drop trigger insert_tbl_partition_trigger on tbl_partition;

CREATE TRIGGER insert_tbl_partition_trigger

BEFORE INSERT

ON tbl_partition

FOR EACH ROW

EXECUTE PROCEDURE auto_insert_into_tbl_partition('serial','site_no','packet_time');

-- 注: 虽然触发器函数缺省不带参数, 此处调用仍然必须传入时间字段名称作为参数. 否则, 函数将不知道以何字段来对主表分区!

 

 

 

--4:插入数据

INSERT INTO tbl_partition (id ,data ,serial ,site_no,packet_time ) VALUES(22,'2',15,'816006001',(now() + interval '20 day'));

INSERT INTO tbl_partition (id ,data ,serial ,site_no,packet_time ) VALUES(33,'3',15,'816006001',(now() + interval '10 day'));

INSERT INTO tbl_partition (id ,data ,serial ,site_no,packet_time ) VALUES(44,'4',15,'816006001',(now() - interval '0 day'));

INSERT INTO tbl_partition (id ,data ,serial ,site_no,packet_time ) VALUES(55,'5',15,'816006001',(now() - interval '10 day'));

INSERT INTO tbl_partition (id ,data ,serial ,site_no,packet_time ) VALUES(66,'6',15,'816006001',(now() - interval '20 day'));

INSERT INTO tbl_partition (id ,data ,serial ,site_no,packet_time ) VALUES(77,'7',15,'816006001',(now() - interval '30 day'));

INSERT INTO tbl_partition (id ,data ,serial ,site_no,packet_time ) VALUES(88,'8',15,'816006001',(now() - interval '40 day'));

INSERT INTO tbl_partition (id ,data ,serial ,site_no,packet_time ) VALUES(99,'9',15,'816006001',(now() - interval '50 day'));

 

 

 

INSERT INTO tbl_partition (id ,data ,serial ,site_no,packet_time ) VALUES(22,'2',12,'816006001',(now() + interval '20 day'));

INSERT INTO tbl_partition (id ,data ,serial ,site_no,packet_time ) VALUES(33,'3',12,'816006001',(now() + interval '10 day'));

INSERT INTO tbl_partition (id ,data ,serial ,site_no,packet_time ) VALUES(44,'4',12,'816006001',(now() - interval '0 day'));

INSERT INTO tbl_partition (id ,data ,serial ,site_no,packet_time ) VALUES(55,'5',12,'816006001',(now() - interval '10 day'));

INSERT INTO tbl_partition (id ,data ,serial ,site_no,packet_time ) VALUES(66,'6',12,'816006001',(now() - interval '20 day'));

INSERT INTO tbl_partition (id ,data ,serial ,site_no,packet_time ) VALUES(77,'7',12,'816006001',(now() - interval '30 day'));

INSERT INTO tbl_partition (id ,data ,serial ,site_no,packet_time ) VALUES(88,'8',12,'816006001',(now() - interval '40 day'));

INSERT INTO tbl_partition (id ,data ,serial ,site_no,packet_time ) VALUES(99,'9',12,'816006001',(now() - interval '50 day'));

 

 

 

 

INSERT INTO tbl_partition (id ,data ,serial ,site_no,packet_time ) VALUES(22,'2',16,'816006002',(now() + interval '20 day'));

INSERT INTO tbl_partition (id ,data ,serial ,site_no,packet_time ) VALUES(33,'3',16,'816006002',(now() + interval '10 day'));

INSERT INTO tbl_partition (id ,data ,serial ,site_no,packet_time ) VALUES(44,'4',16,'816006002',(now() - interval '0 day'));

INSERT INTO tbl_partition (id ,data ,serial ,site_no,packet_time ) VALUES(55,'5',16,'816006002',(now() - interval '10 day'));

INSERT INTO tbl_partition (id ,data ,serial ,site_no,packet_time ) VALUES(66,'6',16,'816006002',(now() - interval '20 day'));

INSERT INTO tbl_partition (id ,data ,serial ,site_no,packet_time ) VALUES(77,'7',16,'816006002',(now() - interval '30 day'));

INSERT INTO tbl_partition (id ,data ,serial ,site_no,packet_time ) VALUES(88,'8',16,'816006002',(now() - interval '40 day'));

INSERT INTO tbl_partition (id ,data ,serial ,site_no,packet_time ) VALUES(99,'9',16,'816006002',(now() - interval '50 day'));

 

 

 

INSERT INTO tbl_partition (id ,data ,serial ,site_no,packet_time ) VALUES(22,'2',12,'816006002',(now() + interval '20 day'));

INSERT INTO tbl_partition (id ,data ,serial ,site_no,packet_time ) VALUES(33,'3',12,'816006002',(now() + interval '10 day'));

INSERT INTO tbl_partition (id ,data ,serial ,site_no,packet_time ) VALUES(44,'4',12,'816006002',(now() - interval '0 day'));

INSERT INTO tbl_partition (id ,data ,serial ,site_no,packet_time ) VALUES(55,'5',12,'816006002',(now() - interval '10 day'));

INSERT INTO tbl_partition (id ,data ,serial ,site_no,packet_time ) VALUES(66,'6',12,'816006002',(now() - interval '20 day'));

INSERT INTO tbl_partition (id ,data ,serial ,site_no,packet_time ) VALUES(77,'7',12,'816006002',(now() - interval '30 day'));

INSERT INTO tbl_partition (id ,data ,serial ,site_no,packet_time ) VALUES(88,'8',12,'816006002',(now() - interval '40 day'));

INSERT INTO tbl_partition (id ,data ,serial ,site_no,packet_time ) VALUES(99,'9',12,'816006002',(now() - interval '50 day'));

 

--5:查询主表和分区表的数据

SELECT * FROM tbl_partition;

SELECT * FROM ONLY tbl_partition;

SELECT * FROM ONLY tbl_partition_serial;

SELECT * FROM ONLY tbl_partition_serial_site_no;

SELECT * FROM ONLY tbl_partition_serial_site_no_202003;

遗留问题:

  分区表自动创建,随着时间推移,分区表会越来越多,我们应该怎么出来呢?希望各位大神告知,谢谢