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

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
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.