[DERBY-6118] Allow Table Functions to return dynamic table shapes

• Type: Improvement
• Status: Open
• Priority: Major
• Resolution: Unresolved
• Affects Version/s: 10.8.3.0
• Component/s: SQL
• Labels: derby_triage10_11
• Urgency: Normal
• Issue & fix info: High Value Fix
• Assignee: Unassigned
• Reporter: David Vyvyan
• Created: 21/Mar/13 16:38
• Updated: 25/Jun/13 04:08

Description

This feature request comes from this forum discussion:
http://apache-database.10148.n7.nabble.com/Limitations-of-Table-Functions-vs-old-VTIs-td127988.html#a127995

Currently, Table Functions have to be created with a static table shape, e.g:
CREATE FUNCTION TF_TEST1(ARG1 INT) RETURNS TABLE(C1 INT, C2 VARCHAR(32672)) PARAMETER STYLE DERBY_JDBC_RESULT_SET LANGUAGE JAVA NOT DETERMINISTIC READS SQL DATA EXTERNAL NAME 'core.TestTableFunctions.TF_TEST1'

The old VTIs offer dynamic table shapes: Derby resolves the table shape of a VTI using method getMetaData() when compiling a new query that references one.

Stored procedures also offer dynamic table shapes: This is specified at creation time using the syntax: "DYNAMIC RESULT SETS".

It would be very useful if Table Functions could offer the same, e.g:
CREATE FUNCTION TF_TEST1(ARG1 INT) RETURNS DYNAMIC RESULT SET PARAMETER STYLE DERBY_JDBC_RESULT_SET LANGUAGE JAVA NOT DETERMINISTIC READS SQL DATA EXTERNAL NAME 'core.TestTableFunctions.TF_TEST1'

Otherwise, they will always remain behind VTIs in capability.