sqlpostgresqltable-functions

Why does this `select` work in PostgreSQL?


select unnest(array[1, 2, 3]), unnest(array[4, 5, 6])

It produces the following

unnest unnest
1 4
2 5
3 6

The docs on table functions indicate that the proper syntax should be

select * from unnest(array[1, 2, 3], array[4, 5, 6])

Is this one of those cases where PostgreSQL is being forgiving, or is there an actual mechanism that's being exploited here?


Solution

  • The doc you linked points to 9.19. Array Functions and Operators where you can find these are two slightly different unnest() functions:

    The special table function UNNEST may be called with any number of array parameters, and it returns a corresponding number of columns, as if UNNEST (Section 9.19) had been called on each parameter separately and combined using the ROWS FROM construct.

    And the description of the variadic variant mentions it's only allowed down in the FROM list - you wouldn't be able to move it up to make this an apples-to-apples comparison:

    unnest ( anyarray, anyarray [, ... ] ) → setof anyelement, anyelement [, ... ]
    Expands multiple arrays (possibly of different data types) into a set of rows. If the arrays are not all the same length then the shorter ones are padded with NULLs. This form is only allowed in a query's FROM clause; see Section 7.2.1.4.

    select * from unnest(ARRAY[1,2], ARRAY['foo','bar','baz']) as x(a,b) →
    
    a |  b
    ---+-----
    1 | foo
    2 | bar
      | baz 
    

    Note that even if you set the variadic and non-variadic unnest() to do the same thing, they won't necessarily perform exactly the same.

    The rules on SRF evaluation in the SELECT list can be found in 36.5.9. SQL Functions Returning Sets:

    Functions returning sets can also be called in the select list of a query. For each row that the query generates by itself, the set-returning function is invoked, and an output row is generated for each element of the function's result set.
    ...
    PostgreSQL's behavior for a set-returning function in a query's select list is almost exactly the same as if the set-returning function had been written in a LATERAL FROM-clause item instead.
    ...
    Set-returning functions in the select list are always evaluated as though they are on the inside of a nested-loop join with the rest of the FROM clause, so that the function(s) are run to completion before the next row from the FROM clause is considered.

    If there is more than one set-returning function in the query's select list, the behavior is similar to what you get from putting the functions into a single LATERAL ROWS FROM( ... ) FROM-clause item. For each row from the underlying query, there is an output row using the first result from each function, then an output row using the second result, and so on. If some of the set-returning functions produce fewer outputs than others, null values are substituted for the missing data, so that the total number of rows emitted for one underlying row is the same as for the set-returning function that produced the most outputs. Thus the set-returning functions run “in lockstep” until they are all exhausted, and then execution continues with the next underlying row.

    Set-returning functions can be nested in a select list, although that is not allowed in FROM-clause items.