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

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

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; 

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