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

On Tue, Dec 03, 2013 at 02:27:06PM -0500, Tom Lane wrote:
> Noah Misch <noah(at)leadboat(dot)com> writes:
> > On Tue, Dec 03, 2013 at 10:03:32AM -0500, Stephen Frost wrote:
> >> Alright, for my 2c, I like having this syntax include 'TABLE' simply
> >> because it's what folks coming from Oracle might be looking for.
> >> Following from that, to keep it distinct from the spec's notion of
> >> 'TABLE', my preference is 'TABLE FROM'. I don't particularly like
> >> 'TABLE OF', nor do I like the various 'ROWS' suggestions.
>
> > I like having "ROWS" in there somehow, because it denotes the distinction from
> > SQL-standard TABLE(). Suppose we were to implement the SQL-standard TABLE(),
> > essentially just mapping it to UNNEST(). Then we'd have "TABLE (f())" that
> > unpacks the single array returned by f(), and we'd have "TABLE FROM (f())"
> > that unpacks the set of rows returned by f(). The word "FROM" alone does not
> > indicate that difference the way including "ROWS" does.
>
> Hm ... fair point, except that "ROWS" doesn't seem to suggest the right
> thing either, at least not to me. After further thought I've figured
> out what's been grating on me about Noah's suggestions: he suggests that
> we're distinguishing "TABLE [FROM ELEMENTS]" from "TABLE FROM ROWS",
> but this is backwards. What UNNEST() really does is take an array,
> extract the elements, and make a table of those. Similarly, what our
> feature does is take a set (the result of a set-returning function),
> extract the rows, and make a table of those. So what would seem
> appropriate to me is "TABLE [FROM ARRAY]" versus "TABLE FROM SET".

Valid. On the other hand, tables *are* sets, so one could be forgiven for
wondering how an operation called TABLE FROM SET modifies anything. Since
order matters for this operation, I also get some mathematical angst from use
of the word "SET". When we added WITH ORDINALITY, set-returning functions
effectively became sequence-returning functions. (Not that actually using the
word SEQUENCE would be a net clarification.)

I model "ROWS FROM (f0(), f1())" as "cut from the following template,
row-wise, to make a table/set: (f0(), f1())".

> Another issue is that if you are used to the Oracle syntax, in which an
> UNNEST() is presumed, it's not exactly clear that TABLE ROWS, or any other
> phrase including TABLE, *doesn't* also imply an UNNEST. So to me that's
> kind of a strike against Stephen's preference --- I'm thinking we might be
> better off not using the word TABLE.

I could go either way on that.

Two naming proposals, "ROWS FROM" and "TABLE FROM", got an ACK from more than
one person apiece. I move that we settle on "ROWS FROM".

--
Noah Misch
EnterpriseDB http://www.enterprisedb.com