Returning a Varying Column Table External UDF Result - Teradata Database - Teradata Vantage NewSQL Engine

SQL Data Manipulation Language

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Published
March 2019
Language
English (United States)
Last Update
2019-05-03
dita:mapPath
fbo1512081269404.ditamap
dita:ditavalPath
TD_DBS_16_20_Update1.ditaval
dita:id
B035-1146
lifecycle
previous
Product Category
Teradata Vantage™
The rules and restrictions are:
  • You can only include table functions in the FROM clause. See FROM Clause.

    The system processes table functions like derived tables.

  • The SELECT statement syntax supports the implicit specification of the number of return columns at runtime in its TABLE (function_name RETURNS … table_name) clause.
  • When you reference a table function that does not have a variable number of output columns, the system obtains its explicit output column definitions from DBC.TVFields.

    However, when you reference a table function that outputs a dynamic number of columns, you must specify either an output column list or the name of an existing table as part of the function invocation using the RETURNS … table_name option in the FROM clause of the SELECT request.

SELECT Specification Columns Returned
Outcome column name list Specified in the list as the function output columns.
Name of an existing table From the definition for that table as the function output columns.

In either case, the number of columns specified cannot exceed the defined maximum number of output columns specified by the VARYING COLUMNS maximum_output_columns specification of its CREATE FUNCTION (Table Form) definition.

See “CREATE FUNCTION (Table Form)” in Teradata Vantage™ SQL Data Definition Language Detailed Topics , B035-1184 .
  • To invoke a varying column table function, you can:
    • Specify a list of return column name-data type pairs.
    • Specify the return columns from an existing table.

    For examples of both forms returning the same result, see Example: Dynamic Row Results Returned by Specifying Table Functions.

  • You cannot specify LOB columns using the BLOB AS LOCATOR or CLOB AS LOCATOR forms.