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

On Wed, Nov 20, 2013 at 3:07 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> Andrew Gierth <andrew@tao11.riddles.org.uk> wrote:
>> 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.
>
> I went back and looked at the spec, and so far as I can tell, the claim
> that this is spec syntax plus an extension is a falsehood.  What
> I read in SQL:2008 7.6 <table reference> is
>
> <table function derived table> ::=
>   TABLE <left paren> <collection value expression> <right paren>
>
> where <collection value expression> is elsewhere defined to be an
> expression returning an array or multiset value, and then syntax rule 2
> says:
>
> * the <collection value expression> shall be a <routine invocation>
>
> * this construct is equivalent to UNNEST ( <collection value expression> )
>
> So unless I'm misreading it, the spec's idea is that you could write
>
>    SELECT ... FROM TABLE( function_returning_array(...) )
>
> and this would result in producing the array elements as a table column.
> There is nothing in there about a function returning set.  You could argue
> that that leaves us with the freedom to define what the construct does
> for functions returning set --- but as this patch stands, if a function
> doesn't return set but does return an array, the behavior will not be what
> the spec plainly demands.

The original post on this thread includes this example, which mixes
SRFs and arrays by running the array through UNNEST:

select * from table(generate_series(10,20,5), unnest(array['fred','jim']));

But if we think the spec calls for things to be implicitly unnested,
you could still get the same effect by adjusting the query.  You'd
just get rid of the UNNEST from the argument that had it and wrap
ARRAY(SELECT ...) around the other one:

select * from table(array(select generate_series(10,20,5)),
array['fred','jim']);

It's not clear to me whether that's likely to be inefficient in
practical cases, but there's no real loss of functionality.  IOW, I'm
not sure we really need to invent a new syntax here; maybe we can just
implement the spec, assuming your interpretation thereof is correct.

-- 
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise PostgreSQL Company