Parameter Names and Data Types in CREATE FUNCTION and REPLACE FUNCTION (SQL Form) - Teradata VantageCloud Lake

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, the system returns an error to the requestor.

You cannot specify TD_ANYTYPE, VARIANT_TYPE, or TABLE as parameter data types for an SQL function, with one exception: when parameter sizes and argument data types differ, but the data types belong to the same category. 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 is BYTEINT and the argument type is INTEGER, the mismatch ends the request and returns 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 must 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)) and are reported by the HELP FUNCTION STATEMENT 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 required 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 Data Type Phrases for a complete list of data types). Character data types can also specify an associated CHARACTER SET clause.

For character string types that can have different lengths, depending on the caller, the length of the parameter in the definition indicates the longest string that can be passed. If you pass a longer string, the result depends on session mode.

You cannot specify a character data type that has a server character set of KANJI1.

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