Uploaded image for project: 'Derby'
  1. Derby
  2. DERBY-6118

Allow Table Functions to return dynamic table shapes

    XMLWordPrintableJSON

    Details

    • Type: Improvement
    • Status: Open
    • Priority: Major
    • Resolution: Unresolved
    • Affects Version/s: 10.8.3.0
    • Fix Version/s: None
    • Component/s: SQL
    • Urgency:
      Normal
    • Issue & fix info:
      High Value Fix

      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.

        Attachments

          Activity

            People

            • Assignee:
              Unassigned
              Reporter:
              drv David Vyvyan
            • Votes:
              0 Vote for this issue
              Watchers:
              2 Start watching this issue

              Dates

              • Created:
                Updated: