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

David Johnston <polobo(at)yahoo(dot)com> writes:
> Tom Lane-2 wrote
>> We could conceivably say that we'll implicitly UNNEST() if the function
>> returns array, and not otherwise --- but that seems pretty inconsistent
>> and surprise-making to me.

> The use-cases for putting a scalar array returning function call into a
> TABLE construct, and NOT wanting the array to be un-nested, are likely few
> and far between.

> Neither the inconsistency nor surprise-making are serious deal-breakers for
> me.

Well, they are for me ;-). I'm concerned for example about how we get
ruleutils.c to reverse-list into a form that's certain to be interpreted
the same by the parser.

The whole business with the spec's reading of TABLE() seems bizarre.
AFAICS there is nothing about TABLE(foo()) that you can't get with
greater clarity by writing UNNEST(foo()) instead. And it's not like
it's a legacy feature --- SQL99 has single-argument UNNEST() but not
TABLE(), so why'd they add TABLE() later, and why'd they make it a
strict subset of what UNNEST() can do? I can't escape the suspicion
that I'm misreading the spec somehow ... but the text seems perfectly
clear.

Anyway, after further thought I've come up with an approach that's purely
a syntactic transformation and so less likely to cause surprise: let's
say that if we have TABLE() with a single argument, and no coldeflist
either inside or outside, then we implicitly insert UNNEST(). Otherwise
not. This is sufficient to satisfy the case spelled out in the standard,
but it doesn't get in the way of any more-typical use of TABLE().
In particular, if you don't want the implicit UNNEST(), you can just leave
off TABLE(), because the case where we'd insert it has no features you
can't get in the old syntax. Similarly, because ruleutils.c is already
coded not to bother with printing TABLE() if there's a single function and
no coldeflist, we needn't worry about falling foul of the implicit action
when a printed view is re-parsed.

BTW, I looked into the option of choosing a different syntax altogether,
but the results weren't too promising. FUNCTION() doesn't work unless
we're willing to make that keyword partially reserved, which seems like
a bad thing. (TABLE() works because TABLE is already a fully reserved
word.) The idea with no keyword at all might work, but it seems way too
likely to cause confusion, especially if you think about parenthesized
JOIN syntax as an alternative possibility for some slightly-typoed query.

Thoughts?

regards, tom lane