16.20 - Definition of a Table Function - Teradata Database - Teradata Vantage NewSQL Engine

Teradata Vantage™ SQL Data Definition Language Detailed Topics

Product
Teradata Database
Teradata Vantage NewSQL Engine
Release Number
16.20
Release Date
March 2019
Content Type
Programming Reference
Publication ID
B035-1184-162K
Language
English (United States)
A table function is a user-defined function that returns a multirow relational table, one row at a time to the SELECT request that calls it. You define the structure of the table to be returned in one of two ways:
  • If you know the number of columns that will be returned before you run the function, then you define the return table structure similarly to the way you define the structure of a persistent table using the CREATE TABLE statement, by specifying its component column names and their respective data types. See CREATE TABLE and Teradata Vantage™ SQL Data Definition Language Syntax and Examples, B035-1144.

    The complete set of column names and their accompanying data types defines the structure of the table the function returns to the caller.

    If one of the specified columns has a LOB data type, then you must define at least one other non-LOB column.

    Each column name is limited to a maximum of 128 UNICODE characters with the same restrictions as other object identifiers. For information on object naming, see Teradata Vantage™ SQL Fundamentals, B035-1141.

  • If you do not know the number of columns that will be returned before you run the function, then you define the return table structure using the TABLE VARYING COLUMNS clause and specify a maximum acceptable number of output columns to be returned. The system stops building return table columns if the specified limit is reached.

The maximum number of columns you can specify is 2,048 for the static and dynamic forms of table function.

The returned table is derived from an external source, typically a native operating system file, message queue, or input argument such as a LOB, and is semantically similar to a derived table (see Teradata Vantage™ SQL Data Manipulation Language , B035-1146 ).

Table functions are different from non-table UDFs because they return an entire table to the calling statement, while a non-table UDF returns only a single scalar result.