Parameter Names and Data Types - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.10
Published
July 2021
Language
English (United States)
Last Update
2021-07-27
dita:mapPath
imq1591724555718.ditamap
dita:ditavalPath
imq1591724555718.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™

The parameter list contains a list of variables to be passed to the function.

The naming of ordinary function parameters is optional; however, all input parameters specified for a row-level security policy UDF must be named.

If you specify one parameter name, then you must specify all parameter names explicitly. Parameter names are standard SQL identifiers. If you do not specify parameter names, then Vantage creates arbitrary names for them in the format P n, where n is an integer that specifies the order in which the parameters are created, beginning with P1.

Parameter names are used by the COMMENT statement (see COMMENT (Comment Placing Form)) and are reported by the HELP FUNCTION statement (see HELP FUNCTION). Parameter names, with their associated database and function names, are also returned in the text of error messages when truncation or overflow errors occur with a function call.

Each parameter type is associated with a mandatory data type to define the type of the parameter passed to or returned by the function. The specified data type can be any valid data type, including UDTs (see Teradata Vantage™ - Data Types and Literals, B035-1143 for a complete list of data types). Character data types can also specify an associated CHARACTER SET clause.

For character string types like VARCHAR that might have a different length depending on the caller, the length of the parameter in the definition indicates the longest string that can be passed. If there is an attempt to pass a longer string, the result depends on the session mode.

You cannot specify a character data type that has a server character set of KANJI1. Otherwise, the system returns an error to the requestor.

Input parameter names for a row-level security policy UDF must be named because a security constraint policy UDF is never called by a user in an SQL DML request. Instead, Vantage automatically calls the security policy function whenever the corresponding statement type is executed against a table on which the constraint has been defined. Because Vantage automatically generates the input parameter values for the UDF, it must know the source you want, and the input parameter name defines that source for the parameter.

You must specify the following system-defined parameter names as the source of the parameter to Vantage. The meaning of the system-defined parameter name is that the input provided by Vantage to a security policy UDF is the constraint values from the source defined by the specified DELETE, INSERT, SELECT, or UPDATE statement-actions.

This parameter name … Defines the source of the parameter as …
CURRENT_SESSION the value that is currently set for the session for the constraint to which the UDF applies.
INPUT_ROW being in the corresponding constraint column of the row that is the object of the request.
You can declare input parameters in procedures written in C or C++ only to have the following parameter data types.
  • VARIANT_TYPE
  • TD_VALIST

VARIANT_TYPE data type functions as a structured UDT with a fixed type name of VARIANT_TYPE. VARIANT_TYPE is used to represent the dynamic UDT data type and can be used only to type non-Java UDF input parameters. See Writing a UDF That Uses VARIANT_TYPE UDT Parameters. The system passes a dynamic UDT data type to this parameter position during runtime using the NEW VARIANT_TYPE expression. For examples, see Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144 and Teradata Vantage™ - SQL Operators and User-Defined Functions, B035-1210.

You cannot specify the VARIANT_TYPE data type for input parameters in a Java procedure or Java UDF, nor can you specify it for returned result sets. The maximum number of input parameters you can declare with the VARIANT_TYPE UDT data type is 8.

If you attempt to create a UDF that declares more than 8 input parameters with a VARIANT_TYPE type, or if you attempt to create a UDF that declares an output parameter with a VARIANT_TYPE type, the system returns an error to the requestor.

You can declare both the input and output parameters in procedures written in C or C++ only to have the TD_ANYTYPE data type. TD_ANYTYPE functions as a structured UDT with a fixed type name of TD_ANYTYPE. TD_ANYTYPE represents the dynamic UDT data type and can be used only to type non-Java UDF parameters. Vantage passes a dynamic UDT data type to this parameter position during runtime.

You cannot specify the TD_ANYTYPE data type for parameters in a Java UDF.

For more information about the TD_ANYTYPE data type, see Teradata Vantage™ - Data Types and Literals, B035-1143 and for more information about how to code your UDFs to make the best use of TD_ANYTYPE, see Teradata Vantage™ - SQL External Routine Programming, B035-1147.

The TD_VALIST input parameter data type enables XML domain-specific UDFs only to use varying number of variables in XML function definitions. In C and C++, it is useful to be able to declare a function footprint that is not fixed with respect to the number and types of parameters that can be passed into the function at runtime. This is the function of the TD_VALIST input parameter data type: to signify that n parameters with any data type can be passed into the function at runtime, where the maximum value for n is 128.

Vantage only supports TD_VALIST for XML Embedded Services functions. If you create a UDF that specifies an input parameter of type TD_VALIST, it can only be a single input parameter to the UDF. Overloading the function is not allowed when the function input parameter has a TD_VALIST data type.

TD_VALIST is an input parameter data type that specifies that n parameters with various data types can be passed into the function at runtime. TD_VALIST provides the benefit of reducing the number of overloading routines required to support routines with varying numbers of input parameters.

The following table summarizes the standard Vantage session mode semantics with respect to character string truncation.

IF the session mode is … THEN …
ANSI any pad characters in the string are truncated silently and no truncation notification is returned to the requestor.

A truncation exception is returned whenever non-pad characters are truncated.

If there is a truncation exception, then the system does not call the function. The relevant indicator values are not set to the number of characters truncated.

Teradata the string is truncated silently and no truncation notification message is returned to the requestor.

For details on parameter passing conventions for the TD_GENERAL, SQL, and JAVA parameter styles, see Teradata Vantage™ - SQL External Routine Programming, B035-1147.