Add SQL Standard WITH ORDINALITY support for UNNEST (and any other SRF)

Author: Andrew Gierth, David Fetter
Reviewers: Dean Rasheed, Jeevan Chalke, Stephen Frost

2 files changed, 78 insertions, 13 deletions

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 528197e4bcc..34c5c2a2d6b 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -13278,7 +13278,7 @@ select $1[i][j]
         generate_subscripts($1,2) g2(j);
 $$ LANGUAGE sql IMMUTABLE;
 CREATE FUNCTION
-postgres=# SELECT * FROM unnest2(ARRAY[[1,2],[3,4]]);
+SELECT * FROM unnest2(ARRAY[[1,2],[3,4]]);
  unnest2 
 ---------
        1
@@ -13289,6 +13289,48 @@ postgres=# SELECT * FROM unnest2(ARRAY[[1,2],[3,4]]);
 </programlisting>
   </para>
 
+  <indexterm>
+   <primary>ordinality</primary>
+  </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.
+
+<programlisting>
+-- set returning function WITH ORDINALITY
+SELECT * FROM pg_ls_dir('.') WITH ORDINALITY AS t(ls,n);
+       ls        | n  
+-----------------+----
+ pg_serial       |  1
+ pg_twophase     |  2
+ postmaster.opts |  3
+ pg_notify       |  4
+ postgresql.conf |  5
+ pg_tblspc       |  6
+ logfile         |  7
+ base            |  8
+ postmaster.pid  |  9
+ pg_ident.conf   | 10
+ global          | 11
+ pg_clog         | 12
+ pg_snapshots    | 13
+ pg_multixact    | 14
+ PG_VERSION      | 15
+ pg_xlog         | 16
+ pg_hba.conf     | 17
+ pg_stat_tmp     | 18
+ pg_subtrans     | 19
+(19 rows)
+</programlisting>
+  </para>
+
  </sect1>
 
  <sect1 id="functions-info">
diff --git a/doc/src/sgml/ref/select.sgml b/doc/src/sgml/ref/select.sgml
index b0cec1421ca..42cfc28a5e5 100644
--- a/doc/src/sgml/ref/select.sgml
+++ b/doc/src/sgml/ref/select.sgml
@@ -52,7 +52,8 @@ 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> [, ...] ] ) [ AS ] <replaceable class="parameter">alias</replaceable> [ ( <replaceable class="parameter">column_alias</replaceable> [, ...] | <replaceable class="parameter">column_definition</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> [, ...] )
     <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> [, ...] ) ]
 
@@ -368,18 +369,40 @@ TABLE [ ONLY ] <replaceable class="parameter">table_name</replaceable> [ * ]
         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
-        duration of this single <command>SELECT</command> command. An
-        alias can also be used. 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. If
-        the function has been defined as returning the <type>record</>
-        data type, then an alias or the key word <literal>AS</> must
-        be present, followed by a column definition list in the form
-        <literal>( <replaceable
+        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
+        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>
+        If the function has been defined as returning the
+        <type>record</> data type, then an alias or the key word
+        <literal>AS</> must be present, followed by a column
+        definition list in the form <literal>( <replaceable
         class="parameter">column_name</replaceable> <replaceable
-        class="parameter">data_type</replaceable> <optional>, ... </>
-        )</literal>.  The column definition list must match the actual
-        number and types of columns returned by the function.
+        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>
       </listitem>
      </varlistentry>