Types of functions
There are several ways to classify functions.
One way to classify functions is as built-in, user-defined, or generated user-defined functions for distinct types.
- Built-in functions are functions that come with the database
manager. These functions provide a single-value result. Built-in functions
include operator functions such as "+", aggregate functions such as
AVG, and scalar functions such as SUBSTR. For a list of the built-in
aggregate and scalar functions and information about these functions,
see Built-in functions.
The built-in functions are part of schema QSYS2. 1
- User-defined functions are functions that are created using
the CREATE FUNCTION statement and registered to the database manager
in catalog table QSYS2.SYSROUTINES and catalog view QSYS2.SYSFUNCS.
For more information, see CREATE FUNCTION.
These functions allow users to extend the function of the database
manager by adding their own or third party vendor function definitions.
A user-defined function is either an SQL, external, or sourced function. An SQL function is defined to the database using only SQL statements. An external function is defined to the database with a reference to an external program or service program that is executed when the function is invoked. A sourced function is defined to the database with a reference to a built-in function or another user-defined function. Sourced functions can be used to extend built-in aggregate and scalar functions for use on distinct types.
A user-defined function resides in the schema in which it was created. The schema cannot be QSYS, QSYS2, or QTEMP.
- Generated user-defined functions for distinct types are
functions that the database manager automatically generates when a
distinct type is created using the CREATE TYPE statement. These functions
support casting from the distinct type to the source type and from
the source type to the distinct type. The ability to cast between
the data types is important because a distinct type is compatible
only with itself.
The generated cast functions reside in the same schema as the distinct type for which they were created. The schema cannot be QSYS, QSYS2, or QTEMP. For more information about the functions that are generated for a distinct type, see CREATE TYPE (distinct).
Another way to classify functions is as aggregate, scalar, or table functions, depending on the input data values and result values.
- An aggregate function receives a set of values for each argument (such as the values of a column) and returns a single-value result for the set of input values. Aggregate functions are sometimes called column functions. Built-in functions and user-defined sourced functions can be aggregate functions.
- A scalar function receives a single value for each argument and returns a single-value result. Built-in functions and user-defined functions can be scalar functions. Generated user-defined functions for distinct types are also scalar functions.
- A table function returns a table for the
set of arguments it receives. Each argument is a single value. A table
function can only be referenced in the FROM clause of a subselect.
A table function can be defined as an external function or as an SQL
function, but a table function cannot be a sourced function.
Table functions can be used to apply SQL language processing power to data that is not stored in the database or to allow access to such data as if it were stored in a table. For example, a table function can read a file, get data from the Web, or access a Lotus Notes® database and return a result table.