PostgreSQL动态CTE查询：空结果时如何返回列名？

问题背景 #

构建了一个动态查询，可一次性返回以下内容：

• CTE中条目的总数
• CTE的分页条目
• CTE使用的列名列表
• 请求的页大小和页码

该查询的优势是一次数据库往返即可获取总数与分页数据，且支持插入任意动态生成的CTE。但存在问题：当CTE无行数据（如WHERE条件未匹配）时，外层查询也会为空，导致无法提取列名。

原SQL代码 #

with "cte" as (
select...)
select
    (
    select
        array_agg(column_name)
    from
        (
        select
            jsonb_object_keys(to_jsonb(cte)) as column_name) as column_names) as "ColumnNames",
    COUNT(*) over() as "TotalCount",
    (
    select
        array_agg(cte_subquery)
    from
        (
        select
            *
        from
            cte offset 30
        limit 15) as cte_subquery) as "PaginatedEntries",
    3 as "PageNumber",
    15 as "PageSize"
from
    "cte"
limit 1

期望结果 #

+-------------+------------+------------------+------------+----------+
|  ColumnNames | TotalCount | PaginatedEntries | PageNumber | PageSize |
+--------------+------------+------------------+------------+----------+
| {id,age,name,email} | 0   |         {}       |          3 |       15 |
+--------------+------------+------------------+------------+----------+

解决方案 #

核心思路是确保外层查询始终返回至少一行，同时保留CTE的列结构以便提取列名，具体修改如下：

WITH "cte" AS (
    SELECT ... -- 替换为你的动态CTE内容
)
SELECT
    (
        SELECT array_agg(column_name)
        FROM (
            SELECT jsonb_object_keys(to_jsonb(cte)) AS column_name
        ) AS column_names
    ) AS "ColumnNames",
    COUNT(cte.*) OVER () AS "TotalCount",
    (
        SELECT array_agg(cte_subquery)
        FROM (
            SELECT * FROM cte OFFSET 30 LIMIT 15
        ) AS cte_subquery
    ) AS "PaginatedEntries",
    3 AS "PageNumber",
    15 AS "PageSize"
FROM cte
RIGHT JOIN (SELECT 1) AS dummy ON true -- 强制返回至少一行
LIMIT 1

修改说明 #

1. RIGHT JOIN (SELECT 1) AS dummy ON true：无论CTE是否有数据，外层查询都会返回一行。当CTE为空时，该行的所有CTE列值为NULL，但列结构完整保留。
2. COUNT(cte.*) OVER ()：统计CTE中的真实行数，空数据时返回0（因为cte.*为NULL时不会被COUNT统计）。
3. 列名提取逻辑：通过to_jsonb(cte)将全NULL的行转为JSONB，再用jsonb_object_keys提取所有列名，确保无数据时仍能获取完整列名列表。

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