16.20 - UDF Types - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL External Routine Programming

Advanced SQL Engine
Teradata Database
Release Number
Release Date
April 2020
Content Type
Programming Reference
Publication ID
English (United States)
Teradata Database supports three types of UDFs:
  • Scalar
  • Aggregate and Window Aggregate
  • Table Functions and Operators

Input/output parameter types and supported UDFs

Scalar Functions

Scalar functions take input arguments and return a single value result. Some examples of standard SQL scalar functions are CHARACTER_LENGTH, POSITION, and SUBSTRING.

You can use a scalar function in place of a column name in an expression. When Teradata Database evaluates the expression, it invokes the scalar function. No context is retained after the function completes.

You can also use a scalar function to implement user-defined type (UDT) functionality such as cast, transform, or ordering.

Aggregate Functions

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.

Teradata Database invokes an aggregate function once for each item in the group, passing the detail values of a group through the input arguments. To accumulate summary information, an aggregate function must retain context each time it is called.

You do not need to understand or worry about how to create a group, or how to code an aggregate UDF to deal with groups. Teradata Database automatically takes care of all of those difficult aspects. You only need to write the basic algorithm of combining the data passed in to produce the desired result.

Window Aggregate Functions

You can apply the ordered analytical window feature to a user-defined aggregate function.

Ordered analytical functions provide support for common operations in analytical processing that require an ordered set of rows or use the values from multiple rows in computing a new value.

The window feature provides a way to dynamically define a subset of data, or window, and allows the aggregate function to operate on that window of rows. Without a window specification, aggregate functions return one value for all qualified rows examined, but window aggregate functions return a new value for each of the qualifying rows participating in the query.

Table Functions

A table function is invoked in the FROM clause of an SQL SELECT statement and returns a table, a row at a time in a loop to the SELECT statement. The function can produce the rows of a table from the input arguments passed to it or by reading an external file or message queue.

The number of columns in the rows that a table function returns can be specified dynamically at runtime in the SELECT statement that invokes the table function.

Table functions and table operators cannot execute against fallback data when an AMP is down. Once the AMP returns to service, the query can be submitted.

Table Operators

A table operator is invoked in the FROM clause of an SQL SELECT statement and accepts one or more tables or table expressions as input and generates a table as output. For more information, see Table Operators.