Re: UNNEST with multiple args, and TABLE with multiple funcs

Andrew Gierth <andrew(at)tao11(dot)riddles(dot)org(dot)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.

I do like the basic concept of this syntax, but I think it's a serious
error to appropriate the TABLE() spelling for something that doesn't
agree with the spec's semantics for that spelling. We need to spell it
some other way.

I've not experimented to see what's practical in bison, but a couple
of ideas that come to mind are:

1. Use FUNCTION instead of TABLE.

2. Don't use any keyword, just parens. Right now you get a syntax error
from that:

regression=# select * from (foo(), bar()) s;
ERROR: syntax error at or near ","
LINE 1: select * from (foo(), bar()) s;
^

which implies that it's syntax space we could commandeer. On the other
hand, I'm a bit worried about the future-proof-ness of such a choice.
It's uncomfortably close to one of the ways to write a row expression,
so it's not too hard to foresee the SQL committee someday defining
something like this in FROM clauses. It's also hard to see what you'd
call the construct in documentation or error messages --- no keyword means
no easy name to apply.

Thoughts, other ideas?

regards, tom lane