16.20 - Table Functions - ODBC Driver for Teradata

ODBC Driver for Teradata® User Guide

ODBC Driver for Teradata
October 2018
User Guide

A table function is a form of UDF that can only be specified in the FROM clause of a SELECT statement. It is treated as a derived table subquery.

A table function returns one table row at a time for each invocation. The function is written in C or C++ and can only be invoked in the FROM clause of a SELECT statement. The function cannot be invoked from any other place.

Table functions run in parallel on all AMPs, however, the table function developer can determine which AMPs will participate, and which AMPs will not participate in the function.

Table functions are created with a CREATE FUNCTION statement. The dictionary entries are created for the new function type. There is no difference in how that process works. The UDF code is then compiled and linked, and the library distributed as required to all nodes.

Because the function produces a table, a row at a time, it requires the column definitions and their data types.

Like a UDF, if the table function does I/O, the function needs to include the new external security clause that is used to associate a client user with the execution of the table function.

It either associates the function with the INVOKER of the function, or with the DEFINER of the function. This is controlled by the developer of the function.

A table function returns a table a row at a time in a loop to the caller of the function. The function is capable of reading an external file or simply producing the rows of a table based on the input arguments passed to it. It does this by having the user specify the function in place of the FROM clause in an SQL SELECT statement.

The table function essentially creates a derived table from an external source (a native OS file or message queue). It can also produce the rows solely from the input arguments. For example an input argument could be a reference to a CLOB that contains XML text. From that CLOB it could parse the XML text and output a whole set of SQL rows.