15.00 - Parameter Names and Data Types - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1184-015K

Parameter Names and Data Types

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

Function parameters must be explicitly named. Parameter names are standard SQL identifiers.

The data types of SQL expressions passed as arguments must match those of their corresponding parameter data types in the definition of the SQL function. If the types do not match, Teradata Database aborts the request and returns an error to the requestor.

You cannot specify TD_ANYTYPE, VARIANT_TYPE, or TABLE as parameter data types for an SQL function.

The only exception to this is the case where the sizes of the parameter and argument data types differ, but the types themselves belong to the same category of data types such as numeric types, character types, DateTime types, and so on. In this case, a mismatch is tolerated as long as the argument size is less than the parameter size. For example, when the parameter type is INTEGER and the argument type is BYTEINT, the types are treated as being compatible. However, if the parameter type were BYTEINT and the argument type were INTEGER, the mismatch would abort the request and return an error to the requestor.

For example, the argument and parameter types are a mismatch in the following SQL UDF definition, but because the size of the argument type is less than the corresponding parameter type, the types are compatible.

     CREATE FUNCTION df2.myudf (a INTEGER, b INTEGER, c INTEGER)
     RETURNS INTEGER
     LANGUAGE SQL
     DETERMINISTIC
     SQL SECURITY DEFINER
     COLLATION INVOKER
     INLINE TYPE 1
     RETURN a*b*c;
 
     CREATE TABLE t1 (
       a1 BYTEINT,
       b1 INTEGER);
 
     SELECT df2.myudf(t1.a1, t1.b2, 2)
     FROM t1;

The contrary result is seen for the following example, where the only difference is that column a1 is defined with an INTEGER type rather than a BYTEINT type. Because the argument type is greater than its corresponding UDF parameter type in the following example, the SELECT request aborts and returns an error to the requestor.

     CREATE FUNCTION df2.myudf (a BYTEINT, b INTEGER, c INTEGER)
     RETURNS INTEGER
     LANGUAGE SQL
     DETERMINISTIC
     SQL SECURITY DEFINER
     COLLATION INVOKER
     INLINE TYPE 1
     RETURN a*b*c;
 
     CREATE TABLE t1 (
       a1 INTEGER,
       b1 INTEGER);
 
     SELECT df2.myudf(t1.a1, t1.b2, 2)
     FROM t1;

To make this example work correctly, you would need to explicitly cast t1.a1 as BYTEINT in the SELECT request, because an INTEGER argument does not fit into the BYTEINT parameter a as defined by the UDF df2.myudf.

Parameter names are used by the COMMENT statement (see “COMMENT (Comment Placing Form)” on page 203) and are reported by the HELP FUNCTION STATEMENT statement (see “HELP FUNCTION” on page 925). 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 SQL Data Types and Literals 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, Teradata Database aborts the request and returns an error to the requestor.

The following table summarizes the standard Teradata Database 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.