CREATE METHOD Syntax Elements - 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™
INSTANCE
The object is an instance method.
INSTANCE is the default.
CONSTRUCTOR
The object is a constructor method.
method_name
The calling name for the method.
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, or method, even if the new object name contains only single-byte characters. Instead, use a multibyte session character set. Otherwise, Vantage returns an error to the requestor.
The name for a method object must conform to object naming rules. For object naming rules, see Teradata Vantage™ - SQL Fundamentals, B035-1141.
method_name must be unique within the SYSUDTLIB database. You cannot give a method the same name as an existing method or any other database object contained within the SYSUDTLIB database.
method_name must match the spelling and case of its C/C++ method name exactly if you do not specify a specific_method_name or external_method_name. This applies only to the definition of the method, not to its use.
SQL supports function name overloading within the same method class, so method_name does not have to be unique within its class.
Parameter data types and number of parameters are used to distinguish among different methods within the same class that have the same method_name.
For more information about function overloading, see Teradata Vantage™ - SQL External Routine Programming , B035-1147 .
For information about instance methods and constructor methods, see the CREATE METHOD topic “Method Types” in Teradata Vantage™ - SQL Data Definition Language Detailed Topics , B035-1184 .
return_data_type
The predefined data type or UDT for the value returned by the method.
The method is responsible for providing the returned data with the correct type. If the return type is difficult for the method to create, you should also specify a CAST FROM clause so the system can perform the appropriate data type conversion. For more information about using CAST expressions, see Teradata Vantage™ - SQL Functions, Expressions, and Predicates, B035-1145.
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.
If there is no parameter name of RETURN0, then the n subscript is not used.
If the external routine for the method is written in C or C++, you can specify TD_ANYTYPE as a parameter data type.
See the CREATE FUNCTION/REPLACE FUNCTION topic “Returns Clause” in Teradata Vantage™ - SQL Data Definition Language Detailed Topics , B035-1184 .
This clause is optional.
If you specify a RETURNS clause, it must be the same as the RETURNS clause specified for the corresponding CREATE TYPE.
If you do not specify a RETURNS clause for the method, then the RETURNS clause specified for the corresponding CREATE TYPE applies to this method by default.
cast_data_type
The result type returned by the method that is to be converted to the type specified by the RETURNS clause.
If you specify a CAST FROM clause, there must be an existing cast from the “result cast from” data type to the returns data type. Example:
  ...RETURNS DECIMAL(9,5) CAST FROM FLOAT...
If data type is a UDT, then the appropriate cast and transform must be defined to handle its conversion. See CREATE CAST and REPLACE CAST and CREATE TRANSFORM and REPLACE TRANSFORM.
Whenever a LOB that requires data type conversion is passed to a method, the LOB must first be materialized for the conversion to take place.
If data type contains a locator indication, then you cannot specify a CAST FROM clause for the method.
UDT_name
Name of the UDT with which the method is associated.
The UDT referenced can be either a distinct UDT or a structured UDT. See "CREATE TYPE (Distinct Form)" and "CREATE TYPE (Structured Form)" in Teradata Vantage™ - SQL Data Definition Language Detailed Topics , B035-1184 .
GLOP_set_name
The name of the GLOP set to associate with this method.
It is not mandatory that the specified GLOP set exist at the time the method is created.
EXTERNAL
The introduction to the mandatory external method body reference clause.
You can specify the following keywords:
  • EXTERNAL only.
  • EXTERNAL NAME plus an external method name and, optionally, a Parameter Style specification.
external_method_name
The entry point for the method object. This name must be unique within the SYSUDTLIB database.
Case is significant and must match the C or C++ method name.
EXTERNAL SECURITY
Keywords introducing the external security clause.
This clause is mandatory for methods that perform operating system I/O operations.
If you do not specify an external security clause, but the method 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 the CREATE FUNCTION/REPLACE FUNCTION topic “External Security Clause” in Teradata Vantage™ - SQL Data Definition Language Detailed Topics , B035-1184 for information about the EXTERNAL SECURITY clause and how it is used for UDFs. This information generalizes to all external routines, including methods and external SQL procedures.
See CREATE AUTHORIZATION and REPLACE AUTHORIZATION for information about creating authorizations for external routines.
DEFINER
The method runs in the client user context of the associated security authorization object created for this purpose, which is contained within SYSUDTLIB.
  • If you specify an authorization name, you must define an authorization object with that name before you can invoke the method.
  • 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 method.
The system reports a warning if the specified authorization name does not exist at the time the method is created, stating that no authorization name exists.
See CREATE AUTHORIZATION and REPLACE AUTHORIZATION.
authorization_name
An optional authorization name.
INVOKER
The method runs in the OS user context with the associated default authorization object that exists for this purpose.
See CREATE AUTHORIZATION and REPLACE AUTHORIZATION.