Support multi-argument UNNEST(), and TABLE() syntax for multiple functions.

This patch adds the ability to write TABLE( function1(), function2(), ...)
as a single FROM-clause entry.  The result is the concatenation of the
first row from each function, followed by the second row from each
function, etc; with NULLs inserted if any function produces fewer rows than
others.  This is believed to be a much more useful behavior than what
Postgres currently does with multiple SRFs in a SELECT list.

This syntax also provides a reasonable way to combine use of column
definition lists with WITH ORDINALITY: put the column definition list
inside TABLE(), where it's clear that it doesn't control the ordinality
column as well.

Also implement SQL-compliant multiple-argument UNNEST(), by turning
UNNEST(a,b,c) into TABLE(unnest(a), unnest(b), unnest(c)).

The SQL standard specifies TABLE() with only a single function, not
multiple functions, and it seems to require an implicit UNNEST() which is
not what this patch does.  There may be something wrong with that reading
of the spec, though, because if it's right then the spec's TABLE() is just
a pointless alternative spelling of UNNEST().  After further review of
that, we might choose to adopt a different syntax for what this patch does,
but in any case this functionality seems clearly worthwhile.

Andrew Gierth, reviewed by Zoltán Böszörményi and Heikki Linnakangas, and
significantly revised by me

3 files changed, 151 insertions, 39 deletions

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index a5c808effae..a411e3a0cc9 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -11185,6 +11185,21 @@ SELECT NULLIF(value, '(none)') ...
         <entry><literallayout class="monospaced">1
 2</literallayout>(2 rows)</entry>
        </row>
+       <row>
+        <entry>
+         <literal>
+          <function>unnest</function>(<type>anyarray</type>, <type>anyarray</type> [, ...])
+         </literal>
+        </entry>
+        <entry><type>setof anyelement, anyelement [, ...]</type></entry>
+        <entry>expand multiple arrays (possibly of different types) to a set
+         of rows.  This is only allowed in the FROM clause; see
+         <xref linkend="queries-tablefunctions"></entry>
+        <entry><literal>unnest(ARRAY[1,2],ARRAY['foo','bar','baz'])</literal></entry>
+        <entry><literallayout class="monospaced">1    foo
+2    bar
+NULL baz</literallayout>(3 rows)</entry>
+       </row>
       </tbody>
      </tgroup>
     </table>
@@ -13295,6 +13310,8 @@ AND
    functions, as detailed in <xref linkend="functions-srf-series"> and
    <xref linkend="functions-srf-subscripts">.  Other, more specialized
    set-returning functions are described elsewhere in this manual.
+   See <xref linkend="queries-tablefunctions"> for ways to combine multiple
+   set-returning functions.
   </para>
 
   <table id="functions-srf-series">
@@ -13499,14 +13516,11 @@ SELECT * FROM unnest2(ARRAY[[1,2],[3,4]]);
   </indexterm>
 
   <para>
-  When a function in the <literal>FROM</literal> clause is suffixed by
-  <literal>WITH ORDINALITY</literal>, a <type>bigint</type> column is appended
-  to the output which starts from 1 and increments by 1 for each row of the
-  function's output.  This is most useful in the case of set returning functions
-  such as UNNEST(). This functionality is available for functions returning
-  composite types or using <literal>OUT</literal> parameters, but not when using
-  a function returning <literal>RECORD</literal> with an explicit column
-  definition list.
+   When a function in the <literal>FROM</literal> clause is suffixed
+   by <literal>WITH ORDINALITY</literal>, a <type>bigint</type> column is
+   appended to the output which starts from 1 and increments by 1 for each row
+   of the function's output.  This is most useful in the case of set returning
+   functions such as <function>unnest()</>.
 
 <programlisting>
 -- set returning function WITH ORDINALITY
diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml
index c32c8576518..b33de682005 100644
--- a/doc/src/sgml/queries.sgml
+++ b/doc/src/sgml/queries.sgml
@@ -643,21 +643,55 @@ FROM (VALUES ('anne', 'smith'), ('bob', 'jones'), ('joe', 'blow'))
      the <literal>FROM</> clause of a query. Columns returned by table
      functions can be included in <literal>SELECT</>,
      <literal>JOIN</>, or <literal>WHERE</> clauses in the same manner
-     as a table, view, or subquery column.
+     as columns of a table, view, or subquery.
     </para>
 
     <para>
-     If a table function returns a base data type, the single result
-     column name matches the function name. If the function returns a
-     composite type, the result columns get the same names as the
-     individual attributes of the type.
+     Table functions may also be combined using the <literal>TABLE</literal>
+     syntax, with the results returned in parallel columns; the number of
+     result rows in this case is that of the largest function result, with
+     smaller results padded with NULLs to match.
     </para>
 
+<synopsis>
+<replaceable>function_call</replaceable> <optional>WITH ORDINALITY</optional> <optional><optional>AS</optional> <replaceable>table_alias</replaceable> <optional>(<replaceable>column_alias</replaceable> <optional>, ... </optional>)</optional></optional>
+TABLE( <replaceable>function_call</replaceable> <optional>, ... </optional> ) <optional>WITH ORDINALITY</optional> <optional><optional>AS</optional> <replaceable>table_alias</replaceable> <optional>(<replaceable>column_alias</replaceable> <optional>, ... </optional>)</optional></optional>
+</synopsis>
+
+    <para>
+     If the <literal>WITH ORDINALITY</literal> clause is specified, an
+     additional column of type <type>bigint</type> will be added to the
+     function result columns.  This column numbers the rows of the function
+     result set, starting from 1. (This is a generalization of the
+     SQL-standard syntax for <literal>UNNEST ... WITH ORDINALITY</literal>.)
+     By default, the ordinal column is called <literal>ordinality</>, but
+     a different column name can be assigned to it using
+     an <literal>AS</literal> clause.
+    </para>
+
+    <para>
+     The special table function <literal>UNNEST</literal> may be called with
+     any number of array parameters, and it returns a corresponding number of
+     columns, as if <literal>UNNEST</literal>
+     (<xref linkend="functions-array">) had been called on each parameter
+     separately and combined using the <literal>TABLE</literal> construct.
+    </para>
+
+<synopsis>
+UNNEST( <replaceable>array_expression</replaceable> <optional>, ... </optional> ) <optional>WITH ORDINALITY</optional> <optional><optional>AS</optional> <replaceable>table_alias</replaceable> <optional>(<replaceable>column_alias</replaceable> <optional>, ... </optional>)</optional></optional>
+</synopsis>
+
     <para>
-     A table function can be aliased in the <literal>FROM</> clause,
-     but it also can be left unaliased. If a function is used in the
-     <literal>FROM</> clause with no alias, the function name is used
-     as the resulting table name.
+     If no <replaceable>table_alias</replaceable> is specified, the function
+     name is used as the table name; in the case of a <literal>TABLE()</>
+     construct, the first function's name is used.
+    </para>
+
+    <para>
+     If column aliases are not supplied, then for a function returning a base
+     data type, the column name is also the same as the function name.  For a
+     function returning a composite type, the result columns get the names
+     of the individual attributes of the type.
     </para>
 
     <para>
@@ -691,7 +725,30 @@ SELECT * FROM vw_getfoo;
      the pseudotype <type>record</>.  When such a function is used in
      a query, the expected row structure must be specified in the
      query itself, so that the system can know how to parse and plan
-     the query.  Consider this example:
+     the query.  This syntax looks like:
+    </para>
+
+<synopsis>
+<replaceable>function_call</replaceable> <optional>AS</optional> <replaceable>alias</replaceable> (<replaceable>column_definition</replaceable> <optional>, ... </optional>)
+<replaceable>function_call</replaceable> AS <optional><replaceable>alias</replaceable></optional> (<replaceable>column_definition</replaceable> <optional>, ... </optional>)
+TABLE( ... <replaceable>function_call</replaceable> AS (<replaceable>column_definition</replaceable> <optional>, ... </optional>) <optional>, ... </optional> )
+</synopsis>
+
+    <para>
+     When not using the <literal>TABLE()</> syntax,
+     the <replaceable>column_definition</replaceable> list replaces the column
+     alias list that could otherwise be attached to the <literal>FROM</>
+     item; the names in the column definitions serve as column aliases.
+     When using the <literal>TABLE()</> syntax,
+     a <replaceable>column_definition</replaceable> list can be attached to
+     each member function separately; or if there is only one member function
+     and no <literal>WITH ORDINALITY</> clause,
+     a <replaceable>column_definition</replaceable> list can be written in
+     place of a column alias list following <literal>TABLE()</>.
+    </para>
+
+    <para>
+     Consider this example:
 <programlisting>
 SELECT *
     FROM dblink('dbname=mydb', 'SELECT proname, prosrc FROM pg_proc')
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index e603b7644ea..88ebd73d49c 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -52,9 +52,12 @@ SELECT [ ALL | DISTINCT [ ON ( <replaceable class="parameter">expression</replac
     [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ] [ [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ] ]
     [ LATERAL ] ( <replaceable class="parameter">select</replaceable> ) [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ]
     <replaceable class="parameter">with_query_name</replaceable> [ [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ] ]
-    [ LATERAL ] <replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] ) [ WITH ORDINALITY ] [ [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ] ]
+    [ LATERAL ] <replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] )
+                [ WITH ORDINALITY ] [ [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ] ]
     [ LATERAL ] <replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] ) [ AS ] <replaceable class="parameter">alias</replaceable> ( <replaceable class="parameter">column_definition</replaceable> [, ...] )
     [ LATERAL ] <replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] ) AS ( <replaceable class="parameter">column_definition</replaceable> [, ...] )
+    [ LATERAL ] TABLE( <replaceable class="parameter">function_name</replaceable> ( [ <replaceable class="parameter">argument</replaceable> [, ...] ] ) [ AS ( <replaceable class="parameter">column_definition</replaceable> [, ...] ) ] [, ...] )
+                [ WITH ORDINALITY ] [ [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] ) ] ]
     <replaceable class="parameter">from_item</replaceable> [ NATURAL ] <replaceable class="parameter">join_type</replaceable> <replaceable class="parameter">from_item</replaceable> [ ON <replaceable class="parameter">join_condition</replaceable> | USING ( <replaceable class="parameter">join_column</replaceable> [, ...] ) ]
 
 <phrase>and <replaceable class="parameter">with_query</replaceable> is:</phrase>
@@ -368,30 +371,32 @@ TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
         Function calls can appear in the <literal>FROM</literal>
         clause.  (This is especially useful for functions that return
         result sets, but any function can be used.)  This acts as
-        though its output were created as a temporary table for the
+        though the function's output were created as a temporary table for the
         duration of this single <command>SELECT</command> command.
-        When the optional <command>WITH ORDINALITY</command> is
-        appended to the function call, a new column is appended after
-        all the function call's columns with numbering for each row.
-        For example:
-<programlisting>
-SELECT * FROM unnest(ARRAY['a','b','c','d','e','f']) WITH ORDINALITY;
- unnest | ordinality
---------+----------
- a      |        1
- b      |        2
- c      |        3
- d      |        4
- e      |        5
- f      |        6
-(6 rows)
-</programlisting>
-        An alias can also be used. If an alias is written, a column
+        When the optional <command>WITH ORDINALITY</command> clause is
+        added to the function call, a new column is appended after
+        all the function's output columns with numbering for each row.
+       </para>
+
+       <para>
+        An alias can be provided in the same way as for a table.
+        If an alias is written, a column
         alias list can also be written to provide substitute names for
         one or more attributes of the function's composite return
         type, including the column added by <literal>ORDINALITY</literal>
         if present.
-      </para>
+       </para>
+
+       <para>
+        Multiple function calls can be combined into a
+        single <literal>FROM</>-clause item by surrounding them
+        with <literal>TABLE( ... )</>.  The output of such an item is the
+        concatenation of the first row from each function, then the second
+        row from each function, etc.  If some of the functions produce fewer
+        rows than others, NULLs are substituted for the missing data, so
+        that the total number of rows returned is always the same as for the
+        function that produced the most rows.
+       </para>
 
       <para>
         If the function has been defined as returning the
@@ -402,7 +407,21 @@ SELECT * FROM unnest(ARRAY['a','b','c','d','e','f']) WITH ORDINALITY;
         class="parameter">data_type</replaceable> <optional>, ...
         </>)</literal>.  The column definition list must match the
         actual number and types of columns returned by the function.
-        <literal>ORDINALITY</literal> does not work in this case.
+       </para>
+
+       <para>
+        When using the <literal>TABLE( ... )</> syntax, if one of the
+        functions requires a column definition list, it's preferred to put
+        the column definition list after the function call inside
+        <literal>TABLE( ... )</>.  A column definition list can be placed
+        after the <literal>TABLE( ... )</> construct only if there's just a
+        single function and no <literal>WITH ORDINALITY</> clause.
+       </para>
+
+       <para>
+        To use <literal>ORDINALITY</literal> together with a column definition
+        list, you must use the <literal>TABLE( ... )</> syntax and put the
+        column definition list inside <literal>TABLE( ... )</>.
        </para>
       </listitem>
      </varlistentry>
@@ -1599,6 +1618,23 @@ SELECT * FROM distributors_2(111) AS (f1 int, f2 text);
   </para>
 
   <para>
+   Here is an example of a function with an ordinality column added:
+
+<programlisting>
+SELECT * FROM unnest(ARRAY['a','b','c','d','e','f']) WITH ORDINALITY;
+ unnest | ordinality
+--------+----------
+ a      |        1
+ b      |        2
+ c      |        3
+ d      |        4
+ e      |        5
+ f      |        6
+(6 rows)
+</programlisting>
+  </para>
+
+  <para>
    This example shows how to use a simple <literal>WITH</> clause:
 
 <programlisting>
@@ -1773,6 +1809,11 @@ SELECT distributors.* WHERE distributors.name = 'Westward';
     <productname>PostgreSQL</productname> treats <literal>UNNEST()</> the
     same as other set-returning functions.
    </para>
+
+   <para>
+    Placing multiple function calls inside <literal>TABLE( ... )</> syntax is
+    also an extension of the SQL standard.
+   </para>
   </refsect2>
 
   <refsect2>