知识分享之PostgreSQL——快速清除表中的数据

背景

日常我们开发时，我们会遇到各种各样的奇奇怪怪的问题（踩坑o(╯□╰)o），这个常见问题系列就是我日常遇到的一些问题的记录文章系列，这里整理汇总后分享给大家，让其还在深坑中的小伙伴有绳索能爬出来。
同时在这里也欢迎大家把自己遇到的问题留言或私信给我，我看看其能否给大家解决。

开发环境

• 系统：windows10
• 版本：PostgreSQL 13

内容

我们想要对于全库中的所有表进行清除数据操作，这时我们需要用到truncate table [表名] 相关语句，清除单张表这样是可以的，但当我们有很多很多的表时怎么办呢，这时我们就需要使用到存储过程了

1、首先我们创建一个自定义类型，用于存储表名和数据的行数

CREATE TYPE table_count AS (table_name TEXT, num_rows INTEGER); 

2、进行创建一个存储过程

CREATE OR REPLACE FUNCTION count_em_all (limitNum INTEGER) RETURNS SETOF table_count  AS '
DECLARE 
    the_count RECORD;   -- 当前行数
    t_name RECORD;   -- 当前表名
    r table_count%ROWTYPE;
BEGIN
-- 循环所有表，对于每个表进行相关操作。
    FOR t_name IN 
        
                select concat_ws(''.'',''"''||pn.nspname||''"'',''"''||pc.relname||''"'') as relname from pg_class as pc
                LEFT JOIN pg_namespace as pn ON pn.oid = pc.relnamespace
                where pc.relkind = ''r'' and pn.nspname !=''pg_catalog''
        LOOP
            -- 对当前循环到的表名进行统计行数，这里我们使用的count，实际上如果要高效建议使用数据库中的大概统计，而不是这个。
            FOR the_count IN EXECUTE ''SELECT COUNT(*) AS "count" FROM '' || t_name.relname 
            LOOP 
            END LOOP; 
            -- 存储表名
            r.table_name := t_name.relname; 
            -- 存储行数
            r.num_rows := the_count.count; 
                                            -- 如果当前我们传入的是一个大于0的，则认为需要执行清除操作。
                        IF limitNum>0 THEN
                                                        -- 开始进行清除大于限定数量的表数据。
                            IF r.num_rows>limitNum THEN
                                LOOP
                                 EXECUTE ''truncate table '' || r.table_name;
                                END LOOP; 
                            END IF;
                        END IF;
            RETURN NEXT r; 
        END LOOP; 
        RETURN; 
END;
' LANGUAGE plpgsql; 

3、进行执行存储过程

-- 清除所有超过1w条数据的表数据。
SELECT * FROM count_em_all(10000) AS r ORDER BY r.num_rows DESC;

这样我们就可以有效的清除超过限定数量的表数据了，不过当前存在一定的性能问题，后续再进行补充高效的存储过程。

参见文章：
https://www.cnblogs.com/tommy-huang/p/9365907.html
https://www.coderbusy.com/archives/1842.html

本文声明：

88x31.png
知识共享许可协议
本作品由 cn華少 采用 知识共享署名-非商业性使用 4.0 国际许可协议 进行许可。