16.20 - Usage Notes - Teradata Vantage NewSQL Engine

Teradata Vantageā„¢ SQL Functions, Expressions, and Predicates

prodname
Teradata Database
Teradata Vantage NewSQL Engine
vrm_release
16.20
category
Programming Reference
featnum
B035-1145-162K
An SQL UDF is a function that is defined by a user and is written using SQL expressions. When Teradata Database 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, Teradata Database 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 
)