Using Clauses with Table UDFs - Teradata Vantage

RETURNS TABLE Clause

The basic RETURNS TABLE clause for a table UDF definition is a basic definition for the table to be returned and consists only of a list of the columns and their respective data types.

The dynamic results row specification RETURNS TABLE clause for a table UDF definition provides a method for dynamically defining the number of columns returned by the function when that number cannot be known before you invoke the function. The only parameter you specify for this clause is the maximum number of output columns. The system defines the column names and data types at run time.

Function of Parameter Style Clause in CREATE FUNCTION (Table Form)

See "Parameter Style Clause" in Using Clauses: CREATE FUNCTION and REPLACE FUNCTION (External Form).

The only valid parameter style for table functions is SQL.

Null Call Clause in CREATE TABLE (Function Form)

You can pass a null as an input argument to a table function or return a null in a result row of a table function only when the data type of the corresponding parameter to its Java method is object-mapped (see Data Type Mapping between SQL and Java: CREATE PROCEDURE and REPLACE PROCEDURE (External Form)).

If the data type of the corresponding parameter to the Java method of the table function is defined as simple-mapped, then attempts to evaluate a null at runtime fail and return an error to the requestor. This is because simple-mapped data types cannot represent nulls.

See "Null Call Clause" in Using Clauses: CREATE FUNCTION and REPLACE FUNCTION (External Form) for general information about the Null Call clause. The only form of null call supported by table functions is CALLED ON NULL INPUT.

CREATE FUNCTION (Table Form): External Body Reference Clause