KingbaseES V8 存储过程动态创建分区表

版本：kingbaseES V8R6  mysql模式

表结构：

-- tr_cle_sys_check_weight_off_site definition

CREATE TABLE tr_cle_sys_check_weight_off_site (
	id varchar(64),
	check_no varchar(64) NOT NULL,
	check_time datetime NOT NULL,
	CONSTRAINT PRIMARY_E8DE7E09 PRIMARY KEY (check_no)
)
PARTITION BY RANGE(check_time);
;

CREATE INDEX check_no_7D2CE502 ON tr_cle_sys_check_weight_off_site (check_no);
CREATE INDEX check_time_3218B19A ON tr_cle_sys_check_weight_off_site (check_time);

存储过程：

CREATE OR REPLACE PROCEDURE tr_cle_sys_check_weight_off_site()
AS $$
DECLARE
    now_time timestamp := NOW();
    tomorrow timestamp := DATE_TRUNC('month', now_time + INTERVAL 1 month)::DATE;
    next_tomorrow timestamp := DATE_TRUNC('month', tomorrow + INTERVAL 1 month)::DATE;
    part_name text := to_char(tomorrow, 'pYYYYMM') ;
    part_value date := (next_tomorrow)::date;
    num integer;
    CreateParSql VARCHAR2(200);

BEGIN
    -- 获取分区数量，判断分区是否存在
    SELECT COUNT(*) INTO num 
    FROM information_schema.partitions
    WHERE  table_name = 'tr_cle_sys_check_weight_off_site'
    AND table_schema = current_schema()
    AND partition_name = part_name;

    IF num = 0 THEN
       RAISE NOTICE 'Partition count: %', num;
       RAISE NOTICE 'para is : %-%-%', part_name,tomorrow,next_tomorrow;
       --根据月份创建分区，如（2024-05-01--2024-06-1）代表5月分区，分区名为：p202405，不包含6月数据。
       CreateParSql := CONCAT('CREATE TABLE tr_cle_sys_check_weight_off_site_',part_name,' partition of tr_cle_sys_check_weight_off_site FOR VALUES FROM (''',tomorrow,''') TO (''',next_tomorrow,''');');
       RAISE NOTICE 'Sql is: %', CreateParSql;
       EXECUTE CreateParSql;

    END IF;
END;
$$ LANGUAGE plpgsql;