Function Calling Argument - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Detailed Topics

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-24
dita:mapPath
jpx1556733107962.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1184
lifecycle
previous
Product Category
Teradata Vantage™

The function calling argument is any simple SQL expression, including, but not limited to, constant values, column references, host variables, the NEW VARIANT_TYPE UDT constructor expression (see Teradata Vantage™ - SQL Operators and User-Defined Functions, B035-1210), or an expression containing any of these, including expressions containing UDFs.

When you call an SQL function, and that function is not stored in either your default database or in database SYSLIB, you must fully qualify the function call with a database name. If your default database and SYSLIB both contain functions matching the name of the called function, then the system references the UDF in your default database. This is the only exception that requires an explicit qualification for SYSLIB.

The argument types passed in the call must be compatible with the parameter declarations in the function definition of an existing function. If there are several functions that have the same name, and no qualifying database is specified, then the particular function that is picked is determined by the following process:

  1. Teradata Database searches the list of built-in functions.

    If the called function has the same name as a Teradata Database built-in function, the search stops and that function is used.

    If a candidate function is not found, proceed to stage 2.

  2. Teradata Database searches the list of SQL and external function names in the default user database.

    Candidate functions are those having the same name and number of parameters as specified by the function call as well as the best fit based on their parameter type.

    If a candidate function is not found, proceed to stage 3.

  3. Teradata Database searches the list of function names in the SYSLIB database.

    Candidate functions are those having the same name and number of parameters as specified by the function call as well as the best fit based on their parameter type.

    If a candidate function cannot be located, Teradata Database returns an error to the requestor.

The rules for invoking an SQL UDF using an SQL expression are as follows:
  • An SQL expression passed as an argument must not be a Boolean value expression, that is, a conditional expression.

    This is also true for external UDFs.

  • Nondeterministic SQL expressions, meaning expressions that use either random functions or nondeterministic UDFs, or both, that are passed as an argument must not correspond to a parameter that is used more than once in the RETURN clause of an SQL UDF.
  • An SQL expression passed as an argument cannot be a scalar subquery.

The rules for selecting a best fit candidate user-defined function once its containing database has been determined are described in Function Name Overloading.