Date: Thu, 24 Sep 2020 14:55:11 -0400
From: Bruce Momjian <bruce@momjian.us>
To: Tom Lane <tgl@sss.pgh.pa.us>
Cc: "David G. Johnston" <david.g.johnston@gmail.com>,
	Guyren Howe <guyren@gmail.com>,
	Pg Docs <pgsql-docs@lists.postgresql.org>
Subject: Re: Please provide examples of rows from
Message-ID: <20200924185511.GC22807@momjian.us>
References: <158638264419.662.2482095087061084020@wrigleys.postgresql.org>
 <20200904014618.GB13932@momjian.us>
 <CAKFQuwbBDr22KJRyJQRECO_XU6cyouwz3ocn0Tv4JgG4Gm5hMQ@mail.gmail.com>
 <20200920003910.GA22746@momjian.us>
 <798909.1600562993@sss.pgh.pa.us>
 <20200920013846.GC22746@momjian.us>
 <835988.1600613834@sss.pgh.pa.us>
 <878265.1600625640@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="2fHTh5uZTiUOsy+g"
Content-Disposition: inline
In-Reply-To: <878265.1600625640@sss.pgh.pa.us>
User-Agent: Mutt/1.10.1 (2018-07-13)
Precedence: bulk


--2fHTh5uZTiUOsy+g
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

On Sun, Sep 20, 2020 at 02:14:00PM -0400, Tom Lane wrote:
> I wrote:
> > You're right that the only suitable core function is going to be
> > json[b]_to_recordset, but I don't see why you can't extend the
> > existing example for that.  Something like
> 
> Meh, I was too hasty and pushed "send" with a broken example.  Better
> 
> =# select * from rows from (json_to_recordset('[{"a":1,"b":"foo"},{"a":"2","b":"bar"}]') as (a int, b text), generate_series(1,3)) as x(p,q,s);
>  p |  q  | s 
> ---+-----+---
>  1 | foo | 1
>  2 | bar | 2
>    |     | 3
> (3 rows)

Yes, this is very helpful.  I was afraid the JSON would overwhelm the
example, but this looks good.  I wrote the attached doc patch which I
think improves this.  I plan to apply it to all supported versions.

-- 
  Bruce Momjian  <bruce@momjian.us>        https://momjian.us
  EnterpriseDB                             https://enterprisedb.com

  The usefulness of a cup is in its emptiness, Bruce Lee


--2fHTh5uZTiUOsy+g
Content-Type: text/x-diff; charset=us-ascii
Content-Disposition: attachment; filename="row.diff"

diff --git a/doc/src/sgml/queries.sgml b/doc/src/sgml/queries.sgml
index 0a643ef597..f6dec5aaf6 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</type>.  When such a function is used in
+     the pseudo-type <type>record</type> 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:
@@ -771,7 +772,7 @@ SELECT * FROM vw_getfoo;
 <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>)
-ROWS FROM( ... <replaceable>function_call</replaceable> AS (<replaceable>column_definition</replaceable> <optional>, ... </optional>) <optional>, ... </optional> )
+ROWS FROM(<replaceable>function_call</replaceable> AS (<replaceable>column_definition</replaceable> <optional>, ... </optional>) <optional>, ... </optional> )
 </synopsis>
 
     <para>
@@ -803,6 +804,31 @@ SELECT *
      that the parser knows, for example, what <literal>*</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.
+     The first column is defined as returning an integer; the second,
+     <type>text</type>.  The third column uses the function's return
+     type directly.  The <literal>ORDER BY</literal> clause sorts its
+     column as an integer.
+    </para>
    </sect3>
 
    <sect3 id="queries-lateral">

--2fHTh5uZTiUOsy+g--