This statement is used to:
Invocation
This statement can be embedded in an application program or issued through the use of dynamic SQL statements. It is an executable statement that can be dynamically prepared. However, if the bind option DYNAMICRULES BIND applies, the statement cannot be dynamically prepared (SQLSTATE 42509).
Authorization
The privileges held by the authorization ID of the statement must include at least one of the following:
If the authorization ID has insufficient authority to perform the operation, an error (SQLSTATE 42502) is raised.
No authority is required on a function referenced in the SOURCE clause.
Syntax
>>-CREATE FUNCTION--function-name------------------------------->
>----(--+------------------------------------------+---)---*---->
| .-,----------------------------------. |
| V | |
'----+-----------------+---data-type1---+--'
'-parameter-name--'
>----RETURNS--data-type2---*----+--------------------------+---->
'-SPECIFIC--specific-name--'
>----*---------------------------------------------------------->
>-----+-SOURCE--+-function-name--------------------------------+-+>
| +-SPECIFIC--specific-name----------------------+ |
| '-function-name--(--+-------------------+---)--' |
| | .-,-----------. | |
| | V | | |
| '----data-type---+--' |
'-AS TEMPLATE----------------------------------------------'
>----*---------------------------------------------------------><
Description
The name, including the implicit or explicit qualifiers, together with the number of parameters and the data type of each parameter (without regard for any length, precision or scale attributes of the data type) must not identify a function or function template described in the catalog (SQLSTATE 42723). The unqualified name, together with the number and data types of the parameters, while of course unique within its schema, need not be unique across schemas.
If a two-part name is specified, the schema-name cannot begin with "SYS". Otherwise, an error (SQLSTATE 42939) is raised.
A number of names used as keywords in predicates are reserved for system use, and may not be used as a function-name (SQLSTATE 42939). The names are SOME, ANY, ALL, NOT, AND, OR, BETWEEN, NULL, LIKE, EXISTS, IN, UNIQUE, OVERLAPS, SIMILAR, MATCH and the comparison operators as described in Basic Predicate.
When naming a user-defined function that is sourced on an existing function with the purpose of supporting the same function with a user-defined distinct type, the same name as the sourced function may be used. This allows users to use the same function with a user-defined distinct type without realizing that an additional definition was required. In general, the same name can be used for more than one function if there is some difference in the signature of the functions.
It is possible to register a function or function template that has no parameters. In this case, the parentheses must still be coded, with no intervening data types. For example,
CREATE FUNCTION WOOFER() ...
No two identically-named functions or function templates within a schema are permitted to have exactly the same type for all corresponding parameters. (This restriction applies also to a function and function template within a schema that have the same name.) Lengths, precisions and scales are not considered in this type comparison. Therefore CHAR(8) and CHAR(35) are considered to be the same type, as are DECIMAL(11,2) and DECIMAL (4,3). There is some further bundling of types that causes them to be treated as the same type for this purpose, such as DECIMAL and NUMERIC. A duplicate signature raises an SQL error (SQLSTATE 42723).
For example, given the statements:
CREATE FUNCTION PART (INT, CHAR(15)) ... CREATE FUNCTION PART (INTEGER, CHAR(40)) ... CREATE FUNCTION ANGLE (DECIMAL(12,2)) ... CREATE FUNCTION ANGLE (DEC(10,7)) ...
the second and fourth statements would fail because they are considered to be a duplicate functions.
With a sourced scalar function any valid SQL data type may be used provided it is castable to the type of the corresponding parameter of the function identified in the SOURCE clause (for the definition of castable, see Casting Between Data Types). A REF(type-name) data type cannot be specified as the data type of a parameter (SQLSTATE 42997).
Since the function is sourced, it is not necessary (but still permitted) to specify length, precision, or scale for the parameterized data types. Instead, empty parentheses may be used (for example CHAR() may be used). A parameterized data type is any one of the data types that can be defined with a specific length, scale, or precision. The parameterized data types are the string data types and the decimal data types.
Any valid SQL data type is valid, as is a distinct type, provided it is castable from the result type of the source function (for the definition of castable, see Casting Between Data Types).
The parameter of a parameterized type need not be specified, as above for parameters of a sourced function. Instead, empty parentheses may be used, for example, VARCHAR().
Also see page (UDFRULE) for additional considerations and rules that apply to the specification of the data type in the RETURNS clause when the function is sourced on another.
The specific-name may be the same as an existing function-name.
If no qualifier is specified, the qualifier that was used for function-name is used. If a qualifier is specified, it must be the same as the explicit or implicit qualifier of function-name or an error (SQLSTATE 42882) is raised.
If specific-name is not specified, a unique name is generated by the database manager. The unique name is SQL followed by a character timestamp, SQLyymmddhhmmssxxx.
The SOURCE clause may be specified only for scalar or column functions; it may not be specified for table functions.
The SOURCE clause provides the identity of the other function.
If an unqualified name is provided, then the authorization ID's current SQL path (the value of the CURRENT PATH special register) is used to locate the function. The first schema in the function path that has a function with this name is selected.
If no function by this name exists in the named schema or if the name is not qualified and there is no function with this name in the function path, an error (SQLSTATE 42704) is raised. If there is more than one specific instance of the function in the named or located schema, an error (SQLSTATE 42725) is raised.
If an unqualified name is provided, then the authorization ID's current SQL path is used to locate the function. The first schema in the function path that has a function with this specific name is selected.
If no function by this specific-name exists in the named schema or if the name is not qualified and there is no function with this specific-name in the SQL path, an error (SQLSTATE 42704) is raised.
The rules for function resolution (as described in Function Resolution) are applied to select one function from the functions with the same function name, given the data types specified in the SOURCE clause. However, the data type of each parameter in the function selected must have the exact same type as the corresponding data type specified in the source function.
It is not necessary to specify the length, precision or scale for the parameterized data types. Instead an empty set of parentheses may be coded to indicate that these attributes are to be ignored when looking for a data type match. For example, DECIMAL() will match a parameter whose data type was defined as DECIMAL(7,2)).
FLOAT() cannot be used (SQLSTATE 42601) since the parameter value indicates different data types (REAL or DOUBLE).
However, if length, precision, or scale is coded, the value must exactly match that specified in the CREATE FUNCTION statement. This can be useful in assuring that the exact intended function will be used. Also note that synonyms for data types will be considered a match (for example DEC and NUMERIC will match).
A type of FLOAT(n) does not need to match the defined value for n since 0<n<25 means REAL and 24<n<54 means DOUBLE. Matching occurs based on whether the type is REAL or DOUBLE.
If no function with the specified signature exists in the named or implied schema, an error (SQLSTATE 42883) is raised.
Rules
An error (SQLSTATE 42613) will result from violation of these rules.
Instead, empty parentheses may be specified as part of the data type (for example: VARCHAR()) in order to indicate that the length/precision/scale will be the same as those of the source function, or determined by the casting.
However, if length, precision, or scale is specified then the value in CF is checked against the corresponding value in SF as outlined below for input parameters and returns value.
Note that this rule provides no guarantee against an error occurring when CF is used. An argument that matches the data type and length or precision attributes of a CF parameter may not be assignable if the corresponding SF parameter has a shorter length or less precision. In general, parameters of CF should not have length or precision attributes that are greater than the attributes of the corresponding SF parameters.
Note that this rule provides no guarantee against an error occurring when CF is used. A result value that matches the data type and length or precision attributes of the SF RETURNS data type may not be assignable if the CF RETURNS data type has a shorter length or less precision. Caution should be used when choosing to specify the RETURNS data type of CF as having length or precision attributes that are less than the attributes of the SF RETURNS data type.
Notes
The counterpart can be a function (scalar or source) or a function template. If the user creates a function and the required mapping, then, each time a query that specifies the function is processed, DB2 (1) compares strategies for invoking it with strategies for invoking the data source function, and (2) invokes the function that is expected to require less overhead.
If the user creates a function template and the mapping, then, each time a query that specifies the template is processed, DB2 invokes the data source function that it maps to, provided that an access plan for invoking this function exists. Refer to the Application Development Guide for more information about controlling the overhead of invoking functions in a federated system.
Examples
Example 1: Some time after the creation of Pellow's original CENTRE external scalar function, another user wants to create a function based on it, except this function is intended to accept only integer arguments.
CREATE FUNCTION MYCENTRE (INTEGER, INTEGER)
RETURNS FLOAT
SOURCE PELLOW.CENTRE (INTEGER, FLOAT)
Example 2: You have created a distinct type HATSIZE which is based on the built-in INTEGER data type, and now would find it useful to have an AVG function to compute the average hat size of different departments. This is easily done as follows:
CREATE FUNCTION AVG (HATSIZE) RETURNS (HATSIZE)
SOURCE SYSIBM.AVG (INTEGER)
The creation of the distinct type has generated the required cast function, allowing the cast from HATSIZE to INTEGER for the argument and from INTEGER to HATSIZE for the result of the function.
Example 3: In a federated system, a user wants to invoke an Oracle UDF that returns table statistics in the form of values with double precision floating-points. The federated server can recognize this function only if there is a mapping between the function and a federated database counterpart. But no such counterpart exists. The user decides to provide one in the form of a function template, and to assign this template to a schema called NOVA. The user uses the following code to register the template with the federated server; for the user's code for the mapping, refer to Examples.
CREATE FUNCTION NOVA.STATS (DOUBLE, DOUBLE)
RETURNS DOUBLE
AS TEMPLATE
Example 4: In a federated system, a user wants to invoke an Oracle UDF that returns the dollar amounts that employees of a particular organization earn as bonuses. The federated server can recognize this function only if there is a mapping between the function and a federated database counterpart. No such counterpart exists; thus, the user creates one in the form of a function template. The user uses the following code to register this template with the federated server; for the user's code for the mapping, refer to Examples.
CREATE FUNCTION BONUS ()
RETURNS DECIMAL (8,2)
AS TEMPLATE