Re: Please provide examples of rows from

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(at)momjian(dot)us> https://momjian.us
EnterpriseDB https://enterprisedb.com

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