I agree that your syntax looks cleaner. ANSI seems to require the TABLE
keyword and parentheses, and that's what DB2 implements. Microsoft
allows your syntax.
Cheers,
-Rick
Daniel John Debrunner wrote:
Rick Hillegas (JIRA) wrote:
[ http://issues.apache.org/jira/browse/DERBY-716?page=comments#action_12357981 ]
Rick Hillegas commented on DERBY-716:
-------------------------------------
This feature is supported by other databases, including DB2 (see
http://publib.boulder.ibm.com/infocenter/db2help/index.jsp?topic=/com.ibm.db2.udb.doc/ad/c0011177.htm)
and Microsoft SQL Server (see
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_7r1l.asp).
These databases largely hew to the ANSI 2003 solution:
1) Declare a function which returns a table
2) Invoke the function in your query's FROM list
The ANSI syntax for declaring VTI-returning functions is defined in Part 2, section
11.50, under the <returns table type> production. It allows you to specify a
table signature in the RETURNS clause of the function declaration:
CREATE FUNCTION functionName ...
RETURNS TABLE( [ [ columnName, columnDatatype ] [, columnName columnDatatype ]*
] )
The ANSI syntax for querying a VTI is defined in Part 2, section 7.6 under the
<table function derived table> production. It allows a FROM list element to be
a function invocation wrapped by a TABLE constructor:
SELECT *
FROM TABLE( functionName( args ... ) )
Is the table operator required when the function is declared as
returning a TABLE?
I was hoping would would be able to do
SELECT * FROM functionName( args ... )
Dan.
