How Do Multiple Set-Returning Functions Behave in PostgreSQL's SELECT Clause Across Different Versions?

PostgreSQL's Handling of Multiple Set-Returning Functions in SELECT Statements: A Version-Specific Analysis

The behavior of PostgreSQL when encountering multiple set-returning functions within a SELECT statement varies significantly depending on the database version.

PostgreSQL 10 and Later:

In versions 10 and above, these functions execute synchronously. The output is structured such that the function producing the most rows dictates the total number of rows in the result set. Shorter function outputs are padded with NULL values to maintain this alignment, effectively creating a cross-join-like result.

PostgreSQL 9.6 and Earlier:

Prior to version 9.6, the outcome differs substantially. The total number of rows generated equals the least common multiple (LCM) of the row counts from each set-returning function. This can lead to unpredictable and potentially undesirable results, particularly when the row counts share no common factors.

Illustrative Example:

Let's examine the query:

<code class="language-sql">SELECT generate_series(1, 3), generate_series(5, 7);</code>

PostgreSQL 10 Output:

<code> generate_series | generate_series 
-----------------+-----------------
               1 |               5
               2 |               6
               3 |               7
               NULL |               NULL
               NULL |               NULL
               NULL |               NULL</code>

PostgreSQL 9.6- Output:

<code> generate_series | generate_series 
-----------------+-----------------
               1 |               5
               2 |               6
               3 |               7
               1 |               5
               2 |               6
               3 |               7
               1 |               5
               2 |               6
               3 |               7</code>

Key Considerations:

• PostgreSQL 10 and later prohibit the use of set-returning functions inside CASE and COALESCE expressions.
• For improved control and predictable results, employing LATERAL JOIN with ROWS FROM (...) is strongly recommended instead of directly including multiple set-returning functions in the SELECT list.

Relevant Documentation:

• PostgreSQL 10 Release Notes
• PostgreSQL 9.6 SQL Functions Returning Sets

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