Home > Database > Mysql Tutorial > How to Prevent Multiple Function Calls with `(func()).*` in Older PostgreSQL Versions?

How to Prevent Multiple Function Calls with `(func()).*` in Older PostgreSQL Versions?

DDD
Release: 2025-01-10 10:57:42
Original
461 people have browsed it

How to Prevent Multiple Function Calls with `(func()).*` in Older PostgreSQL Versions?

Avoiding Repeated Function Calls with (func()).* in Older PostgreSQL Versions

The Issue

In PostgreSQL versions prior to 9.3, using the (func()).* syntax (where func returns a table) can lead to unintended multiple executions of the function. This significantly impacts query performance.

Solutions

Several methods effectively circumvent this problem:

1. Subquery Approach:

SELECT (mf).* FROM (
    SELECT my_func(x) AS mf FROM some_table
) sub;
Copy after login

2. OFFSET 0 Technique:

SELECT (mf).* FROM (
    SELECT my_func(x) AS mf FROM some_table OFFSET 0
) sub;
Copy after login

3. CTE (Common Table Expression) Approach:

WITH tmp(mf) AS (
    SELECT my_func(x) FROM some_table
)
SELECT (mf).* FROM tmp;
Copy after login

4. LATERAL JOIN (PostgreSQL 9.3 and later):

SELECT mf.*
FROM some_table
LEFT JOIN LATERAL my_func(some_table.x) AS mf ON true;
```  This is the preferred method for PostgreSQL 9.3 and above.


### Explanation

The root cause lies in how PostgreSQL's parser handles `(func()).*` in older versions.  The wildcard expands into individual column selections, mistakenly causing the function to be called repeatedly for each column.


### Why Repeated Calls Occur

Pre-9.3 PostgreSQL parsers interpret `(func()).*` by replicating nodes within the parse tree.  This replication results in a separate function call for every selected column, even if a single call would suffice.
Copy after login

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

Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template