Use of Table Functions | CREATE FUNCTION | Teradata Vantage - Definition of a Table Function - Analytics Database - Teradata Vantage

SQL Data Definition Language Detailed Topics

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
2023-07-11
dita:mapPath
vuk1628111288877.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
B035-1184
lifecycle
latest
Product Category
Teradata Vantage™
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.