An SQL UDF is a function that is defined by a user and is written using SQL expressions. When Vantage evaluates an SQL UDF expression, it invokes the function with the arguments passed to it. The following rules apply to the arguments in the function call:
- The arguments must be comma-separated expressions in the same order as the parameters declared in the function.
- The number of arguments passed to the SQL UDF must be the same as the number of parameters declared in the function.
- The data types of the arguments must be compatible with the corresponding parameter declarations in the function and follow the precedence rules that apply to compatible types.
To pass an argument that is not compatible with the corresponding parameter type, use CAST to explicitly convert the argument to the proper type. For more information, see "CAST in Explicit Data Type Conversions" in Teradata Vantage™ - Data Types and Literals, B035-1143.
- A NULL argument is compatible with a parameter of any data type. You can pass a NULL argument explicitly or by omitting the argument.
- Any form of SQL expression can be used as an argument with three important rules:
- The SQL expression must not be a Boolean value expression (that is, a conditional expression).
- If the expression is a nondeterministic SQL expression (expressions involving random functions and/or nondeterministic UDFs), it must not correspond to a parameter that is used more than once in the RETURN statement.
- The SQL expression must not be a scalar subquery.
When an SQL UDF is invoked, Vantage searches for the UDF in the following locations:
- In the database specified if the function call is qualified by a database name.
- In the current database.
- In the SYSLIB database.
The result type of an SQL UDF is based on the return type specified in the RETURNS clause of the CREATE FUNCTION statement.
The default title of an SQL UDF appears as:
UDF_name (argument_list )