-
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
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 the information about the 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
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
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:
-
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.