postgres.git / summary / log / commit / refs

commit    9e2c53d4c8dfa9cd9f72282509ff1c7c466273b0
Author:   Bruce Momjian <bruce@momjian.us>
Date:     Mon Oct 05 20:27:33 2020 +0000

    doc:  show functions returning record types and use of ROWS FROM
    
    Previously it was unclear exactly how ROWS FROM behaved and how to cast
    the data types of columns returned by FROM functions.  Also document
    that only non-OUT record functions can have their columns cast to data
    types.
    
    Reported-by: guyren@gmail.com
    
    Discussion: https://postgr.es/m/158638264419.662.2482095087061084020@wrigleys.postgresql.org
    
    Backpatch-through: 9.5


 doc/src/sgml/queries.sgml | 30 +++++++++++++++++++++++++++++-
 1 file changed, 29 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml
index 7cb96a2d0e0..08fdf16615f 100644
--- a/doc/src/sgml/queries.sgml
+++ b/doc/src/sgml/queries.sgml
@@ -762,7 +762,8 @@ SELECT * FROM vw_getfoo;
      In some cases it is useful to define table functions that can
      return different column sets depending on how they are invoked.
      To support this, the table function can be declared as returning
-     the pseudo-type <type>record</>.  When such a function is used in
+     the pseudo-type <type>record</> with no <literal>OUT</literal>
+     parameters.  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.  This syntax looks like:
@@ -803,6 +804,33 @@ SELECT *
      that the parser knows, for example, what <literal>*</> should
      expand to.
     </para>
+
+    <para>
+     This example uses <literal>ROWS FROM</literal>:
+<programlisting>
+SELECT *
+FROM ROWS FROM
+    (
+        json_to_recordset('[{"a":40,"b":"foo"},{"a":"100","b":"bar"}]')
+            AS (a INTEGER, b TEXT),
+        generate_series(1, 3)
+    ) AS x (p, q, s)
+ORDER BY p;
+
+  p  |  q  | s
+-----+-----+---
+  40 | foo | 1
+ 100 | bar | 2
+     |     | 3
+</programlisting>
+     It joins two functions into a single <literal>FROM</literal>
+     target.  <function>json_to_recordset()</function> is instructed
+     to return two columns, the first <type>integer</type>
+     and the second <type>text</type>.  The result of
+     <function>generate_series()</function> is used directly.
+     The <literal>ORDER BY</literal> clause sorts the column values
+     as integers.
+    </para>
    </sect3>
 
    <sect3 id="queries-lateral">


[parent: 74dae41e5ac3]