# Routines: Functions

Functions are relationships between sets of input data values and a set of result values. They enable you to extend and customize SQL. Functions are invoked from within elements of SQL statements such as a select-list or a FROM clause.

There are four types of functions:

- Aggregate functions
- Scalar functions
- Row functions
- Table functions

- Aggregate functions
- Also called a column function, this type of function returns a
scalar value that is the result of an evaluation over a set of like
input values. The similar input values can, for example, be specified
by a column within a table, or by tuples in a VALUES clause. This
set of values is called the argument set. For example, the following
query finds the total quantity of bolts that are in stock or on order
by using the SUM aggregate function:
`SELECT SUM (qinstock + qonorder) FROM inventory WHERE description LIKE '%Bolt%'`

- Scalar functions
- A scalar function is a function that, for each set of one or more
scalar parameters, returns a single scalar value. Examples of scalar
functions include the LENGTH function, and the SUBSTR function. Scalar
functions can also be created that do complex mathematical calculations
on function input parameters. Scalar functions can be referenced
anywhere that an expression is valid within an SQL statement, such
as in a select-list, or in a FROM clause. The following example shows
a query that references the built-in LENGTH scalar function:
`SELECT lastname, LENGTH(lastname) FROM employee`

- Row functions
- A row function is a function that for each set of one or more scalar parameters returns a single row. Row functions can only be used as a transform function mapping attributes of a structured type into built-in data type values in a row.
- Table functions
- Table functions are functions that for a group of sets of one
or more parameters, return a table to the SQL statement that references
it. Table functions can only be referenced in the FROM clause of a
SELECT statement. The table that is returned by a table function can
participate in joins, grouping operations, set operations such as
UNION, and any operation that could be applied to a read-only view.
The following example demonstrates an SQL table function that updates
an inventory table and returns the result set of a query on the updated
inventory table:
`CREATE FUNCTION updateInv(itemNo VARCHAR(20), amount INTEGER) RETURNS TABLE (productName VARCHAR(20), quantity INTEGER) LANGUAGE SQL MODIFIES SQL DATA BEGIN ATOMIC UPDATE Inventory as I SET quantity = quantity + amount WHERE I.itemID = itemNo; RETURN SELECT I.itemName, I.quantity FROM Inventory as I WHERE I.itemID = itemNo; END`

Functions provide support for the following features:

- Moderate support for SQL statement execution
- Parameter support for input parameters and scalar or aggregate function return values
- Efficient compilation of function logic into queries that reference functions
- External functions provide support for storing intermediate values between the individual function sub-invocations for each row or value

There are built-in functions that are ready-to-use, or users can create user-defined functions. Functions can be implemented as SQL functions or as external functions. SQL functions can be either compiled or inlined. Inlined functions perform faster than compiled functions, but can execute only a subset of the SQL PL language. See the CREATE FUNCTION statement for more information.