15.00 - User-Defined Functions - Teradata Database

Teradata Database SQL Fundamentals

prodname
Teradata Database
vrm_release
15.00
category
Programming Reference
featnum
B035-1141-015K

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
  • External UDFs
  • 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.