Thread: Re: [PATCHES] targetlist functions part 1 (was targetlist

Tom Lane writes:

> > With sufficiently blurred vision one might even find SQL99's clause
> >          <collection derived table> ::=
> >               UNNEST <left paren> <collection value expression> <right paren>
> > applicable.  Or maybe not.
>
> Hm.  I'm not sure what UNNEST does, but now that you bring SQL99 into
> the picture, what about WITH?  That might solve the problem, because
> (I think) WITH tables are logically determined before the main SELECT
> begins to execute.

The SQL 200x draft defines a new clause TABLE ( <collection value
expression> ) as a possible <table primary>, where the <collection value
expression> is required to be a function call.  At the end this just boils
down to UNNEST, though.  UNNEST is defined in terms of a hairy recursive
join subquery with a LATERAL( ) around it.  LATERAL(subquery) is the same
as just (subquery) except that the scope clauses are different.  So I
think this is probably what we ought to look at.

-- 
Peter Eisentraut   peter_e@gmx.net

I wrote:

> The SQL 200x draft defines a new clause TABLE ( <collection value
> expression> ) as a possible <table primary>, where the <collection value
> expression> is required to be a function call.  At the end this just boils
> down to UNNEST, though.  UNNEST is defined in terms of a hairy recursive
> join subquery with a LATERAL( ) around it.  LATERAL(subquery) is the same
> as just (subquery) except that the scope clauses are different.  So I
> think this is probably what we ought to look at.

I have stared at this some more and it is indeed what we're looking for.
The hairy recursive join is only so that they can get the WITH ORDINALITY
feature (which basically adds a "line number" column to the output) in
there in a closed form.  If you simplify it, the command

SELECT ... FROM TABLE( func(...) ) ...

resolves to

SELECT ... FROM table_generated_by_func ...

As for the question of where nonconstant argument values come from, this
is addressed as well.  The general form of this feature is the lateral
derived table, for example

SELECT ... FROM table1, LATERAL(select ...), table2 ...

as opposed to simply

SELECT ... FROM table1, (select ...), table2 ...

In the second form the subquery cannot access outside values.  In the
first form, the subquery can access range variables in FROM items defined
to its left.

The table function calls are a special case of this, meaning that in

SELECT .. FROM table1, TABLE( func(args) ), table2 ...

the "args" can refer to table1 but not to table2.

Oracle implements exactly this feature.  (See
<http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/statements_103a.htm#2104992>.)
If there are doubts about the semantics we could try it out there.

-- 
Peter Eisentraut   peter_e@gmx.net