如何在PostgreSQL中关联执行函数并合并结果，附CSV导出方案

这问题我太熟了！要实现这种逐表统计总大小和可回收空间并合并结果的需求，PostgreSQL的LATERAL JOIN简直是量身定做的工具，具体操作步骤如下：

1. 先确保pgstattuple扩展已安装 #

pgstattuple不是PostgreSQL默认自带的扩展，得先安装它（只需执行一次）：

CREATE EXTENSION IF NOT EXISTS pgstattuple;

用IF NOT EXISTS可以避免重复安装报错，很贴心对吧？

2. 合并查询的完整SQL语句 #

我们可以用LATERAL JOIN让主查询的每一行结果都调用一次pgstattuple函数，然后把两边的字段合并起来：

SELECT 
    main.relation,
    main.total_size_pretty,
    main.total_size,
    stats.recoverable_space_pretty,
    stats.recoverable_space
FROM (
    -- 主查询：获取所有目标表的名称和总大小
    SELECT 
        nspname || '.' || relname AS relation,
        pg_size_pretty(pg_total_relation_size(C.oid)) AS total_size_pretty,
        pg_total_relation_size(C.oid) AS total_size
    FROM pg_class C
    LEFT JOIN pg_namespace N ON (N.oid = C.relnamespace)
    WHERE 
        nspname NOT IN ('pg_catalog', 'information_schema')
        AND C.relkind <> 'i'  -- 排除索引
        AND nspname !~ '^pg_toast'  -- 排除toast表
) AS main
-- 对主查询的每一行调用pgstattuple，计算可回收空间
CROSS JOIN LATERAL (
    SELECT 
        pg_size_pretty(dead_tuple_len + free_space) AS recoverable_space_pretty,
        (dead_tuple_len + free_space) AS recoverable_space
    FROM extensions.pgstattuple(main.relation::regclass)
) AS stats;

小提示： #

如果有些表因为权限问题导致pgstattuple调用失败，你可以把CROSS JOIN LATERAL改成LEFT JOIN LATERAL，这样那些表的可回收空间字段会显示为NULL，而不会导致整个查询中断。

3. 将结果导出为CSV格式 #

有三种常用的导出方式，按需选择：

方式一：用psql客户端的\copy命令（推荐，适合本地导出） #

在psql连接到数据库后执行：

\copy (
    -- 把上面的完整合并查询放这里
) TO '/你本地的路径/output.csv' WITH (FORMAT csv, HEADER, DELIMITER ',');

注意这里用的是\copy（带反斜杠），它会把结果导出到客户端本地路径，不需要服务器端的文件权限。

方式二：命令行直接导出 #

在终端执行这条命令，直接输出CSV到文件：

psql -d 你的数据库名 -U 你的用户名 --csv -c "上面的完整合并查询" > output.csv

--csv参数会自动让输出带上表头，格式符合CSV规范。

方式三：服务器端COPY语句（适合导出到服务器本地） #

如果需要把文件存在数据库服务器上，用标准的COPY语句：

COPY (
    -- 上面的完整合并查询
) TO '/服务器上的路径/output.csv' WITH (FORMAT csv, HEADER, DELIMITER ',');

这个需要PostgreSQL对应的操作系统用户有该路径的写入权限。

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