External UDFs - Teradata Database

External UDFs allow you to write your own functions in the C, C++, or Java programming language, install them on the database, and then use them like standard SQL functions.

You can also install external UDF objects or packages from third-party vendors.

Teradata Database supports three types of external UDFs.

UDF Type	Description
Aggregate	Aggregate functions produce summary results. They differ from scalar functions in that they take grouped sets of relational data, make a pass over each group, and return one result for the group. Some examples of standard SQL aggregate functions are AVG, SUM, MAX, and MIN.
Scalar	Scalar functions take input parameters and return a single value result. Examples of standard SQL scalar functions are CHARACTER_LENGTH, POSITION, and TRIM.
Table	A table function is invoked in the FROM clause of a SELECT statement and returns a table to the statement.

To create and use an SQL UDF, follow these steps:

Use CREATE FUNCTION or REPLACE FUNCTION to define the UDF. Use GRANT to grant privileges to users who are authorized to use the UDF. Call the function.

Here is a synopsis of the steps you take to develop, compile, install, and use an external UDF:

Write, test, and debug the C, C++, or Java code for the UDF. If you are using Java, place the class or classes for the UDF in an archive file (JAR or ZIP) and call the SQLJ.INSTALL_JAR external stored procedure to register the archive file with the database. Use CREATE FUNCTION or REPLACE FUNCTION to create a database object for the UDF. Use GRANT to grant privileges to users who are authorized to use the UDF. Call the function.

For more information on …	See …
writing, testing, and debugging source code for an external UDF	SQL External Routine Programming.
data definition statements related to UDFs, including CREATE FUNCTION and REPLACE FUNCTION	SQL Data Definition Language.
invoking a table function in the FROM clause of a SELECT statement	SQL Data Manipulation Language.
archiving and restoring UDFs	Teradata Archive/Recovery Utility Reference.