unnest() subquery that returns an array?

Question

I've encountered a rather unintuitive behaviour while unnesting array results returned FROM sub-SELECT.

SELECT unnest(c).* 
FROM (SELECT chat_messages[0 : array_length(chat_messages, 1)] 
      FROM Chats WHERE chat_id = 2) c

This was my original query. Postgres doens't like it:

function unnest(record) does not exist

But this seemingly equivalent query works:

SELECT * 
FROM unnest((SELECT chat_messages[0 : array_length(chat_messages, 1)] 
             FROM Chats WHERE chat_id = 2)) c

This query doesn't work either with the same error message:

SELECT * 
FROM (SELECT chat_messages[0 : array_length(chat_messages, 1)] 
      FROM Chats WHERE chat_id = 2) c,
     unnest(c) u

I'm pretty sure I'm missing something here. Why such behaviour? And how come subquery returns record type when it's defined composite type?

Answer 1

In the first and third queries c is formally a set of rows (of pseudo-type record), so you cannot unnest(c). You should use a value instead (I skipped slice as irrelevant):

Query #1:

SELECT (unnest(val)).* 
FROM (
    SELECT chat_messages
    FROM Chats WHERE chat_id = 2
    ) c(val);

-- or

SELECT (unnest(val)).* 
FROM (
    SELECT chat_messages val
    FROM Chats WHERE chat_id = 2
    ) c;

Query #3:

SELECT * 
FROM (
    SELECT chat_messages
    FROM Chats WHERE chat_id = 2
    ) c(val),
    unnest(val) u;

-- or

SELECT * 
FROM (
    SELECT chat_messages val
    FROM Chats WHERE chat_id = 2
    ) c,
    unnest(val) u;

In query #2 you extract a value by using additional brackets, so the result is not a row but a value (an array in this case). This query will raise an error if the inner query returns more than one row.