15.10 - RETURNS TABLE - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

Teradata Database
December 2015
Programming Reference

The function returns a set of rows in a standard relational table.

Name of a column in the set of rows to be returned by the function. You must define at least one column name and its data type for any table function definition.
For information about naming database objects, see SQL Fundamentals.
The maximum number of columns you can specify per table function is 2,048.
If one of the specified columns has a LOB data type, then you must define at least one other non-LOB column.
The complete set of column names and their accompanying data types defines the structure of the table the function returns when it is called.
data type
A data type for each column name you specify. For a list of data types, see Data Types Syntax.
If the type is one of the CHARACTER family, then you can also specify a CHARACTER SET attribute.
You cannot specify a RETURNS clause data type of TD_ANYTYPE.
You cannot specify a character server data set of KANJI1. Otherwise, Teradata Database returns an error to the requestor.
If the data type for a returned column is either BLOB or CLOB, then you cannot specify it with an AS LOCATOR phrase. Such a specification returns an error to the requestor.
This is in direct contrast with the specification of LOB parameter data types, which must be specified with an AS LOCATOR phrase.
You cannot specify any other attributes for a column other than its data type and a CHARACTER SET clause for character data types.

Example: Different LOB Specifications for Parameter and RETURNS TABLE Clauses

The following example indicates the different ways you must specify LOB data types for function parameters and table columns, respectively. Notice how the parameter type specifications require the specification of an AS LOCATOR phrase, while the table column specifications prohibit the use of an AS LOCATOR phrase.

     CREATE FUNCTION lobtf_concat3 (
      NumRows INTEGER,
     A       BLOB AS LOCATOR,
     B       VARBYTE(64000),
     C       BLOB AS LOCATOR)
     a_out    BLOB(10),
     b_out    VARBYTE(10),
     c_out    BLOB(10),
     myresult BLOB(30))
     NO SQL
       EXTERNAL NAME 'SS!lobtf_concat3!/home/i18n/hsf/tf/c/