Re: Please provide examples of rows from - Mailing list pgsql-docs

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
catdesctext, 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