Types of UDXs

A UDX can be of one of the following types:

UDSF

A scalar function (that is, a function that computes a single result value from one or more input values) such as upper(), sqr(), or length(). Its input values can include literals or non-literal expressions containing columns from other tables.

A UDSF is the most commonly used type of UDX. A UDSF is usually used in the SELECT and WHERE clauses of a SQL statement. In the following SQL statement, the UDSF named "testudf" is shown in the SELECT and WHERE clauses of the statement:

select x, y, testudf(x,y) from foo where testudf(x,y) > 100

UDTF

A table function (that is, a function that returns a table shape of zero or more rows with columns that have names and types). Its input values can include literals or non-literal expressions containing columns from other tables. Use table functions for tasks such as expanding data from one row into many rows.

A UDTF is used in the FROM clause of a SQL statement. This enables a UDTF to return several columns or rows for each input row. The following SQL statement invokes a table function named tftest, which passes the values foo.x and foo.y as the first and second arguments and returns columns a, b, and c:

select foo.x, foo.y, tf.a, tf.b, tf.c 
  from foo, table(testtf(foo.x, foo.y)) tf 
  where foo.x > 1000;

UDAF

An aggregate function (that is, a function that computes a single result value from the values in a column) such as count(), sum(), avg(), max(), or min(). An aggregate function can be invoked only for one or more columns of a table, not for a set of literal values. The results of intermediate aggregation are stored in state values.

A UDAF is used in the GROUP BY clause of a SQL statement.