Teradata Database supports three types of Java UDFs.
| Java UDF Type | Description |
|---|---|
| Scalar | 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. |
| 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. 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. |
| Table | 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. |