Call a set-returning function with an array argument multiple times

by

In Postgres 9.3 or later, it’s typically best to use LEFT JOIN LATERAL ... ON true:

SELECT sub.dataid, f.*
FROM  (
   SELECT dataid, array_agg(data) AS arr
   FROM   dataset
   WHERE  dataid = something
   GROUP  BY 1
   ) sub
LEFT   JOIN LATERAL foo(sub.arr) f ON true;

If the function foo() can return no rows, that’s the safe form as it preserves all rows to the left of the join, even when no row is returned to the right.

Else, or if you want to exclude rows without result from the lateral join, use:

CROSS JOIN LATERAL foo(sub.arr)

or the shorthand:

, foo(sub.arr)

There is an explicit mention in the manual.

Craig’s related answer (referenced by Daniel) is updated accordingly:

More Related Contents:

  1. Record returned from function has columns concatenated
  2. PostgreSQL unnest() with element number
  3. Refactor a PL/pgSQL function to return the output of various SELECT queries
  4. Is SELECT or INSERT in a function prone to race conditions?
  5. How to return result of a SELECT inside a function in PostgreSQL?
  6. What is the expected behaviour for multiple set-returning functions in SELECT clause?
  7. Pass multiple values in single parameter
  8. GROUP BY and aggregate sequential numeric values
  9. Split column into multiple rows in Postgres
  10. PL/pgSQL checking if a row exists
  11. PostgreSQL parameterized Order By / Limit in table function
  12. Return multiple fields as a record in PostgreSQL with PL/pgSQL
  13. PostgreSQL IF statement
  14. Split given string and prepare case statement
  15. Postgres Dynamic Query Function
  16. Explain JOIN vs. LEFT JOIN and WHERE condition performance suggestion in more detail
  17. Return setof record (virtual table) from function
  18. Window Functions or Common Table Expressions: count previous rows within range
  19. PostgreSQL JOIN with array type with array elements order, how to implement?
  20. DROP FUNCTION without knowing the number/type of parameters?
  21. Postgres function returning table not returning data in columns
  22. What is the difference between a LATERAL JOIN and a subquery in PostgreSQL?
  23. Return multiple columns and rows from a function PostgreSQL instead of record
  24. How to return two columns with function
  25. COPY with dynamic file name
  26. pseudo_encrypt() function in plpgsql that takes bigint
  27. Parallel unnest() and sort order in PostgreSQL
  28. How to perform a select query in a DO block?
  29. Function with SQL query has no destination for result data
  30. Postgres trigger after insert accessing NEW

Leave a Comment