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.
1 Built-in functions are implemented
internally by the database manager, so an associated program or service
program object does not exist for a
built-in function. Furthermore,
the catalog does not contain information about
built-in functions.
However,
built-in functions can be treated as if they exist
in QSYS2 and a
built-in function name can be qualified with
QSYS2.