[jira] Commented: (DERBY-716) Re-enable VTIs

["Rick Hillegas (JIRA)" <de...@db.apache.org>]

    [ 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 ... ) )

This differs from the Cloudscape approach, which was to declare the VTI on the fly at query time using a constructor. The ANSI approach seems simple, powerful, and elegant enough. Here's a sketch of what we could do:

1) Enhance the CREATE FUNCTION syntax to support the RETURNS TABLE clause.
2) Enhance query specifications to allow TABLE( functionName( args ... )  ) in the FROM list
3) Expose template ResultSet and ResultSetMetaData implementations which customers can extend. We currently have versions of these in org.apache.derby.vti.VTITemplate.
4) Raise a query-execution-time exception if a) the java function does not return a ResultSet, or b) the VTI's ResultSetMetaData does not match the signature declared by CREATE FUNCTION.



> Re-enable VTIs
> --------------
>
>          Key: DERBY-716
>          URL: http://issues.apache.org/jira/browse/DERBY-716
>      Project: Derby
>         Type: New Feature
>     Reporter: Rick Hillegas

>
> Cloudscape used to expose Virtual Table Interfaces, by which any class which implemented ResultSet could be included in a query's FROM list. Derby still exposes a number of these VTIs as diagnostic tools. However, Derby now prevents customers from declaring their own VTIs. The parser raises an error if a VTI's package isn't one of the Derby diagnostic packages.
> This is a very powerful feature which customers can use to solve many problems. We should discuss the reasons that it was disabled and come up with a plan for putting this power back into our customers' hands.

-- 
This message is automatically generated by JIRA.
-
If you think it was sent incorrectly contact one of the administrators:
   http://issues.apache.org/jira/secure/Administrators.jspa
-
For more information on JIRA, see:
   http://www.atlassian.com/software/jira

Re: [jira] Commented: (DERBY-716) Re-enable VTIs

[Rick Hillegas <Ri...@Sun.COM>]

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.
>
>
>  
>

Re: [jira] Commented: (DERBY-716) Re-enable VTIs

[Daniel John Debrunner <dj...@debrunners.com>]

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.