[ 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
