(func()).*
in Older PostgreSQL VersionsIn 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.
Several methods effectively circumvent this problem:
1. Subquery Approach:
SELECT (mf).* FROM ( SELECT my_func(x) AS mf FROM some_table ) sub;
2. OFFSET 0 Technique:
SELECT (mf).* FROM ( SELECT my_func(x) AS mf FROM some_table OFFSET 0 ) sub;
3. CTE (Common Table Expression) Approach:
WITH tmp(mf) AS ( SELECT my_func(x) FROM some_table ) SELECT (mf).* FROM tmp;
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.
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!