What is the expected behaviour for multiple set-returning functions in SELECT clause?

by

Postgres 10 or newer

adds null values for smaller set(s). Demo with generate_series():

SELECT generate_series( 1,  2) AS row2
     , generate_series(11, 13) AS row3
     , generate_series(21, 24) AS row4;

row2 | row3 | row4
-----+------+-----
   1 |   11 |   21
   2 |   12 |   22
null |   13 |   23
null | null |   24

dbfiddle here

The manual for Postgres 10:

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.

This ends the traditionally odd behavior.

Postgres 9.6 or older

The number of result rows (somewhat surprisingly!) is the lowest common multiple of all sets in the same SELECT list. (Only acts like a CROSS JOIN if there is no common divisor to all set-sizes!) Demo:

SELECT generate_series( 1,  2) AS row2
     , generate_series(11, 13) AS row3
     , generate_series(21, 24) AS row4;

row2 | row3 | row4
-----+------+-----
   1 |   11 |   21
   2 |   12 |   22
   1 |   13 |   23
   2 |   11 |   24
   1 |   12 |   21
   2 |   13 |   22
   1 |   11 |   23
   2 |   12 |   24
   1 |   13 |   21
   2 |   11 |   22
   1 |   12 |   23
   2 |   13 |   24

dbfiddle here

Documented in manual for Postgres 9.6 the chapter SQL Functions Returning Sets, along with the recommendation to avoid it:

Note: The key problem with using set-returning functions in the select
list, rather than the FROM clause, is that putting more than one
set-returning function in the same select list does not behave very
sensibly. (What you actually get if you do so is a number of output
rows equal to the least common multiple of the numbers of rows
produced by each set-returning function.) The LATERAL syntax produces
less surprising results when calling multiple set-returning functions,
and should usually be used instead.

Bold emphasis mine.

A single set-returning function is OK (but still cleaner in the FROM list), but multiple in the same SELECT list is discouraged now. This was a useful feature before we had LATERAL joins. Now it’s merely historical ballast.

Related:

More Related Contents:

  1. PostgreSQL unnest() with element number
  2. Split column into multiple rows in Postgres
  3. Best way to count rows by arbitrary time intervals
  4. Call a set-returning function with an array argument multiple times
  5. Split given string and prepare case statement
  6. What’s the proper index for querying structures in arrays in Postgres jsonb?
  7. Record returned from function has columns concatenated
  8. Calculate working hours between 2 dates in PostgreSQL
  9. How to return two columns with function
  10. Parallel unnest() and sort order in PostgreSQL
  11. How to perform a select query in a DO block?
  12. Join a count query on generate_series() and retrieve Null values as ‘0’
  13. Select first row in each GROUP BY group?
  14. How to concatenate strings of a string field in a PostgreSQL ‘group by’ query?
  15. How to use RETURNING with ON CONFLICT in PostgreSQL?
  16. PostgreSQL DISTINCT ON with different ORDER BY
  17. Split comma separated column data into additional columns
  18. How to delete duplicate entries?
  19. Transpose latest rows per user to columns
  20. delete “column does not exist”
  21. How to create a temporary function in PostgreSQL?
  22. Polymorphism in SQL database tables?
  23. How to round an average to 2 decimal places in PostgreSQL?
  24. Explain JOIN vs. LEFT JOIN and WHERE condition performance suggestion in more detail
  25. INSERT COMMAND :: ERROR: column “value” does not exist
  26. Return multiple columns and rows from a function PostgreSQL instead of record
  27. Postgres error updating column data
  28. SQL LIKE condition to check for integer?
  29. PostgreSQL nested INSERTs / WITHs for foreign key insertions
  30. Fetch records that are non zero after the decimal point in PostgreSQL

Leave a Comment