How to Prevent Multiple Function Calls When Using `(func()).*` in SQL Queries?

Avoiding Repeated Function Calls Using (func()).* in SQL Queries

When employing functions returning tables or composite types, the (func(arg)).* syntax can lead to redundant function calls for each output column. This issue arises when calling functions within tables or subqueries, where (func()).* is often the only practical approach.

The Problem

Unexpectedly, (func()).* triggers multiple function executions—a number equal to the output column count. For instance, a function returning four columns might be called eight times instead of the expected two.

Solutions

To rectify this, encapsulate the function call within a subquery:

<code class="language-sql">SELECT (mf).* FROM (
    SELECT my_func(x) AS mf FROM some_table
) sub;</code>

This generally avoids extra function calls and doesn't introduce additional runtime scans. For absolute certainty, consider the OFFSET 0 trick or leveraging PostgreSQL's limitations in CTE optimization:

<code class="language-sql">SELECT (mf).* FROM (
    SELECT my_func(x) AS mf FROM some_table OFFSET 0
) sub;

WITH tmp(mf) AS (
    SELECT my_func(x) FROM some_table
)
SELECT (mf).* FROM tmp;</code>

PostgreSQL 9.3 and later versions offer a more elegant solution using LATERAL:

<code class="language-sql">SELECT mf.*
FROM some_table
LEFT JOIN LATERAL my_func(some_table.x) AS mf ON true;</code>

Root Cause

The parser's expansion of (func()).* into a column list is the underlying cause. The parsed tree reveals that (func(x)).* transforms into:

<code>(my_func(x)).i, (my_func(x)).j, (my_func(x)).k, (my_func(x)).l</code>

This inefficient node cloning, rather than a single function call node replication, results in the repeated calls.

The above is the detailed content of How to Prevent Multiple Function Calls When Using `(func()).*` in SQL Queries?. For more information, please follow other related articles on the PHP Chinese website!