db-derby-dev mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Rick Hillegas <Richard.Hille...@Sun.COM>
Subject Re: [jira] Commented: (DERBY-716) Re-enable VTIs
Date Fri, 18 Nov 2005 19:27:27 GMT
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.
>
>
>  
>


Mime
View raw message