Use of Table Functions | CREATE FUNCTION | VantageCloud Lake - Use of Table Functions

Definition of a Table Function

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 to 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.
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 Object Names.
If you do not know the number of columns to 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 view (see CREATE VIEW and REPLACE VIEW).

A table function returns an entire table to the calling statement. A nontable UDF returns a single scalar result.

Typical Uses for Table Functions

The most common use for table functions is analyzing nonrelational data or converting nonrelational data to relational data. Possible applications include the following:

Converting flat file data into relational tables.
Converting XML and web data into relational tables.
Analyzing spreadsheet data or converting it into relational tables.

Think of table functions as a facility for creating a view-like mechanism that allows SQL processing facilities to process and analyze numerous types of non-relational data.

General Limitations and Restrictions on Table Functions

These general restrictions apply to table functions:

Table functions cannot run against fallback data when an AMP is down. When the AMP returns to service, the query can complete as normal.
If the UDF library for your database contains any objects with multibyte characters in their name, you cannot use a single-byte session character set to create a new UDF, method, or Java external procedure object even if the new object name contains only single-byte characters. Otherwise, the system returns an error to the requestor. Instead, use a multibyte session character set.
Run in protected mode if a table function accesses data external to Vantage or causes the OS to consume system resources. This includes anything that causes the OS to allocate system context, including open files, pipes, semaphores, tokens, threads (processes), and so on.
Protected mode servers consume disk resources as follows:
A table function can be deadlocked if too few protected mode servers are configured.
The system does not detect or report this deadlock.
You cannot specify more than one table UDF in a FROM clause.

You cannot invoke a table function in a FROM clause join condition using the FROM … JOIN …ON syntax.

Suppose you create the following table and table function.

CREATE TABLE supp_indata (
  a FLOAT,
  b FLOAT;
CREATE FUNCTION c_mytable (a FLOAT, b FLOAT)
RETURNS TABLE (c3 FLOAT, c4 FLOAT )
LANGUAGE C
NO SQL
PARAMETER STYLE SQL
EXTERNAL NAME 'CS!c_mytable!c_mytable.c';

The following SELECT request is invalid because it uses the FROM … JOIN …ON syntax.

SELECT t1.c4
FROM TABLE (c_mytable(supp_indata.a, supp_indata.b)) AS t1
JOIN supp_indata AS t2 ON t1.c3 = t2.b;

However, the following SELECT request is valid.

SELECT t1.c4
FROM TABLE(c_mytable(supp_indata.a, supp_indata.b)) AS t1,
                     supp_indata AS t2
WHERE t1.c3 = t2b;

You cannot create or replace a table function from an embedded SQL application.
A table function cannot reference a recursive view.
The size of any row returned by a table function cannot exceed the system limit.
If a row exceeds this limit, the system ends the request (in Teradata session mode) or its containing transaction (in ANSI session mode), rolls it back, and returns an error message to the requestor.

The system defers the maximum row size check until you invoke the function, because it cannot know whether the limit is exceeded by a table function defined with dynamic result rows until the function runs.
The maximum number of columns that can be returned by any table function is 2,048.
When you run in protected mode, all instances of a table function run as TDATUSER.
You must troubleshoot code problems outside Vantage.
You can do a limited amount of code checking by building the trace facility into the code with the FNC_Trace_Write or FNC_Trace_Write_DL library calls. See CREATE GLOBAL TEMPORARY TRACE TABLE Usage Notes.
You can specify the TD_ANYTYPE data type as an input parameter for a table function.
You cannot specify the TD_ANYTYPE data type as the return type for a table function.
The way you must specify columns having a BLOB or CLOB data type depends on whether you are specifying a data type for a parameter column or for a RETURN TABLE clause column:
Clause LOB Type

Parameter With an AS LOCATOR phrase.

RETURNS TABLE Without an AS LOCATOR phrase.

Use of Table Functions | CREATE FUNCTION | VantageCloud Lake - Use of Table Functions - Teradata Vantage

Teradata® VantageCloud Lake

Definition of a Table Function

Typical Uses for Table Functions

General Limitations and Restrictions on Table Functions

Clause	LOB Type
Parameter	With an AS LOCATOR phrase.
RETURNS TABLE	Without an AS LOCATOR phrase.