Does PostgreSQL document and support this behavior of the SRF unnest()
as a computer/virtual column,
SELECT
unnest(array_agg(id)) AS id
FROM foo
GROUP BY CUBE (a,b,c,d)
HAVING count(*) > 1
If I try to move the unnest
to FROM there, I get
ERROR: aggregate functions are not allowed in functions in FROM
LINE X: CROSS JOIN LATERAL unnest(array_agg(id)) AS id
You can create the test data to run against this, like this
CREATE TABLE foo AS
SELECT row_number() OVER (), *
FROM ( VALUES
( 'X', 'D', 'G', 'P' ),
( 'F', 'D', 'L', 'M' ),
( 'X', 'N', 'R', 'S' ),
( 'Y', 'I', 'W', 'Q' ),
( 'U', 'Z', 'E', 'H' )
) AS f(a,b,c,d);
What's a better way to write the above?
Is there anything less convoluted than,
SELECT k FROM (
SELECT array_agg(id) AS x
FROM foo
GROUP BY CUBE (a,b,c,d)
HAVING count(*) > 1
) AS t
CROSS JOIN LATERAL unnest(x) AS k;