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’)

原文地址：http://www.cnblogs.com/liujinhui/p/16852389.html