UDXs

Netezza's user-defined function capability was implemented to extend SQL and provide advanced, custom functionality. Netezza’s support for user-defined functions generally follows the model that is used in the 2003 SQL Standard for SQL Invoked Routines. The term UDX is used as a generic reference to Netezza's user-defined scalar, table, and aggregate functions. UDXs provide functionality corresponding to the three categories of built-in functions. For more information, see SQL functions.

Table 1.
SQL function	UDX function
Scalar functions	User-defined functions or UDFs
Table functions	User-defined table functions or UDTFs
Aggregate functions	User-defined aggregates or UDAs

UDXs allow you to create these custom functions to perform specific types of analysis for your business reporting and data queries. User-defined functions and aggregates must be written in C++. While they can use the full standard C library (LIBC), they should avoid interprocess communication (IPC) calls and other low-level operations.

User-Defined Functions

A user-defined function (UDF) is user-supplied code that is executed by the Netezza system in response to SQL invocation syntax. A user-defined function is a scalar function; that is, it returns one value for each input row.

A UDF invocation can appear anywhere inside a SQL statement where a built-in function can appear, including restrictions (WHERE clauses), join conditions, projections (SELECT from lists), and HAVING conditions. A UDF can accept zero or more input values but produces one output value. Input values to a UDF can be literals, column references, or expressions. The data types of inputs and output must be Netezza built-in data types.

User-Defined Table Functions

A user-defined table function (UDTF) is a function that can be invoked in a FROM clause of a SQL statement. A table function can appear in a SQL clause at almost location where a table normally appears. It returns a table shape with columns that have names and types. Unlike the scalar nature of a UDF, a table function can return zero or more rows per input row. A table function can be invoked with arguments, including literals and non-literal expressions containing columns from other tables.

Table functions can be used for tasks such as expanding data from one row into many rows, producing summaries by combining data from many rows, creating custom summaries in table form, or performing “unpivot” operations such as combining the data from several related tables into a new combined table.

Note: A UDTF should be invoked with at least one argument.

User-Defined Aggregates

A user-defined aggregate (UDA) is user-supplied code that implements, on the Netezza system, the various phases of aggregate evaluation, such as initialization, accumulation, and merging.

UDAs provide new types of aggregation functions to expand upon built-in aggregates such as count(), sum(), avg(), max(), or min(). While UDAs can take multiple arguments, in terms of output they are scalar and produce only one output value. UDAs can be used in a SQL statement anywhere a built-in aggregate can appear as either grand, grouped, or windowed aggregates.