UNNEST with multiple args, and TABLE with multiple funcs - Mailing list pgsql-hackers

Summary:

This patch implements a method for expanding multiple SRFs in parallel
that does not have the surprising LCM behaviour of SRFs-in-select-list.
(Functions returning fewer rows are padded with nulls instead.)

It then uses this method combined with a parse-time hack to implement
the (intended to be) spec-conforming behaviour of UNNEST with multiple
parameters, including flattening of composite results.

The upshot is that given a table like this:

postgres=# select * from t1;
       a       |         b         |                      c
---------------+-------------------+----------------------------------------------
 {11,12,13}    | {wombat}          |
 {5,10}        | {foo,bar}         | {"(123,xyzzy)","(456,plugh)","(789,plover)"}
 {21,31,41,51} | {fred,jim,sheila} | {"(111,xyzzy)","(222,plugh)"}
(3 rows)

(where column "c" is an array of a composite type with 2 cols, "x" and "y")

You can do this:

postgres=# select u.* from t1, unnest(a,b,c) with ordinality as u;
 ?column? | ?column? |  x  |   y    | ordinality
----------+----------+-----+--------+------------
       11 | wombat   |     |        |          1
       12 |          |     |        |          2
       13 |          |     |        |          3
        5 | foo      | 123 | xyzzy  |          1
       10 | bar      | 456 | plugh  |          2
          |          | 789 | plover |          3
       21 | fred     | 111 | xyzzy  |          1
       31 | jim      | 222 | plugh  |          2
       41 | sheila   |     |        |          3
       51 |          |     |        |          4
(10 rows)

Or for an example of general combination of functions:

postgres=# select * from table(generate_series(10,20,5), unnest(array['fred','jim']));
 ?column? | ?column?
----------+----------
       10 | fred
       15 | jim
       20 |
(3 rows)

Implementation Details:

The spec syntax for table function calls, <table function derived table>
in <table reference>, looks like TABLE(func(args...)) AS ...

This patch implements that, plus an extension: it allows multiple
functions, TABLE(func1(...), func2(...), func3(...)) [WITH ORDINALITY]
and defines this as meaning that the functions are to be evaluated in
parallel.

This is implemented by changing RangeFunction, function RTEs, and the
FunctionScan node to take lists of function calls rather than a single
function. The calling convention for SRFs is completely unchanged; each
function returns its own rows (or a tuplestore in materialize mode) just
as before, and FunctionScan combines the results into a single output
tuple (keeping track of which functions are exhausted in order to
correctly fill in nulls on a backwards scan).

Then, a hack in the parser converts unnest(...) appearing as a
func_table (and only there) into a list of unnest() calls, one for each
parameter.  So

   select ... from unnest(a,b,c)

is converted to

   select ... from TABLE(unnest(a),unnest(b),unnest(c))

and if unnest appears as part of an existing list inside TABLE(), it's
expanded to multiple entries there too.

This parser hackery is of course somewhat ugly. But given the objective
of implementing the spec's unnest syntax, it seems to be the least ugly
of the possible approaches. (The hard part of doing it any other way
would be generating the description of the result type; composite array
parameters expand into multiple result columns.)

Overall, it's my intention here to remove as many as feasible of the old
reasons why one might use an SRF in the select list. This should also
address the points that Josh brought up in discussion of ORDINALITY
regarding use of SRF-in-select to unnest multiple arrays.

(As a side issue, this patch also sets up pathkeys for ordinality along
the lines of a patch I suggested to Greg a while back in response to
his.)

Current patch status:

This is a first working cut: no docs, no tests, not enough comments, the
deparse logic probably needs more work (it deparses correctly but the
formatting may be suboptimal). However all the functionality is believed
to be in place.

--
Andrew (irc:RhodiumToad)