CREATE FUNCTION and REPLACE FUNCTION Syntax Elements (Table Form) - 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™
database_name_1
user_name_1
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 0 permanent space and without access privileges. See the CREATE FUNCTION/REPLACE FUNCTION topic “Function Calling Argument” in the Teradata Vantage™ - SQL Data Definition Language Detailed Topics , B035-1184 .
Users must have the EXECUTE FUNCTION privilege on any UDF they run from SYSLIB.
If you do not specify a database name, then the system creates or replaces the function within the current database or user.
function_name
Calling name for the function.
For information about naming database objects, see Teradata Vantage™ - SQL Fundamentals, B035-1141.
This is a mandatory attribute for all table 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 table 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 table 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, Vantage returns an error 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. The same suggestion applies to the naming conventions of the Java language for Java table functions. This rule applies only to the definition of the function, not to calling it.
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.
Parameter data types and number of parameters are used to distinguish among different functions within the same class that have the same function_name.
See Teradata Vantage™ - SQL External Routine Programming , B035-1147 for further information about function overloading.
parameter_name
Parameter names must be unique within a table UDF definition.
The maximum number of parameters a UDF accepts is 128.
You must specify opening and closing parentheses even if no parameters are to be passed to the function.
data_type
Vantage supports the system-defined parameter type VARIANT_TYPE for input parameters in a table function.
Note the following rules and restrictions for specifying the system-defined parameter type VARIANT_TYPE for an input parameter in a table function.
  • You can specify VARIANT_TYPE as the data type for a parameter whose external routine is written in C or C++.
  • You cannot specify VARIANT_TYPE as the data type for a parameter whose external routine is written in Java.
Note that while UDFs support a maximum of 128 parameters, each VARIANT_TYPE input parameter supports a maximum of another 128 parameters. Because you can declare a maximum of 8 UDF input parameters to have the VARIANT_TYPE data type, the actual number of UDF input parameters Vantage supports when you specify the maximum number of VARIANT_TYPE parameters is 1,144.
You can specify TD_ANYTYPE as the data type for any parameter of a table function. The system-defined data type TD_ANYTYPE can assume any system-defined data type. Its attributes are determined when the function is executed.
See Teradata Vantage™ - Data Types and Literals, B035-1143 for more information about the TD_ANYTYPE parameter data type and see Teradata Vantage™ - SQL External Routine Programming , B035-1147 for more information about how to code C and C++ routines to take advantage of TD_ANYTYPE.
The following rules and restrictions apply to specifying the system-defined parameter type TD_ANYTYPE for an input or result parameter.
  • You can specify TD_ANYTYPE as the data type for an input parameter in a scalar, aggregate, or table function written in C, C++, or Java.
  • You can specify TD_ANYTYPE as the data type for a result parameter in a scalar or aggregate function written in C, C++, or Java.
  • You cannot specify TD_ANYTYPE as the data type for a result parameter in a table function.
You cannot specify a character server data set of KANJI1. Otherwise, Vantage returns an error to the requestor.
BLOB and CLOB parameter data types must be represented by a locator, in contrast with RETURN TABLE clause LOB column data types, which must not be represented by locators. For a description of locators, see “USING Request Modifier” in Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.
Vantage does not support in-memory LOB parameters: an AS LOCATOR phrase must be specified for each LOB parameter.
Note, however, that whenever a LOB that requires data type conversion is passed to a table UDF, the LOB must be materialized for the conversion to take place.
If you specify one parameter name, then you must specify names for all the parameters passed to the function.
If you do not specify parameter names, the system assigns unique names to them in the form P1, P2, …, P n. These names are used in the COMMENT statement, displayed in the report produced by the HELP FUNCTION statement, and appear in the text of error messages. See COMMENT (Comment Placing Form) and HELP ONLINE.
The data type associated with each parameter is the type of the parameter or returned value. All Vantage data types are valid. Character data can also specify a CHARACTER SET clause.
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.
column_name
Name of a column in the set of rows to be returned by the function. You must define at least one column name and its data type for any table function definition.
For information about naming database objects, see SQL Fundamentals .
The maximum number of columns you can specify per table function is 2,048.
If one of the specified columns has a LOB data type, then you must define at least one other non-LOB column.
The complete set of column names and their accompanying data types defines the structure of the table the function returns when it is called.
column_data_type
A data type for each column name you specify. For a list of data types, see Data Types Syntax.
If the type is one of the CHARACTER family, then you can also specify a CHARACTER SET attribute.
You cannot specify a RETURNS clause data type of TD_ANYTYPE.
You cannot specify a character server data set of KANJI1. Otherwise, Vantage returns an error to the requestor.
If the data type for a returned column is either BLOB or CLOB, then you cannot specify it with an AS LOCATOR phrase. Such a specification returns an error to the requestor.
This is in direct contrast with the specification of LOB parameter data types, which must be specified with an AS LOCATOR phrase.
You cannot specify any other attributes for a column other than its data type and a CHARACTER SET clause for character data types.
maximum_output_columns
Number of output columns to be returned by the function is not known before it is invoked, so limit them to a maximum of maximum_output_columns columns.
The upper limit is 2,048 columns per table function.
There is no default value.
database_name_2
user_name_2
Optional database or user specification.
function_name
Accepts one table or table expression as input and produces one table.
Specifying an explicit function name is optional.
language_clause
A code that represents the programming language in which the external function is written:
Code Meaning
C The external function is written in C, even if the external function is in object form.
CPP The external function is written in C++, even if the external function is in object form.
JAVA The external function is written in Java.
SAS The external function is written in SAS, which must use the SQLTABLE parameter style.
This is a mandatory attribute for all UDFs.
The valid languages for writing external UDFs are C, C++, and Java.
If the external function object is not written in C, C++, or Java, it must be compatible with C, C++, or Java object code.
See the CREATE FUNCTION/REPLACE FUNCTION topic “Language Clause” in Teradata Vantage™ - SQL Data Definition Language Detailed Topics , B035-1184 .
SQL_data_access
Specifies whether the external function body accesses the database or contains SQL statements.
This clause is mandatory for all table UDFs, and it must be specified as NO SQL.

function_attribute

database_name_3
user_name_3
Optional database or user name.
specific_function_name
Specific name for the function.
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, Vantage returns an error to the requestor. Instead, use a multibyte session character set.
For information about naming database objects, see Teradata Vantage™ - SQL Fundamentals, B035-1141.
This clause is mandatory for overloaded function names, but is otherwise optional and can only be specified once per function definition.
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.
The specific_function_name must be unique within its database or user to avoid name clashes, unlike function_name.
This name is stored in DBC.TVM as the name of the UDF database object.
PARAMETER STYLE
The parameter passing convention to be used when passing parameters to the table function.
The specified parameter style must match the parameter passing convention of the external function.
This clause is optional for SQL table functions and can only be specified once per function definition. It is mandatory for Java table functions.
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.
You cannot use a table function to enforce row-level security for a security constraint.
For more information about UDF parameter styles, see “CREATE FUNCTION (Table 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.
JAVA
Mandatory for all Java table functions.
If the Java function must accept null arguments, then the EXTERNAL NAME clause must include the list of parameters and specify data types that map to Java objects.
DETERMINISTIC
Specifies whether the function returns identical results for identical inputs.
This clause is optional and can only be specified once per function definition.
NOT DETERMINISTIC
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.
This clause is optional and can only be specified once per function definition.
GLOP_set_name
Name of the GLOP set this table function is associated with.
For information about naming database objects, see Teradata Vantage™ - SQL Fundamentals, B035-1141.
The specified GLOP set does not have to exist at the time the table function is created.
You can specify this clause anywhere between the RETURNS clause and the EXTERNAL clause.
EXTERNAL
Introduction to the external function body reference clause.
This clause is mandatory for all table 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 mandatory 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, up to 30 characters.
For information about naming database objects, see Teradata Vantage™ - SQL Fundamentals, B035-1141.
Case is significant and must match the C, C++, or Java function name.
external_string_literal
A string that specifies the location of source and object components needed to build the table function.
For more information, see Teradata Vantage™ - SQL Data Definition Language Detailed Topics , B035-1184 .

code_specification

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

F
Function object. The string that follows is the entry point name of the C or C++ table function object.
F!function_entry_point_name
C
Client. The source or object code for the table function is stored on the client.
S
Server. The source or object code for the table function is stored on the server.

path_specification

Following is a list of the path specifications for the external table function. The options may be repeated as many times as needed 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.
file_pathname
Location (path) and name of the source, include file, object, or library. Because packages and libraries must be preinstalled, you must use the server option (S). Path specifications can use forward slashes (/) or backward slashes (\) regardless of whether the function is being created on a Unix or Windows platform.

JAR_ID_specification

The following variables apply to Java table 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 executed.
method_name
The name of the method executed when the UDF is executed.
See SQL Fundamentals for the rules for naming database objects.

java_parameter_class

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
EXTERNAL SECURITY
Keywords introducing the external security clause.
This clause is mandatory for external UDFs that perform operating system I/O operations.
If you do not specify an external security clause, but the UDF being defined performs OS I/O, then the results of that I/O are unpredictable. The most likely outcome is crashing the database, and perhaps crashing the entire system.
See CREATE AUTHORIZATION and REPLACE AUTHORIZATION for information about creating authorizations for external routines.
DEFINER
Specifies that 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 table function.
  • If you specify an authorization name, you must define an authorization object with that name before you can invoke the table function.
  • 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 table function.
Vantage reports a warning if the specified authorization name does not exist at the time the UDF is created, stating that no authorization name exists.
If you then attempt to execute the table function, the request aborts and Vantage returns an error to the requestor.
authorization_name
Optional authorization name.
For information about naming database objects, see Teradata Vantage™ - SQL Fundamentals, B035-1141.
The specified authorization object must already be defined or the system reports an error. For further information, see CREATE AUTHORIZATION and REPLACE AUTHORIZATION.
INVOKER
Specifies that the table function runs in the OS user context with the associated default authorization object that exists for this purpose.
See CREATE AUTHORIZATION and REPLACE AUTHORIZATION.
EXECUTE MAP
Specify a contiguous or sparse map and, optionally, colocation name for table operator execution. The table operator executes only on the AMPs in the map.
You can override this map by specifying the EXECUTE MAP clause in the FROM clause of the SELECT statement that executes the table operator. See Teradata Vantage™ - SQL Data Manipulation Language, B035-1146.
You must have been granted the specified map.
map_name
Name of contiguous or sparse map.
You cannot specify TD_DataDictonaryMap or TD_GlobalMap.
colocation_name
Name for colocating the function on the same AMPs with other functions, tables, join indexes, or hash indexes.
You can only specify this option for a sparse map. For a contiguous map, the colocation_name is set to NULL.
If you do not specify a colocation name, the name defaults to database_function, where database is the name of the database or user followed by an underscore (_) and function is the name of the user defined function. If database exceeds 63 characters, database is truncated to 63 characters. If function exceeds 64 characters, function is truncated to 64 characters.