17.00 - return_data_type - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Definition Language Syntax and Examples

Product
Advanced SQL Engine
Teradata Database
Release Number
17.00
Release Date
September 2020
Content Type
Programming Reference
Publication ID
B035-1144-170K
Language
English (United States)

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. For more information about the TD_ANYTYPE data type, see Teradata Vantage™ - Data Types and Literals, B035-1143. For information about writing function code that takes advantage of the TD_ANYTYPE data type, see Teradata Vantage™ - SQL External Routine Programming , B035-1147 .

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, Vantage 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 Teradata Vantage™ - SQL Functions, 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.

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

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

CAST FROM return_data_type

The result type returned by the external function that is to be converted to the type specified by the RETURNS clause.

Example:

  ...RETURNS DECIMAL(9,5) CAST FROM FLOAT...

Whenever a LOB that requires data type conversion is passed to an external UDF, the LOB must first be materialized for the conversion to take place.

The value for data_type can be a UDT.

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