使用存储过程删除指定schema中符合过期条件的旧数据实现方法

实现逻辑 #

你需要的功能可以通过plpgsql存储过程配合动态SQL实现，核心逻辑是遍历查询到的表名，动态拼接DELETE语句执行，避免硬编码表名。

完整存储过程代码 #

CREATE OR REPLACE PROCEDURE clean_expired_data(
    -- 入参：目标schema名称、数据保留时长、时间字段名
    IN target_schema text,
    IN retain_interval interval,
    IN time_column text
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_table record;
    v_deleted_rows integer;
BEGIN
    -- 遍历指定schema下所有包含指定时间列的普通表
    FOR v_table IN
        SELECT DISTINCT t.table_name
        FROM information_schema.tables t
        JOIN information_schema.columns c
          ON t.table_schema = c.table_schema AND t.table_name = c.table_name
        WHERE t.table_schema = target_schema
          AND t.table_type = 'BASE TABLE' -- 排除视图、系统表等非业务表
          AND c.column_name = time_column -- 仅保留有指定时间列的表，避免执行报错
    LOOP
        -- 动态执行删除语句，用format转义标识符避免注入，USING传参数避免类型转换问题
        EXECUTE format(
            'DELETE FROM %I.%I WHERE %I < now() - $1',
            target_schema,
            v_table.table_name,
            time_column
        ) USING retain_interval;

        -- 可选：打印删除行数日志，方便核对
        GET DIAGNOSTICS v_deleted_rows = ROW_COUNT;
        RAISE NOTICE '表%.% 完成删除，共删除%条过期数据', target_schema, v_table.table_name, v_deleted_rows;
    END LOOP;
END;
$$;

调用方法 #

替换参数为你实际的配置即可调用，示例如下：

-- 示例：删除public schema下所有表中time列早于12个月的数据
CALL clean_expired_data('public', '12 months'::interval, 'time');

注意事项 #

• 正式执行前建议先做测试：将存储过程中EXECUTE开头的删除语句替换为RAISE NOTICE '%', format(...)，先打印即将执行的SQL语句，确认表名、条件完全符合预期后再执行实际删除操作。
• 如果单表数据量较大，建议分批删除，避免长时间锁表影响业务，也可以调整为在业务低峰期执行。
• 执行存储过程的数据库账号需要拥有目标schema下对应表的DELETE权限，以及information_schema的查询权限。
• 如果你确定所有表都有对应的时间列，也可以去掉存储过程中对columns表的关联查询，直接遍历tables表即可。

内容的提问来源于stack exchange，提问作者agingcabbage32