From: Bruce Momjian <bruce@momjian.us>
To: David G. Johnston <david.g.johnston@gmail.com>
Cc: Guyren Howe <guyren@gmail.com>
Cc: Pg Docs <pgsql-docs@lists.postgresql.org>
Subject: Re: Please provide examples of rows from
Date: Sat, 19 Sep 2020 20:39:10 -0400
Message-ID: <20200920003910.GA22746@momjian.us> (raw)
In-Reply-To: <CAKFQuwbBDr22KJRyJQRECO_XU6cyouwz3ocn0Tv4JgG4Gm5hMQ@mail.gmail.com>
References: <158638264419.662.2482095087061084020@wrigleys.postgresql.org>
<20200904014618.GB13932@momjian.us>
<CAKFQuwbBDr22KJRyJQRECO_XU6cyouwz3ocn0Tv4JgG4Gm5hMQ@mail.gmail.com>
On Mon, Sep 14, 2020 at 06:49:22PM -0700, David G. Johnston wrote:
> That documents one of the two variants - and incorporating the column alias
> feature seems worthwhile for the chosen example. I do think this is worth
> adding.
>
> The more complicated one is the second:
>
> ROWS FROM( ... function_call AS (column_definition [, ... ]) [, ... ] )
>
> First, what's with the first set of "..."? It doesn't appear in the reference
> documentation.
>
> I was looking at the "Queries" doc comment a little bit ago and am wondering if
> there is some principle by which we would decide to place any new examples in
> this section versus the examples part of the SELECT reference section?
>
> I would note that part of the confusion probably stems from not defining
> "column definition" in this chapter. It probably could be defined more
> prominently in the SELECT reference as well.
>
> Basically, aliases outside the ROWS FROM, column definitions within, unless
> there is only a single "record" returning function involved (and without
> ORDINALITY) in which case the external aliases can be instead a complete column
> definition.
>
> For the simple solution to the complaint I would thus suggest three examples,
> but added to the SELECT reference, covering those three situations
> (mutli-typed-aliased, multi-record,
> single-rows-from-record-using-outside-columndef), and pointing the user to the
> SELECT reference for said examples. That would be in addition to the one
> example (another multi-typed-aliased) above being added to the queries section.
>
> A more involved patch would need, IMO, some structure to make the queries
> section sufficient but less complex while relegating much of the complexity to
> the reference section. That seems to be doing a better job describing this
> reality presently anyway.
I spent some time on this. First, since ROWS FROM is a Postgres
extension, it is certainly our job to document it clearly. I started
looking at using system tables that return RECORD for the examples, but
found that this did not work, even without ROWS FROM:
test=> \df pg_get_keywords
List of functions
Schema | Name | Result data type | Argument data types | Type
------------+-----------------+------------------+-----------------------------------------------------------------------------------------------+------
pg_catalog | pg_get_keywords | SETOF record | OUT word text, OUT catcode "char", OUT barelabel boolean, OUT catdesc text, OUT baredesc text | func
(1 row)
test=> select * from pg_get_keywords() AS f(word text);
--> ERROR: a column definition list is only allowed for functions returning "record"
LINE 1: select * from pg_get_keywords() AS f(word text);
Oddly, dblink did work:
test=> \df dblink
List of functions
Schema | Name | Result data type | Argument data types | Type
--------+--------+------------------+---------------------+------
public | dblink | SETOF record | text | func
public | dblink | SETOF record | text, boolean | func
public | dblink | SETOF record | text, text | func
public | dblink | SETOF record | text, text, boolean | func
(4 rows)
test=> SELECT *
FROM dblink('dbname=mydb', 'SELECT proname, prosrc FROM pg_proc')
AS t1(proname name, prosrc text);
ERROR: could not establish connection
DETAIL: FATAL: database "mydb" does not exist
Is it because dblink() does not use OUT parameters?
--
Bruce Momjian <bruce@momjian.us> https://momjian.us
EnterpriseDB https://enterprisedb.com
The usefulness of a cup is in its emptiness, Bruce Lee
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-docs@postgresql.org
Cc: bruce@momjian.us, david.g.johnston@gmail.com, guyren@gmail.com, pgsql-docs@lists.postgresql.org
Subject: Re: Please provide examples of rows from
In-Reply-To: <20200920003910.GA22746@momjian.us>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox