17.05 - Returning a Varying Column Table External UDF Result - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)
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.