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.

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.