UDFs and UDAFs

UDFs and UDAFs differ in the number of rows that they accept as input. A UDF operates on a single row and produces a single row as the output. When you use a UDF in a query, the UDF is called once for each row in the result set. Mathematical and string functions are examples of UDFs. When you create a UDF, you issue the CREATE FUNCTION statement. After you create a UDF, you can use it in the expression of a SQL statement.

A UDAF operates on multiple input rows and produces a single row as output. The COUNT(), MAX(), SUM(), and AVG() aggregate functions are examples of UDAFs. You might use a UDAF in a query with a GROUP BY clause to produce a result set with a separate aggregate value for each combination of values from the GROUP BY clause. When you create UDAFs, use the CREATE AGGREGATE FUNCTION statement. After you create a UDAF, you can use it the expression of a SQL statement.

When you create UDFs and UDAFs, they cannot have the same name as any of the built-in functions.