知识分享之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
本文声明：
知识共享许可协议
本作品由 cn華少 采用 知识共享署名-非商业性使用 4.0 国际许可协议 进行许可。
