【数据库】Postgresql、PG的分区操作：创建、删除指定分区，非分区表转分区表

〇、参考链接

 

一、为表创建指定分区

-- 表创建分区 参数  表名 分区序列  例如: ltc_customer , 20220915 则创建 ltc_customer_20220915 分区表
CREATE or replace FUNCTION createPartitionIfNotExists (tb_name VARCHAR, partiton_val VARCHAR) RETURNS void AS $body$
  DECLARE master_name TEXT := tb_name; -- 创建分区表的表名
  DECLARE partition_name TEXT := tb_name || '_' || partiton_val; -- 分区的表名称

  BEGIN
    -- 判断分区名称是否存在，不存在时才需要创建
    IF to_regclass (partition_name) IS NULL THEN
      -- 执行创建分区
      EXECUTE format (
          'create table %I partition of %I for values in (%s)',
          partition_name,
          master_name,
          partiton_val
      );
    END IF;
  END;
$body$ LANGUAGE plpgsql;

二、删除指定的表分区

-- 删除表分区 例如: ltc_customer_20220915
CREATE or replace FUNCTION deletePartitionIfExists (tb_name_partiton_val VARCHAR) RETURNS void AS $body$
  DECLARE master_name TEXT := tb_name_partiton_val; -- 删除分区表 表名

  BEGIN
    -- 判断分区名称是否存在，不存在时才需要创建
    IF to_regclass (tb_name_partiton_val) is not null THEN
      -- 执行创建分区
      EXECUTE format ('DROP TABLE IF EXISTS %s ',tb_name_partiton_val);
    END IF;
  END;
$body$ LANGUAGE plpgsql;

三、将原有的非分区表转换为分区表

-- 将原有的非分区表转化为分区表
CREATE OR REPLACE FUNCTION "table_migration"("tb_name" varchar, "partition_column" varchar)
  RETURNS "pg_catalog"."void" AS $BODY$ DECLARE
DECLARE
  old_name TEXT := tb_name || '_old';
declare d varchar;
declare days varchar[] :=   array(select to_char(date(t),'yyyymmdd')::varchar as day
 from 
 generate_series('2022-08-01'::date,'2022-11-01', '1 days') as t );
BEGIN
  IF
    to_regclass ( tb_name ) IS NOT NULL THEN
      -- 1. 表重命名
      EXECUTE format ( 'ALTER TABLE %s RENAME TO %s', tb_name, old_name );
      
      -- 2. 创建分区表
      EXECUTE format ( 'CREATE TABLE %s ( LIKE %s INCLUDING ALL ) PARTITION BY list ( %s )', tb_name, old_name, partition_column );
      
      -- 3. 创建2022年一年的分区
      foreach d in array days loop
        EXECUTE format ( 'create table %s partition of %s for values in (%s)', tb_name || '_' || d, tb_name, d );
      end loop;
      
      -- 4. 执行数据迁移
      EXECUTE format ( 'insert into %s select * from %s', tb_name, old_name );
      
      -- 5. 删除原来的表，可以先不做
      -- EXECUTE format ( 'drop table %s',  old_name );
  END IF;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100