postgresql函数：定期删除模式下指定天数前的表数据及分区物理表

一、现有函数
-- 1、现有函数调用
select ods.deletePartitionIfExists('fact_ship' || '_' || to_char(CURRENT_DATE - INTERVAL'2 month','yyyymmdd'));
-- 2、函数内容
CREATE OR REPLACE FUNCTION "ods"."deletepartitionifexists"("tb_name_partiton_val" varchar)
  RETURNS "pg_catalog"."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 %s ',tb_name_partiton_val);
    END IF;
  END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

-- 二、依赖项
-- 要求：保留近一个月、两个月、一星期的分区，之前的都删除
-- 函数体：del_schema_period(schema_name,table_name,period_saved)
-- 1、现有函数逻辑
-- 判断存在表的话（to_regclass (tb_name_partiton_val)），删除两月前的当日分区
drop table if exists ap.fact_ship_20220910;
select to_regclass('fact_ship') ; --是否存在此表，不存在返回null，存在返回表名
-- 2、日期
select to_char(date_trunc('day',now())+'-1 day','yyyymmdd');
-- 3、查询模式下的表
select tablename
from pg_tables
where schemaname = 'ods'
and tablename like 'ods_icsale_%';
-- and position ('_2' in tablename) = 0;

-- 三、过程
-- 要求：保留近一个月、两个月、一星期的分区，之前的都删除【先删除数据，再判断是否存在表，删除结构】
-- 函数体：del_schema_period(schema_name,table_name,period_saved)
-- 调用：select ods.del_schema_period('ap','fact_ship','4 days')
select ods.del_schema_period('ap','fact_ship','4 days')
-- 1、删除指定日期的数据
CREATE OR REPLACE FUNCTION "ods"."del_schema_period"(schema_name varchar,table_name varchar,period_saved varchar)
  RETURNS "pg_catalog".void AS $BODY$
  BEGIN
      EXECUTE format('delete from %s.%s where dt < to_char(date_trunc(''day'',now())+''- %s'',''yyyymmdd'')',schema_name,table_name,period_saved);
  END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

-- 2、如果是分区表，删除表结构
CREATE OR REPLACE FUNCTION "ods"."del_schema_period"(schema_name varchar,table_name varchar,period_saved varchar)
  RETURNS "pg_catalog".void AS $BODY$
  DECLARE arrat_t varchar[];
    DECLARE dt_split INTERVAL='-' ||period_saved;
    DECLARE tb_each VARCHAR;
  BEGIN
        -- 删除指定日期的数据
        EXECUTE format('delete from %s.%s where dt < to_char(date_trunc(''day'',now())+''- %s'',''yyyymmdd'')',schema_name,table_name,period_saved);
        -- 如果存在分区表，则删除对应的物理表
        select array(select tablename
        from (
            select tablename,concat('20',split_part(tablename,'_20',2)) as mon_day
            from pg_tables
            where schemaname = schema_name
            and tablename like concat(table_name,'_%')
        ) a
        where mon_day < to_char(date_trunc('day',now()) + dt_split,'yyyymmdd')) into arrat_t;
        raise notice 'helloworld %',arrat_t;
        -- 遍历数组的每个值，删除对应的表结构
                foreach tb_each in array arrat_t
        loop
            -- 删除表
                        raise notice 'helloworld %',tb_each;
                        EXECUTE format('DROP TABLE IF EXISTS %s.%s', schema_name,tb_each);
        end loop;
  END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
-- 使用：
select ods.del_schema_period('ap','fact_ship','70 days')

作者：​​哥们要飞​​