How Can I Efficiently Call a Set-Returning Function with an Array Argument Multiple Times in PostgreSQL?

Calling a Set-Returning Function with an Array Argument Multiple Times

This discussion centers around a set-returning function named foo that can process an array of data utilizing a specific parameter and provides a result containing a set of rows and an additional column. While the function operates successfully with a single data set, it encounters challenges when attempting to process multiple data sets without relying on a data ID for referencing.

Various attempts to modify the syntax, such as utilizing the following format:

SELECT dataid, (foo(ARRAY_AGG(data)),1).*
FROM dataset
WHERE dataid = something -- only testing on 1
GROUP BY dataid

have been unsuccessful, resulting in the function being called repeatedly for each column.

Solution: Lateral Join

In PostgreSQL versions 9.3 and beyond, using a LEFT JOIN LATERAL construct typically yields optimal results:

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;

This syntax ensures that all rows to the left of the join are preserved, even if the function foo returns no rows.

If foo can indeed return no rows and excluding those rows is desired, the following syntax can be employed:

CROSS JOIN LATERAL foo(sub.arr)

or its shorthand version:

, foo(sub.arr)

This approach is documented in the PostgreSQL manual.

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