How to Call a Set-Returning Function Multiple Times with Array Arguments in PostgreSQL?

Calling a Set-Returning Function with Array Arguments Multiple Times

You have a function that processes an array of rows with a given parameter and returns a set of rows with an additional column.

Question: How can you call this function multiple times on different groups of data without passing a dataid to the function?

Answer:

In Postgres 9.3 or later, use the LEFT JOIN LATERAL ... ON true syntax:

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 foo() function can return no rows, this form will preserve all rows to the left of the join, even when no row is returned to the right.

If the function cannot return no rows, or if you want to exclude rows without results from the lateral join, use:

CROSS JOIN LATERAL foo(sub.arr)

Alternatively, you can use the shorthand:

, foo(sub.arr)

This method is recommended for Postgres 9.3 and later. For earlier versions, refer to other available solutions.

The above is the detailed content of How to Call a Set-Returning Function Multiple Times with Array Arguments in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!