How Can I Avoid Multiple Function Evaluations When Using `(func()).*` in PostgreSQL?

*Avoid using `(func()).` in PostgreSQL to cause repeated function calls**

In PostgreSQL, using the (func()).* syntax to access the results of a function that returns a table or composite type may result in repeated calls to the function for each column. This can impact performance, especially if the function is computationally expensive.

Solution

To avoid this problem, you can wrap the function call in a subquery like this:

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

This ensures that the function is called only once, regardless of how many columns are in the result. Alternatively, in PostgreSQL 9.3 and later, you can use the LATERAL JOIN syntax:

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

Cause of the problem

The repeated call occurs because the parser treats (func()).* as a placeholder for a list of column names. It expands the expression macro into a series of separate columns, resulting in multiple calls to the function.

Demo

To demonstrate the problem and solution, create a function:

<code class="language-sql">CREATE OR REPLACE FUNCTION my_func(integer)
RETURNS TABLE(a integer, b integer, c integer) AS $$
BEGIN
    RAISE NOTICE 'my_func(%)',;
    RETURN QUERY SELECT , , ;
END;
$$ LANGUAGE plpgsql;</code>

and a table containing dummy data:

<code class="language-sql">CREATE TABLE some_table AS SELECT x FROM generate_series(1,10) x;</code>

Compare the results of the following queries:

• Original syntax:

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

• Solution syntax:

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

You'll notice that the original syntax raises multiple notifications, while the solution syntax only raises one notification, demonstrating the effect of avoiding multiple function calls.

The above is the detailed content of How Can I Avoid Multiple Function Evaluations When Using `(func()).*` in PostgreSQL?. For more information, please follow other related articles on the PHP Chinese website!