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

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(at)gmx(dot)net