【PostgreSQL】PostgreSQL批量删除多余的备份表

---

PostgreSQL批量删除多余的备份表

/* PostgreSQL批量删除多余的备份表 */
DO $$
DECLARE 
  record_tmp RECORD;
  cursor_tmp CURSOR FOR
    select * from pg_tables t
     where schemaname = CURRENT_SCHEMA
       and tablename not like 'act\_%'
       and tablename not like 'tpl\_%'
       -- and tablename like 'tmc%'
       and (tablename not like '%\_t' and tablename not like '%\_ti' and tablename not like '%\_tmp')
       -- 把分区表过滤掉
       -- 查结尾不是"p+4位数字"的，例如：p2023
       and not tablename ~ '[p][0-9]{4}$'
       -- 查结尾不是"p+5位数字"的，例如：p20231
       and not tablename ~ '[p][0-9]{5}$'
       -- 查结尾6位字符不是"others"的
       and not tablename ~ '[others]{6}$'
       -- 查结尾5位字符不是"other"的
       and not tablename ~ '[other]{5}$'
       -- 查结尾是数字的表名
       and (tablename ~ '[0-9]{1}$' or tablename like '%\_bak')
    ;
  i int8:=0;
BEGIN
  -- 打开游标
  OPEN cursor_tmp;
  -- RAISE NOTICE 'i=%',i;
  LOOP
    -- 获取游标中的记录
    FETCH cursor_tmp INTO record_tmp;
    -- 没有找到更多数据时退出循环
    EXIT WHEN NOT FOUND;
    i:=i+1;
    EXECUTE 'DROP TABLE '||record_tmp.tablename||' CASCADE';
    RAISE NOTICE '% - DROP TABLE % CASCADE',i,record_tmp.tablename;
  END LOOP;
  CLOSE cursor_tmp;
END $$;