15.00 - Scalar UDF - Teradata Database

Teradata Database SQL Functions, Operators, Expressions, and Predicates

Product
Teradata Database
Release Number
15.00
Content Type
Programming Reference
Publication ID
B035-1145-015K
Language
English (United States)

Scalar UDF

Purpose  

Takes input arguments and returns a single value result.

Syntax  

For UDFs that are defined with parameters of TD_ANYTYPE data type:

where:

 

Syntax element …

Specifies …

udf_name

the name of the scalar UDF.

argument

a valid SQL expression. See Usage Notes for rules that apply to scalar UDF arguments.

data_type

the desired return type of the TD_ANYTYPE result parameter.

column_expr

a table or a view column reference that is used to determine the return type of the TD_ANYTYPE result parameter.

ANSI Compliance

Scalar UDFs are partially ANSI SQL:2011 compliant.

The requirement that parentheses appear when the argument list is empty is a Teradata extension to preserve compatibility with existing applications.

The RETURNS data_type or RETURNS STYLE clauses are Teradata extensions to the ANSI SQL standard.

Restrictions

  • Any restrictions that apply to standard SQL scalar functions also apply to scalar UDFs.
  • Scalar UDF expressions cannot be used in a partitioning expression of the CREATE TABLE statement.
  • Authorization

    You must have EXECUTE FUNCTION privileges on the function or on the database containing the function.

    To invoke a scalar UDF that takes a UDT argument or returns a UDT, you must have the UDTUSAGE privilege on the SYSUDTLIB database or on the specified UDT.

    UDF Arguments

    When Teradata Database evaluates a 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 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. For details, see SQL External Routine Programming.
  • 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 information, see “CAST in Explicit Data Type Conversions” on page 590.

    Result Data Type

    The result type of a scalar UDF is based on the return type specified in the RETURNS clause of the CREATE FUNCTION statement.

    When invoking a scalar or aggregate UDF that is defined with a TD_ANYTYPE result parameter, you can use the RETURNS data type or RETURNS STYLE column expression clauses in the function call to specify the desired return type. The column expression can be any valid table or view column reference, and the return data type is determined based on the type of the column.

    The RETURNS or RETURNS STYLE clause is not mandatory as long as the function call also includes a TD_ANYTYPE input argument. If you do not specify a RETURNS or RETURNS STYLE clause, then the data type of the first TD_ANYTYPE input argument is used to determine the return type of the TD_ANYTYPE result parameter. For character types, if the character set is not specified as part of the data type, then the default character set is used.

    You can use these clauses only with scalar and aggregate UDFs. You cannot use them with table functions. Also, you must enclose the UDF invocation in parenthesis if you use the RETURNS or RETURNS STYLE clauses.

    Default Title

    The default title of a scalar UDF appears as:

       UDF_name(argument_list)

    Example  

    Consider the following table definition and data:

       CREATE TABLE pRecords (pname CHAR(30),
                              pkey INTEGER);
     
       SELECT * FROM pRecords;

    The output from the SELECT statement is:

       pname                                  pkey
       ------------------------------  -----------
       Tom                                       6
       Bob                                       5
       Jane                                      4

    The following is the SQL definition of a scalar UDF that calculates the factorial of an integer argument:

       CREATE FUNCTION factorial (i INTEGER)
       RETURNS INTEGER
       SPECIFIC factorial
       LANGUAGE C
       NO SQL
       PARAMETER STYLE TD_GENERAL
       NOT DETERMINISTIC
       RETURNS NULL ON NULL INPUT
       EXTERNAL NAME 'ss!factorial!factorial.c!F!fact'

    The following query uses the scalar UDF expression to calculate the factorial of the pkey column + 1.

       SELECT pname, factorial(pkey)+1 
       FROM pRecords;

    The output from the SELECT statement is:

       pname                           (factorial(pkey)+1)
       ------------------------------  -------------------
       Tom                                             721
       Bob                                             121
       Jane                                             25

    Example  

    Consider the following table and function definitions:

       CREATE TABLE T1 (intCol INTEGER, 
                        varCharCol VARCHAR(40) CHARACTER SET UNICODE);
     
       CREATE TABLE T2 (intCol INTEGER, 
                        decimalCol DECIMAL (10, 6));
     
       CREATE FUNCTION myUDF1 (A INTEGER, B TD_ANYTYPE) 
       RETURNS TD_ANYTYPE;
     
       CREATE FUNCTION myUDF2 (A TD_ANYTYPE, B TD_ANYTYPE) 
       RETURNS TD_ANYTYPE;

    The following invocation of myUDF1 uses the RETURNS data_type clause to specify the UDF return type to be DECIMAL(10,6).

       SELECT (myUDF1 (T1.intCol, T2.decimalCol) RETURNS DECIMAL(10,6)); 

    The following invocation of myUDF2 uses the RETURNS STYLE clause to specify the UDF return type to be the data type of the T1.varCharCol column, which is VARCHAR(40) CHARACTER SET UNICODE.

       SELECT (myUDF2 (T1.varCharCol, T2.decimalCol)
          RETURNS STYLE T1.varCharCol);

    Related Topics

     

    FOR more information on …

    SEE …

    Implementing external UDFs

    SQL External Routine Programming.

  • CREATE FUNCTION
  • REPLACE FUNCTION
  • SQL Data Definition Language.
  • Database Administration.
  • EXECUTE FUNCTION and UDTUSAGE privileges

    SQL Data Control Language.

    the TD_ANYTYPE data type

    SQL Data Types and Literals.