User-Defined Functions
SQL provides a set of useful functions, but they might not satisfy all of the particular requirements you have to process your data.
Teradata Database supports two types of user-defined functions (UDFs) that allow you to extend SQL by writing your own functions:
SQL UDFs
SQL UDFs allow you to encapsulate regular SQL expressions in functions and then use them like standard SQL functions.
Rather than coding commonly used SQL expressions repeatedly in queries, you can objectize the SQL expressions through SQL UDFs.
Moving complex SQL expressions from queries to SQL UDFs makes the queries more readable and can reduce the client/server network traffic.
External UDFs
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. |
Usage
To create and use an SQL UDF, follow these steps:
1 Use CREATE FUNCTION or REPLACE FUNCTION to define the UDF.
2 Use GRANT to grant privileges to users who are authorized to use the UDF.
3 Call the function.
Here is a synopsis of the steps you take to develop, compile, install, and use an external UDF:
1 Write, test, and debug the C, C++, or Java code for the UDF.
2 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.
3 Use CREATE FUNCTION or REPLACE FUNCTION to create a database object for the UDF.
4 Use GRANT to grant privileges to users who are authorized to use the UDF.
5 Call the function.
Related Topics
|
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. |