Array aggregation for all columns in a table in Postgres

Question

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

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

The above query will yield.

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

Is there a way to generate the same output without mentioning column names, i.e. in a generic way, in the query.

Is there any inbuilt function, in place of the magic_function in the following query?

 Select magic_function(*) from Dept

Or is there any round about way, such as join with information_schema.columns, that will extract the field name dynamically.

Answer 1

There is no magic function that I'd know of, meaning there probably is no such thing in standard Postgres.

The information schema or catalog tables would not help with an ad-hoc row type in your example. But you are probably looking to do this with a table, which would use a registered row type and offer this route:

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

This generates an appropriate SQL statement dynamically.
The cast of the table name to regclass makes it safe against SQL injection. More explanation in this related answer:
Table name as a PostgreSQL function parameter

Result:

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

Full automation with dynamic SQL in a function is not easy, because the return type is dynamic as well. You could create a cursor and return it, but that's probably overkill.