15.10 - RETURNS data type - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

Teradata Database
December 2015
Programming Reference

The data type for the value returned by the external function.

This clause is mandatory for all UDFs.

You can specify the system-defined parameter type TD_ANYTYPE as the data type for a RETURNS clause. TD_ANYTYPE can assume any system-defined data type. Its attributes are determined when the function is executed. See SQL Data Types and Literals, B035-1143 for more information about the TD_ANYTYPE data type and SQL External Routine Programming for information about writing function code that takes advantage of the TD_ANYTYPE data type.

The RETURNS data type of an algorithmic function used to compress a UDT column must be VARBYTE(n).

The RETURNS data type of an algorithmic function used to decompress a UDT column must match the UDT parameter data type of its compression function and the data type of the UDT column exactly.

The return length n of the RETURNS VARBYTE(n) data type of an algorithmic decompression function must match the length of the compression function VARBYTE(n) parameter exactly.

You cannot specify a character server data set of KANJI1. Otherwise, Teradata Database returns an error to the requestor.

The function is responsible for providing the returned data with the correct type. If the return type is difficult for the function to create, you should also specify a CAST FROM clause so the system can perform the appropriate data type conversion. For more information about using CAST expressions, see SQL Functions, Operators, Expressions, and Predicates, B035-1145.

The result type has a dictionary entry in DBC.TVFields under the name RETURN0[n], where n is a sequence of digits appended to RETURN0 rows to make each value unique, ensuring that no user-defined parameter names are duplicated. The value of n is incremented until it no longer duplicates a parameter name.

The RETURNS data type for a row-level security policy UDF has the following possible values.

  • If the policy implements INSERT or UPDATE actions, the data type must be the same as that specified by the constraint definition. See CREATE CONSTRAINT.

    The value for the parameter is whatever was inserted as a new row or was updated in an existing row.

  • If the policy implements DELETE or SELECT actions, the data type for the return parameter must be CHARACTER(1).

    The value for the parameter is either T or F.

Do not specify the subscript indicated by n if there is no parameter name of RETURN0.

Unity Director uses a CLIv2 parcel to send request-specific context information as part of the request to enable Teradata Database to replace the result of functions referenced in the request with predefined values. Teradata Database makes this context information available in the RETURN expression for a UDF.

However, UDFs can generate and use their own arbitrary nondeterministic values that Teradata Database does not have knowledge of. Therefore Teradata Database cannot guarantee that SQL referencing such UDFs has a consistent result.