15.10 - parameter name data type - Teradata Database

Teradata Database SQL Data Definition Language Syntax and Examples

prodname
Teradata Database
vrm_release
15.10
created_date
December 2015
category
Programming Reference
featnum
B035-1144-151K

Optional parameter names and locators for the variables to be passed to the function. A function that is used to compress or decompress a UDT column can have only one input parameter. The data type of the parameter can be any of the supported UDT data types.

For information about naming database objects, see SQL Fundamentals, B035-1141.

You must specify opening and closing parentheses even if no parameters are to be passed to the function.

The maximum number of parameters a UDF accepts is 128.

Parameter names must be unique within a UDF definition. If you specify one parameter name, then you must specify names for all the parameters passed to the function. You cannot use the keyword SELF to name UDF parameters. If you do not specify parameter names, Teradata Database assigns unique names to them in the form P1, P2, …, Pn. These names are used in the COMMENT statement and displayed in the report produced by the HELP FUNCTION statement, and appear in the text of error messages. See COMMENT (Comment Placing Form) and HELP FUNCTION.

The parameter data type of an algorithmic compression function used to compress a UDT column must match the RETURNS data type of its decompression function exactly. The type of an algorithmic compression function must also match the data type of the UDT column being compressed.

UDFs with a parameter type of UDT must reside in SYSUDTLIB rather than in SYSLIB.

data type
A parenthetical comma-separated list of data types, including UDTs. The data types are required to differentiate between overloaded functions with the same name.
The data type associated with each parameter is the type of the parameter or returned value. All Teradata Database data types are valid. For data types that take a length or size specification, like BYTE, CHARACTER, DECIMAL, VARCHAR, and so on, the size of the parameter indicates the largest number of bytes that can be passed. Character data can also specify a CHARACTER SET clause.
You cannot specify a character server data set of KANJI1. Otherwise, the system returns an error to the requestor.
BLOB and CLOB types must be represented by a locator. For a description of locators, see SQL Data Manipulation Language, B035-1146. Teradata Database does not support in-memory LOB parameters: an AS LOCATOR phrase must be specified for each LOB parameter and return value.
Whenever a LOB that requires data type conversion is passed to a UDF, the LOB must be materialized for the conversion to take place.
VARIANT_TYPE
You can specify the system-defined parameter type VARIANT_TYPE as the data type for a function input parameter. VARIANT_TYPE 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 VARIANT_TYPE parameter data type.
You cannot specify the VARIANT_TYPE UDT in a Java function.
While UDF input and result parameters support a maximum of 128 parameters of any data type, each VARIANT_TYPE input parameter supports a maximum of another 128 parameters. Because you can declare a maximum of 8 UDF input parameters to have the VARIANT_TYPE data type, the actual number of UDF input parameters Teradata Database supports when you specify the maximum number of VARIANT_TYPE parameters is 1,144.
TD_ANYTYPE
The system-defined data type 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 parameter data type, see SQL Data Types and Literals, B035-1143. For more information about how to code C, C++, or Java routines to use TD_ANYTYPE, see SQL External Routine Programming, B035-1147.
You can specify TD_ANYTYPE as the data type for:
  • An input parameter in a scalar, aggregate, or table function written in C, C++, or Java.
  • A result parameter in a scalar or aggregate function written in C, C++, or Java.
You cannot specify TD_ANYTYPE as the data type for a result parameter in a table function.

Input Parameter Values and Row-Level Security

All input parameter values defined for a row-level security constraint must be named. A security constraint UDF is called automatically by Teradata Database whenever the corresponding request type is executed against a table on which the constraint has been defined. Because Teradata Database automatically generates the input parameter values for the function, it must know the source, and the input parameter name is defined as the source for the parameter.

The input parameters allowed for each UDF depend on the statement-action for the UDF as defined in the constraint object.

Action Parameters Required
SELECT or INSERT CURRENT_SESSION Yes
Column name or UDT No
UPDATE CURRENT_SESSION Yes
INPUT_ROW Yes
UDT name No
DELETE INPUT_ROW Yes
Column name or UDT No

You can also specify the following parameter names as the source of the parameter. The meaning of the parameter name is that the input provided by Teradata Database to a security policy UDF is the constraint value from the source defined by the specified DELETE, INSERT, SELECT, or UPDATE statement actions.

CURRENT_SESSION
This parameter name defines the source of the parameter as the value that is currently set for the session for the constraint to which the UDF applies.
INPUT_ROW
This parameter name defines the source of the parameter as being in the corresponding constraint column of the row that is the object of the request.