Returning a Varying Column Table External UDF Result - Teradata Database

Returning a Varying Column Table External UDF Result

The rules and restrictions are:

You can only include table functions in the FROM clause. See “FROM Clause” on page 80.

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.

IF you specify …	THEN the system uses the columns …
an outcome column name list	specified in the list as the function output columns.
the 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 SQL Data Definition Language.

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 14: Dynamic Row Results Returned by Specifying Table Functions” on page 28.

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