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 [EMAIL PROTECTED] ---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
