CREATE FUNCTION and REPLACE FUNCTION Syntax Elements (SQL Form) - Advanced SQL Engine - Teradata Database

database_name_1

user_name_1

An optional database or user name specified if the function is to be created or replaced in a non-default database or user.

If you use the SYSLIB database as your UDF depository, you must modify the size of its permanent space and grant appropriate privileges on it because it is created with 0 permanent space and without access privileges.

Users must have the EXECUTE FUNCTION privilege on any UDF they run or on the database containing the function.

If you do not specify a database name, then the system creates or replaces the function within the current database.

function_name

Calling name for the SQL function.

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

This clause is mandatory for all UDFs.

If you use function_name to identify the function, take care to follow the identifier naming conventions of the programming language in which it is written.

You cannot give a UDF the same name as an existing Vantage-supplied function (also known as an intrinsic function) unless you enclose the name in QUOTATION MARK characters. For example, “TRIM”(). Using the names of intrinsic functions for UDFs is a poor programming practice and should be avoided.

A UDT and a UDF without parameters that is stored in SYSUDTLIB cannot have the same name.

Parameter data types and number of parameters are used to distinguish among different functions within the same class that have the same function_name.

parameter_name

A parenthetical comma-separated list of data types, including UDTs, and parameter names for the variables to be passed to the SQL function. The data types are required to differentiate between functions with the same name.

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

The maximum number of parameters an SQL UDF accepts is 128.

You must specify opening and closing parentheses even if no parameters are to be passed to the function.

Parameter names must be unique within an SQL UDF definition.

If you specify one parameter name, then you must specify names for all the parameters passed to the function.

parameter_data_type

The data type associated with each parameter is the type of the parameter or returned value. All Vantage data types except VARIANT_TYPE and TD_ANYTYPE are valid. Character data can also specify a CHARACTER SET clause.

Although you can specify the CHARACTER SET for a parameter, the CHARACTER SET of the caller of the SQL UDF argument is what Vantage uses for SQL UDF processing.

You cannot specify a character server data set of KANJI1. Otherwise, Vantage returns an error to the requestor.

For data types that take a length or size specification, like BYTE, CHARACTER, DECIMAL, VARCHAR, and so on, the size of the parameter indicates the largest number of bytes that can be passed.

return_data_type

Data type for the value returned by the SQL function.

This clause is mandatory for all SQL UDFs.

You cannot specify a character server data set of KANJI1. Otherwise, Vantage returns an error to the requestor.

You cannot specify a RETURNS data type of TD_ANYTYPE for an SQL UDF.

The function is responsible for providing the returned data with the correct type.

The result type has a dictionary entry in DBC.TVFields under the name RETURN0[n], where n is a sequence of digits appended to RETURN0 rows to make each value unique, ensuring that no user-defined parameter names are duplicated. The value of n is incremented until it no longer duplicates a parameter name.

The n subscript is not used if there is no parameter name of RETURN0.

language_clause

A code that represents the programming language in which the SQL function is written.

The only valid language for writing SQL UDFs is SQL, so you must specify either LANGUAGE SQL or nothing.

The default is LANGUAGE SQL.

SQL_data_access

Whether the SQL function body accesses the database or contains SQL statements.

This is a mandatory attribute for all SQL UDFs and it must be specified as CONTAINS SQL.

database_name_2

user_name_2

An optional database or user name for the specific function.

specific_function_name

Specific name for the function.

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

Unlike function_name, the specific name for an SQL 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.

This clause is mandatory for overloaded function_names, but otherwise optional and can only be specified once per SQL function definition.

DETERMINISTIC

The function returns identical results for identical inputs.

DETERMINISTIC can be specified only once per function definition.

NOT DETERMINISTIC

The function might not return identical results for identical inputs.

For example, if the SQL function calls a random number generator as part of its processing, then the results of an SQL function call cannot be known in advance of making the call and the function is NOT DETERMINISTIC.

NOT DETERMINISTIC can be specified only once per function definition.

The default is NOT DETERMINISTIC.

CALLED ON NULL INPUT

Specifies that the SQL function is always evaluated whether parameters are null at the time the function is to be called or not.

This clause is optional and can only be specified once per SQL function definition.

The default is CALLED ON NULL INPUT.

RETURNS NULL ON NULL INPUT

If any parameters are null at the time the SQL function is to be called, a null result is returned without evaluating the function.

This clause is optional and can only be specified once per SQL function definition.

The default is CALLED ON NULL INPUT.

SQL SECURITY DEFINER

Vantage checks the privileges of the creator and owner of the function for objects it references when it is invoked.

If either the immediate owner or the creator do not have the privileges required to access the database objects specified within the UDF definition at the time the function is invoked, Vantage returns an error to the requestor.

If the owner of the function does not exist at the time privileges are checked, Vantage returns an error to the requestor.

SQL SECURITY DEFINER is the default for an SQL UDF and is the only valid entry.

You can only specify this clause if you also specify LANGUAGE SQL.

COLLATION INVOKER

The function uses the default collation setting of the session it is called from rather than the default collation for its creator.

This clause is mandatory.

INLINE TYPE 1

The function is to be executed inline from an SQL request rather than as an independent function.

This clause is mandatory.

The TYPE 1 option indicates that the SQL UDF executes based on the environmental settings of the system. A system that has different settings, might produce different results.

When an SQL UDF is created using the inline option, you can still specify how it should work at the time it is created, depending on the environment in effect for the creator when the function is created and the environment for the caller when it is invoked. If the two environments differ, then the outcome of calling the function can still differ.

return_expression

The SQL statement the SQL UDF is to execute.

You cannot specify a FORMAT attribute for a RETURN expression data type.

The only supported SQL procedure statement is RETURN. See RETURN Statement.