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

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

In PostgreSQL, you may encounter a situation where you need to invoke a set-returning function multiple times, each time providing an array as an argument. However, when using the traditional method of passing a single array to the function, the function may get called multiple times, resulting in inefficient processing.

To resolve this issue, PostgreSQL 9.3 and later offer the LEFT JOIN LATERAL ... ON true syntax. This approach optimizes the execution by performing the following steps:

1. The LEFT JOIN LATERAL statement joins the data from a subquery with the results of calling the set-returning function, foo(sub.arr).
2. The ON true clause ensures that all rows from the subquery are included in the result, regardless of whether the function returns any rows.

To illustrate this approach, consider the following example:

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;

In this example, the subquery aggregates the data column for each dataid, creating an array. This array is then passed as an argument to the foo() function, which returns a set of enhanced data rows.

Alternatively, if the foo() function can potentially return no rows and you want to preserve all rows to the left of the join, you can use the CROSS JOIN LATERAL or shorthand syntax.

By utilizing this approach, you can efficiently invoke a set-returning function multiple times with array arguments, avoiding unnecessary function calls.

The above is the detailed content of How Can I Efficiently 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!