HGDB批量创建list子分区

HGDB批量创建list子分区

1. 创建分区主表

highgo=# create table list(id int,logdate date) partition by list(logdate);
CREATE TABLE
highgo=# \d+ list
                          分区表 "public.list"
  栏位   |  类型   | 校对规则 | 可空的 | 预设 | 存储  | 统计目标 | 描述
---------+---------+----------+--------+------+-------+----------+------
 id      | integer |          |        |      | plain |          |
 logdate | date    |          |        |      | plain |          |
分区键值: LIST (logdate)
分区数: 0

2. 创建函数，用于批量创建list分区表

highgo=# create or replace function createtb(dt_start date)
highgo-# RETURNS void as $$
highgo$# DECLARE
highgo$#    stmt varchar;
highgo$#    dt_end date :=dt_start;
highgo$#    dt_current date := localtimestamp::DATE;
highgo$#    partnm varchar;
highgo$# BEGIN
highgo$#    while dt_end <= dt_current loop
highgo$#        partnm := 'tb_' || to_char(dt_end,'YYMMDD');
highgo$#        stmt := 'create table ' || partnm || ' partition of list for values in ('''||dt_end||''')';
highgo$#        IF NOT EXISTS(SELECT 1 FROM pg_catalog.pg_class WHERE relname = partnm) THEN
highgo$#           EXECUTE stmt;
highgo$#        END IF;
highgo$#        dt_end := dt_end + INTERVAL '1 months';
highgo$#  end loop;

highgo$#  end loop;
highgo$# end; $$  LANGUAGE plpgsql;
CREATE FUNCTION

3. 调用函数，并验证list分区子表已创建

highgo=# select createtb('20230101'::date);
 createtb
----------

(1 行记录)

highgo=# \d+ list
                          分区表 "public.list"
  栏位   |  类型   | 校对规则 | 可空的 | 预设 | 存储  | 统计目标 | 描述
---------+---------+----------+--------+------+-------+----------+------
 id      | integer |          |        |      | plain |          |
 logdate | date    |          |        |      | plain |          |
分区键值: LIST (logdate)
分区: tb_230101 FOR VALUES IN ('2023-01-01 00:00:00'),
      tb_230201 FOR VALUES IN ('2023-02-01 00:00:00'),
      tb_230301 FOR VALUES IN ('2023-03-01 00:00:00'),
      tb_230401 FOR VALUES IN ('2023-04-01 00:00:00'),
      tb_230501 FOR VALUES IN ('2023-05-01 00:00:00'),
      tb_230601 FOR VALUES IN ('2023-06-01 00:00:00'),
      tb_230701 FOR VALUES IN ('2023-07-01 00:00:00')

highgo=#