CREATE FUNCTION and REPLACE FUNCTION Syntax Elements (External Form) - Teradata Vantage

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 recommended 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 no permanent space and no access privileges.

All row-level security policy functions must reside in the SYSLIB database.

An external UDF used as a cast, ordering, or transform routine for a UDT must be created in the SYSUDTLIB database.

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

function_name

The calling name for the function.

This clause is required for all UDFs.

A function name can be a maximum of 30 characters in length.

If you use function_name to identify the function, 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(U+0022) characters. For example, “TRIM”(). Using the names of intrinsic functions for UDFs is a poor programming practice.

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

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, an error is returned to the requestor. Instead, use a multibyte session character set.

function_name must match the spelling and case of the C, C++, or Java function name exactly if you do not specify a specific_function_name or external_function_name. This rule applies only to the definition of the function, not to calling it.

With the exception of row-level security policy UDFs and XML domain-specific functions with an input parameter that specifies the TD_VALIST data type, SQL supports function name overloading within the same function class, so function_name need not be unique within its class. However, you cannot give the same name to both a scalar and an aggregate function within the same database or user.

Row-level security enforcement UDFs do not support function name overloading. Because of the parameter types required by Vantage for input to the UDF, the same number of parameters and the same data types for the parameters are required for each UDF that runs the security policy for a specific statement action. The only difference that can exist between the parameters for UDFs that run a security policy is that the parameters of different UDFs can either include or omit null indicators, depending on whether the constraint allows nulls.

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_specification

Optional parameter names and locators for the variables to be passed to the function. A function that is used to compress or decompress a UDT column can have only one input parameter. The data type of the parameter can be any of the supported UDT data types.

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

The maximum number of parameters a UDF accepts is 128.

The parameter data type of an algorithmic compression function used to compress a UDT column must match the RETURNS data type of its decompression function exactly. The type of an algorithmic compression function must also match the data type of the UDT column being compressed.

UDFs with a parameter type of UDT must reside in SYSUDTLIB rather than in SYSLIB.

parameter name

Parameter names must be unique within a UDF definition. If you specify one parameter name, you must specify names for all the parameters passed to the function. You cannot use SELF to name UDF parameters. If you do not specify parameter names, Vantage assigns unique names to them in the form P1, P2, …, P n. These names appear in the COMMENT statement, HELP FUNCTION output, and error messages. See COMMENT (Comment-Placing Form) and HELP FUNCTION.

data_type

A parenthetical comma-separated list of data types, including UDTs. The data types are required to differentiate between overloaded functions with the same name.

The data type associated with each parameter is the type of the parameter or returned value. All Vantage data types are valid. 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. Character data can also specify a CHARACTER SET clause.

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

BLOB and CLOB types must be represented by a locator. Vantage does not support in-memory LOB parameters: an AS LOCATOR phrase must be specified for each LOB parameter and return value. See Example: Locator Mode Processing of a Large Object and Example: Using a Locator Multiple Times in a Session.

Whenever a LOB that requires data type conversion is passed to a UDF, the LOB must be materialized for the conversion to take place.

CLOB LATIN/UTF16 is only supported on the Block File System on the primary cluster. It is not available for the Object File System.

return_data_type

The data type for the value returned by the external function.

This clause is required for all UDFs.

You can specify the system-defined parameter type TD_ANYTYPE as the data type for a RETURNS clause. TD_ANYTYPE can assume any system-defined data type. Its attributes are determined when the function runs.

The RETURNS data type of an algorithmic function used to compress a UDT column must be VARBYTE(n).

The RETURNS data type of an algorithmic function used to decompress a UDT column must match the UDT parameter data type of its compression function and the data type of the UDT column exactly.

The return length n of the RETURNS VARBYTE(n) data type of an algorithmic decompression function must match the length of the compression function VARBYTE(n) parameter exactly.

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

The function is responsible for providing the returned data with the correct type. If the return type is difficult for the function to create, specify a CAST FROM clause so the system can perform the appropriate data type conversion. For more information about CAST, see CAST in Explicit Data Type Conversions.

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 no duplicate user-defined parameter names. The value of n is incremented until it no longer duplicates a parameter name.

Do not specify the subscript indicated by n if there is no parameter name of RETURN0.

CAST FROM return_data_type

The result type returned by the external function that is to be converted to the type specified by the RETURNS clause.

Example:

  ...RETURNS DECIMAL(9,5) CAST FROM FLOAT...

Whenever a LOB that requires data type conversion is passed to an external UDF, the LOB must first be materialized for the conversion to take place.

The value for data_type can be a UDT.

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

language_clause

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

LANGUAGE

Keyword to introduce the programming language.

C

The external UDF is written in C.

CPP

The external UDF is written in C++.

JAVA

The external UDF is written in Java.

Row-level security constraints cannot be written in Java.

SQL_data_access

Specifies whether the external function body accesses the database or contains SQL statements.

Required for an external UDF. Must specify NO SQL.

function_attribute

Specific name for the function. Required for overloaded function names, otherwise optional and can only be specified once per function definition.

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 now, 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 General Usage Guidelines: CREATE FUNCTION and REPLACE FUNCTION (External Form).

SQL

Uses indicator variables to pass arguments.

Therefore, you can 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

Required 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 before making the call and the function is NOT DETERMINISTIC.

The default is NOT DETERMINISTIC.

CALLED ON NULL INPUT

The function is 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.

code_specification

Single character code. Depending on the initial code in the sequence, the string specifies either the external function object name for the UDF or an encoded name or path for the components needed to create the external function.

F

Function object. The string that follows is the entry point name of the C or C++ external function object.

F!function_entry_point_name

D

Enables symbolic debugging for the UDF, which shows source code and displays variables by name. Without this option, UDFs can only be debugged at the machine instruction level. Specify this option for debugging purposes when UDFs are being tested. This option adds -g to the C compiler command line.

See the information about C/C++ command-line debugging for UDFs in SET SESSION DEBUG FUNCTION.

The D option applies only to C and C++ code, not to Java UDFs.

Do not use this option when installing debugged UDFs on production system because this option increases the size of the UDF library.

S

Server. The source or object code for the external function is stored on the server.

C

Client. The source or object code for the external function is stored on the client.

JAR_ID_specification

The following variables apply to Java functions only.

JAR_ID

The registered name of the JAR file associated with this function.

java_class_name

The name of the Java class contained within the JAR that contains the Java method to be run.

method_name

The name of the method that runs when the UDF runs.

primitive

A primitive parameter class as one of the following:

• BYTE
• DOUBLE
• INT
• LONG
• SHORT

object

An object parameter class definition in the format:

java.pkg.class

path_specification

Following is a list of the path specifications for the external function. You can repeat options as necessary with the exception of the package option. You can specify the following file types as external string literals.

I

Include file (.h).

I!name_on_server!include_name

L

Library name for a nonstandard library files needed by the UDF.

L!library_name

O

Object file.

O!name_on_server!object_name

P

Package name. You cannot use the package option with any other options except F, the C function name option.

P!package_name

S

Source file.

S!name_on_server!source_name

NS

No source file. Source files and include files are not stored in the function table. This option only affects how source code is processed in the creation of a new function and applies to all source code specified in the external string literal.

NS!source_file!include_file

delimiter

Specify a delimiter character, such as !. You must use the same delimiter throughout the string specification.

name_on_server

Name the file on the server. Include files must have the same name specified in the include statement in the C source, without the extension.

COMPRESS

This UDF is to be used for algorithmic compression of data.

The same UDF cannot be used to compress and decompress data.

DECOMPRESS

Specifies that the UDF is to be used for decompression of data that was algorithmically compressed using the UDF named compress_UDF_name.

The same UDF cannot be used to decompress and compress data.

GLOP_set_name

Name of the GLOP set associated with this function. Can but need not exist when the function is created.

You can specify this clause anywhere between the RETURNS clause and the EXTERNAL clause.

EXTERNAL

Introduction to the required external function body reference clause.

Required for all UDFs.

This clause can specify four different things:

• The keyword EXTERNAL only.
• The keywords EXTERNAL NAME plus an external function name (with optional Parameter Style specification).

  This is a required attribute for all UDFs.

• The keywords EXTERNAL NAME plus a set of external string literals.
• The keywords EXTERNAL NAME plus a Java JAR ID specification.

external_function_name

Entry point for the function object.

Case is significant and must match the C, C++, or Java function name.

EXTERNAL SECURITY

Keywords introducing the external security clause.

Recommended for UDFs that perform operating system I/O operations, because it allows you to specify an OS user under whom the function runs. Otherwise, a protected mode UDF runs under the generic user tdatuser. See CREATE AUTHORIZATION and REPLACE AUTHORIZATION.

DEFINER

The UDF runs in the client user context of the associated security authorization object created for this purpose, which is contained within the same database as the procedure.

• If you specify an authorization name, you must define an authorization object with that name before you can invoke the procedure.
• If you do not specify an authorization name, you must define a default DEFINER authorization object.

The default authorization object must be defined before a user can run the procedure.

Vantage reports a warning if the specified authorization name does not exist at the time the procedure is created, stating that no authorization name exists.

If you then call the procedure, the request aborts and the system returns an error to the requestor.

authorization_name

An optional identifier for this DEFINER.

For information about naming database objects, see Object Names and CREATE AUTHORIZATION and REPLACE AUTHORIZATION.

INVOKER

The function runs using the INVOKER authorization associated with the logged on user who is running the function. See CREATE AUTHORIZATION and REPLACE AUTHORIZATION.