PostgreSQL/pgsql自动添加分区子表

网上之前能找到的例子，都是按数量创建分区子表。

而实际业务中，按日期分区应该是主流，如果照搬按数量创建分区子表的话，感觉不太合用，毕竟实施运维人员也不想动脑子，你居然要求不能频繁执行某个函数，然后又要求多久必需执行它一次……

那么，还是应该搞一个按日期创建分区子表的函数，就显得很有必要了。

前段时间就抽空写了这样一个函数，以后项目上可以直接拿来用。

这个函数可以多次执行，而且不会创建过多的分区子表（估计我有精神洁癖），毕竟可以通过 _end_date参数来规范子表的日期范围嘛，贴出来共享给大家。

注释写得比较啰嗦，我也怕时间久了记不清当初的意图，慢慢看哈。

CREATE OR REPLACE FUNCTION append_partition_table(
  _partition_tablename VARCHAR, 
  _begin_date DATE, 
  _end_date DATE, 
  _partition_type INT, 
  _gaps INT)
RETURNS void
AS 
$BODY$
DECLARE 
	-- 分区表子表名
	_partition_child_name VARCHAR;
	-- 循环递增变量i
	i INT DEFAULT 0;
	-- 计算得出的，假设的分区字段From的值，对应的日期
	partition_from_date DATE;
	-- 计算得出的，假设的分区字段To的值，对应的日期，用于判断是否可以退出循环
	partition_to_date DATE;
BEGIN
  -- 功能：不使用分区表插件，为PgSQL内置分区表中，为指定分区主表，按天、按月、按年添加分区
  --       子表，若不是按[天]分区时，添加的分区子表会比[截止日期]稍大些日期
  --       在PostgresSQL v14.1中调试通过
  -- 说明：
  -- 1、使用该存储过程时，只是按指定规则生成分区子表的表名并创建，并不能判断该子表名是否
  --     为分区主表的子表，即，若该子表名已存在则不建表，若该子表名不存在则创建成指定分区
  --     主表的分区子表
  -- 2、分区子表名未加双引号，不区分大小写，默认为小写
  --     分区表子表名 = 主表名 + 分隔符 + 年月日（或者年月，或者年），如，log_all_202001或者log_all_20200201
  -- 3、分区子表将与分区主表在同一个Schema中，本存储过程并不做特殊处理
  --
  -- 4、本存储过程自动创建的分区子表，是range分区表，请自行建好主表，建主表Demo如下：
  -- 创建分区主表
  -- CREATE TABLE queue (
  --   qid varchar(50) NOT NULL,
  --   pati_id varchar(50) NULL,
  --   pati_name varchar(50) NULL,
  --   serial_number varchar(100) NULL,
  --   stateid int4,
  --   validdate date,
  --   PRIMARY KEY (qid, validdate)
  -- ) PARTITION BY range(validdate);
  -- 为分区主表的分区字段创建BRIN索引，分区子表会自动生成相应的索引
  -- CREATE INDEX idx_queue_validdate ON queue USING BRIN(validdate); 

  -- 5、因为没找到如何查找现有分区子表的From和To的值，所以只好强行建表了，有知道的麻烦告诉我，谢谢 

  -- 调用Demo1：SELECT append_partition_table('operation_log', '2021-02-11', '2021-08-11', 0, 1);
  -- 调用Demo2：SELECT append_partition_table('public.operation_log', '2021-02-11', '2021-08-11', 1, 1);

-- 参数：_partition_tablename：创建分区子表的主表的名称, 注意确认表名中是否需要包含Schema名，分区
--       分区子表将与分区主表在同一个Schema中
-- 参数：_begin_date：创建分区表的开始日期
-- 参数：_end_date：创建分区表的截止日期
-- 参数：_partition_type：分区类型[0按天分区，1按月分区，2按年分区]
-- 参数：_gaps：分区间隔，如果分区类型为0，则表示每个分区的间隔为 gaps天
--       如果分区类型为1，则表示每个分区的间隔为 gaps月
--       如果分区类型为2，则表示每个分区的间隔为 gaps年

  -- 输出调试信息
  RAISE NOTICE '_partition_type=[%]', _partition_type;
  RAISE NOTICE '_gaps=[%]', _gaps;
  
  -- _partitiontype 只接收0/1/2三种值
  IF _partition_type not in (0,1,2) THEN
    RAISE EXCEPTION 'The Parameter [_partition_type] is incorrect. Only 0, 1, and 2 are supported!!! '; 
  END IF;
  
  partition_from_date := _begin_date::DATE;
  partition_to_date := _begin_date::DATE;

  -- 开始循环，判断并自动分区
  WHILE (partition_to_date <= _end_date) LOOP
    -- 根据[类型入参]，生成准备创建的分区子表的表名
    IF (_partition_type = 0) THEN
      -- 每个分区按天递增, 递增gaps天, 计算得到From和To的值，并按From的值生成分区子表名中日期的数字部分
      partition_from_date = _begin_date::DATE+ CONCAT((i)*_gaps,' day')::INTERVAL;
      partition_to_date = _begin_date::DATE+ CONCAT((i+1)*_gaps,' day')::INTERVAL;
      _partition_child_name := to_char(partition_from_date::DATE, 'yyyymmdd');
    ELSEIF (_partition_type = 1) THEN
      -- 每个分区按月递增, 递增gaps月, 计算得到From和To的值，并按From的值生成分区子表名中日期的数字部分
      partition_from_date := date_trunc('MONTH', _begin_date::DATE)+ CONCAT((i)*_gaps, ' MONTH')::INTERVAL;
      partition_to_date := date_trunc('MONTH', _begin_date::DATE)+ CONCAT((i+1)*_gaps, ' MONTH')::INTERVAL;
      _partition_child_name := to_char(partition_from_date::DATE, 'yyyymm');
    ELSE
      -- 每个分区按年递增, 递增gaps年, 计算得到From和To的值，并按From的值生成分区子表名中日期的数字部分
      partition_from_date := date_trunc('YEAR', _begin_date::DATE) + CONCAT((i)*_gaps,' YEAR')::INTERVAL;
      partition_to_date := date_trunc('YEAR', _begin_date::DATE) + CONCAT((i+1)*_gaps,' YEAR')::INTERVAL;
      _partition_child_name := to_char(partition_from_date::DATE, 'yyyy');
    END IF;

    -- 拼接分区表子表名，分区表子表名 = 主表名 + 分隔符 + 年月日（或者年月，或者年）
    _partition_child_name := _partition_tablename||'_'||_partition_child_name;

    -- 输出调试信息
    RAISE NOTICE 'partition_from_date=[%]', partition_from_date;
    RAISE NOTICE 'partition_to_date=[%]', partition_to_date;
    RAISE NOTICE '_partition_child_name=[%]', _partition_child_name;

--     CREATE TABLE IF NOT EXISTS  _partition_child_name
--      PARTITION OF _partition_tablename 
--      FOR VALUES FROM (partition_from_date) TO (partition_to_date);
    -- 输出调试信息
    RAISE NOTICE 'Run SQL: CREATE TABLE IF NOT EXISTS % PARTITION OF % FOR VALUES FROM (''%'') TO (''%'') WITH (fillfactor=90);', 
                  _partition_child_name, _partition_tablename, partition_from_date, partition_to_date;
    -- 执行SQL语句，判断指定的表名是否存在，若不存在则创建成指定分区主表的分区子表
    -- [fillfactor=90]也是我的精神洁癖，会浪费10%的磁盘空间，感觉不爽的朋友请自行去掉
    execute format('CREATE TABLE IF NOT EXISTS %s PARTITION OF %s FOR VALUES FROM (''%s'') TO (''%s'') WITH (fillfactor=90);',
                  _partition_child_name, _partition_tablename, partition_from_date, partition_to_date);
    RAISE NOTICE 'Run SQL OK!';
    
    -- 递增变量
    i := i + 1;
  END LOOP; -- End While

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
;

测试：

SELECT append_partition_table('kysoft.log_all', '2020-02-01', '2020-02-09', 0, 3);

执行结果（意思一下，不贴图了，中间三个表是上面SQL语句建出来的，另两个是之前建的）：

Table Name｜schema

log_all_202001    kysoft
log_all_20200201    kysoft
log_all_20200204    kysoft
log_all_20200207    kysoft
log_all_his    kysoft

另外，因为没找到如何查找现有分区子表的From和To的值，所以只好强行建表了，有知道的麻烦告诉我，谢谢！