Date: Thu, 24 Sep 2020 15:37:14 -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: <20200924193714.GD22807@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>
 <20200924185511.GC22807@momjian.us>
 <1668963.1600974324@sss.pgh.pa.us>
MIME-Version: 1.0
Content-Type: multipart/mixed; boundary="s/l3CgOIzMHHjg/5"
Content-Disposition: inline
In-Reply-To: <1668963.1600974324@sss.pgh.pa.us>
User-Agent: Mutt/1.10.1 (2018-07-13)
Precedence: bulk


--s/l3CgOIzMHHjg/5
Content-Type: text/plain; charset=us-ascii
Content-Disposition: inline

On Thu, Sep 24, 2020 at 03:05:24PM -0400, Tom Lane wrote:
> Bruce Momjian <bruce@momjian.us> writes:
> > 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.
> 
> Couple thoughts:
> 
> * Taking the initial ... out of the syntax synopsis is not an improvement.
> It makes it look like you can only apply AS to the first function of a
> ROWS FROM.

Oh, so the dots represent optional non-column_definition function calls.
I can't think if a cleaner way to show that, so I guess "..." will have
to do.

> * I think the ORDER BY adds nothing to the example except complication
> and confusion.

I wanted to highlight that the column_definition specifies the data type
of the column in other parts of the query.

> * Maybe the other sentence of explanation would read better as
> 
> 	json_to_recordset() is instructed to return two columns,
> 	the first integer and the second text.  The result of
> 	generate_series() is used directly.

OK, better.  New patch attached.

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

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


--s/l3CgOIzMHHjg/5
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..3bb5cb89bd 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:
@@ -803,6 +804,33 @@ 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.  <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">

--s/l3CgOIzMHHjg/5--