sql - Postgres 表中所有列的数组聚合

Question

WITH Dept(DName, HeadCount) AS (
   VALUES ('D1', 5), ('D2', 6), ('D3', 7)
) 

Select array_agg(DName), array_agg(HeadCount) from Dept

上面的查询将会产生。

"{D1,D2,D3}";"{5,6,7}"

有没有办法在不提及列名的情况下生成相同的输出，即在查询中以通用方式。

是否有任何内置函数代替以下查询中的 magic_function？

 Select magic_function(*) from Dept

或者是否有任何迂回方式，例如加入information_schema.columns，这将动态提取字段名称。

Answer 1

没有我所知道的魔法函数，这意味着标准 Postgres 中可能没有这样的东西。

信息架构或目录表对您示例中的临时行类型没有帮助。但是您可能希望使用一个表来实现这一点，该表将使用已注册的行类型并提供以下路径:

WITH x AS (SELECT 'mytable'::regclass AS tbl)
SELECT 'SELECT '
     || string_agg(format('array_agg(%I) AS %1$I_arr', a.attname), E'\n ,')
     || E'\nFROM ' || tbl
FROM   x
JOIN   pg_attribute a ON a.attrelid = x.tbl
WHERE  NOT a.attisdropped  -- no dropped (dead) columns
AND    a.attnum > 0        -- no system columns (you may or may not want this)
GROUP  BY x.tbl

这会动态生成适当的 SQL 语句。
将表名转换为 regclass 可以安全地防止 SQL 注入(inject)。此相关答案中的更多解释:
Table name as a PostgreSQL function parameter

结果:

SELECT array_agg(col1) AS col1_arr
 ,array_agg("illegal nAmE") AS "illegal nAmE_arr"
 , ...
FROM mytable

在函数中使用动态 SQL 实现完全自动化并不容易，因为返回类型也是动态的。您可以创建一个游标并返回它，但这可能有点矫枉过正。