CREATE FUNCTION and REPLACE FUNCTION Syntax Elements (External 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
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 0 permanent space and without 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 mandatory for all UDFs.
A function name can be a maximum of 30 characters in length.
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(U+0022) 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.
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 executes the security policy for a specific statement action. The only difference that can exist between the parameters for UDFs that execute 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.
For further information about function overloading, see Teradata Vantage™ - SQL External Routine Programming , B035-1147 .
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 this function is associated with.
For information about naming database objects, see Teradata Vantage™ - SQL Fundamentals, B035-1141.
It is not mandatory that the specified GLOP set exist at the time the function is created.
You can specify this clause anywhere between the RETURNS clause and the EXTERNAL clause.
EXTERNAL
Introduction to the mandatory external function body reference clause.
This clause is mandatory 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 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.
For details about object name length limits, see Teradata Vantage™ - SQL Fundamentals, B035-1141.
Case is significant and must match the C, C++, or Java function name.
EXTERNAL SECURITY
Keywords introducing the external security clause.
This clause is recommended for UDFs that perform operating system I/O operations because it permits you to specify a particular OS user under whom the function runs. Otherwise, a protected mode UDF runs under the generic user tdatuser.
Also 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 attempt to execute 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 Teradata Vantage™ - SQL Fundamentals, B035-1141.
See 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.