How Do Multiple Set-Returning Functions Behave in a PostgreSQL SELECT Clause?

PostgreSQL SELECT Clause: Behavior of Multiple Set-Returning Functions

The interaction of multiple set-returning functions (SRFs) within a PostgreSQL SELECT clause is version-dependent. Understanding this behavior is crucial for predictable query results.

PostgreSQL 10 and Later:

In PostgreSQL 10 and subsequent versions, SRFs are processed before scalar expressions in the SELECT list. This is functionally equivalent to using them in a LATERAL FROM clause. The system generates output rows iteratively: first using the initial result from each SRF, then the second result from each, and so on.

If the SRFs return varying row counts, the shorter results are padded with NULL values to match the length of the longest result set. This ensures all output rows have the same number of columns.

PostgreSQL 9.6 and Earlier:

Older versions (9.6 and below) exhibit different behavior. The total number of output rows becomes the least common multiple (LCM) of the row counts generated by each SRF. This can lead to unexpected and potentially unpredictable outcomes, particularly when the SRF result set sizes share no common divisors.

Best Practices:

To avoid ambiguity and ensure consistent results across PostgreSQL versions, it's strongly advised to avoid using multiple SRFs directly in the SELECT list, especially in older PostgreSQL versions. The LATERAL join provides a clearer, more predictable, and cross-version compatible approach for handling multiple SRFs.

Further Reading:

• PostgreSQL 10 Manual: Set-Returning Functions in SELECT List
• PostgreSQL 9.6 Manual: SQL Functions Returning Sets

The above is the detailed content of How Do Multiple Set-Returning Functions Behave in a PostgreSQL SELECT Clause?. For more information, please follow other related articles on the PHP Chinese website!