function_attribute - Advanced SQL Engine - Teradata Database

SQL Data Definition Language Syntax and Examples

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

Specific name for the function. This clause is mandatory for overloaded function names, but otherwise is optional and can only be specified once per function definition.

For information about naming database objects, see Teradata Vantage™ - SQL Fundamentals, B035-1141.

If the UDF library for your database contains any objects with multibyte characters in their name, you cannot use a single-byte session character set to create a new UDF, UDT, method, or Java stored procedure object even if the new object name contains only single-byte characters. Otherwise, the system returns an error to the requestor. Instead, use a multibyte session character set.
database_name_2
An optional database name.
specific_function_name
If you use specific_function_name to identify the C or C++ function name, take care to follow the identifier naming conventions of the C or C++ languages. The same suggestion applies to the naming conventions of the Java language for Java functions.
Unlike function_name, the specific name for a function, method, or UDT must be unique within its database to avoid name clashes. This name is stored in DBC.TVM as the name of the UDF database object.
CLASS
Class of the function being defined.
Do not specify a keyword if the function class is scalar.
All row-level security policy UDFs must be scalar functions.
AGGREGATE
AG
The function class is aggregate.
Do not specify this clause for scalar functions.
This clause is optional and can only be specified once per function definition.
interim_size
The size of the aggregate cache allocated for an aggregate UDF.
The minimum value is 1 byte.
The maximum value is 64,000 bytes.
The default value is 64 bytes.
PARAMETER STYLE
Parameter passing convention to be used when passing parameters to the function.
The specified parameter style must match the parameter passing convention of the external function.
If you do not specify a parameter style at this point, you can specify one with the external body reference.
You cannot specify parameter styles more than once in the same CREATE/REPLACE FUNCTION request.
This clause is optional and can only be specified once per function definition.
For more information about UDF parameter styles, see “CREATE FUNCTION (External Form)” in Teradata Vantage™ - SQL Data Definition Language Detailed Topics , B035-1184 .
SQL
Uses indicator variables to pass arguments.
As a result, you can always pass nulls as inputs and return nulls in results.
SQL is the default parameter style.
TD_GENERAL
Uses parameters to pass arguments.
Can neither be passed nor return nulls.
JAVA
Mandatory for all Java functions.
DETERMINISTIC
Specifies that the function returns identical results for identical inputs.
DETERMINISTIC and NOT DETERMINISTIC are optional and can only be specified once per function definition.
NOT DETERMINISTIC
Specifies that the function does not always return identical results for identical inputs.
For example, if the function calls a random number generator as part of its processing, then the results of a function call cannot be known in advance of making the call and the function is NOT DETERMINISTIC.
The default is NOT DETERMINISTIC.
CALLED ON NULL INPUT
The function is always evaluated whether parameters are null at the time the function is to be called or not.
If the PARAMETER STYLE for the function is TD_GENERAL, nulls generate an exception condition.
This clause is optional and can only be specified once per function definition.
The default is CALLED ON NULL INPUT.
RETURNS NULL ON NULL INPUT
If any parameters are null at the time the function is to be called, a null result is returned without evaluating the function.
You cannot specify this option for aggregate functions.
This clause is optional and can only be specified once per function definition.
The default is CALLED ON NULL INPUT.