15.00 - Function Calling Argument - Teradata Database

Teradata Database SQL Data Definition Language Detailed Topics

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

Function Calling Argument

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 SQL Functions, Operators, Expressions, and Predicates), 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 (see “Function Name Overloading” on page 309), 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 Teradata Database cannot find a candidate function, it aborts the request and 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 (a conditional expression).
  • This is also true for external UDFs.

  • Non‑deterministic SQL expressions, meaning expressions that use either random functions or non‑deterministic 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” on page 309.