db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Daniel John Debrunner <...@debrunners.com>
Subject Re: [jira] Commented: (DERBY-716) Re-enable VTIs
Date Fri, 18 Nov 2005 18:45:55 GMT
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.



Mime
View raw message