SQL Reference

CREATE FUNCTION (Source or Template)

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

function-name
Names the function or function template being defined. It is a qualified or unqualified name that designates a function. The unqualified form of function-name is an SQL identifier (with a maximum length of 18). In dynamic SQL statements, the CURRENT SCHEMA special register is used as a qualifier for an unqualified object name. In static SQL statements the QUALIFIER precompile/bind option implicitly specifies the qualifier for unqualified object names. The qualified form is a schema-name followed by a period and an SQL identifier.

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.

(data-type,...)
Identifies the number of input parameters of the function or function template, and specifies the data type of each parameter. One entry in the list must be specified for each parameter that the function or function template will expect to receive. No more than 90 parameters are allowed. If this limit is exceeded, an error (SQLSTATE 54023) is raised.

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.

parameter-name
Specifies an optional name for the parameter that is distinct from the names of all other parameters in this function.

data-type1
Specifies the data type of the parameter.

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.

RETURNS
This mandatory clause identifies the output of the function or function template.

data-type2
Specifies the data type of the output.

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.

SPECIFIC specific-name
Provides a unique name for the instance of the function that is being defined. This specific name can be used when sourcing on this function, dropping the function, or commenting on the function. It can never be used to invoke the function. The unqualified form of specific-name is an SQL identifier (with a maximum length of 18). The qualified form is a schema-name followed by a period and an SQL identifier. The name, including the implicit or explicit qualifier, must not identify another function instance that exists at the application server; otherwise an error (SQLSTATE 42710) is raised.

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.

SOURCE
Specifies that the function being created is to be implemented by another function (the source function) already known to the database manager. The source function can be either a built-in function 71 or a previously created user-defined scalar function.

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.

function-name
Identifies the particular function that is to be used as the source and is valid only if there is exactly one specific function in the schema with this function-name. This syntax variant is not valid for a source function that is a built-in 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.

SPECIFIC specific-name
Identifies the particular user-defined function that is to be used as the source, by the specific-name either specified or defaulted to at function creation time. This syntax variant is not valid for a source function that is a built-in function.

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.

function-name (data-type,...)
Provides the function signature, which uniquely identifies the source function. This is the only valid syntax variant for a source function that is a built-in function.

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.

function-name
Gives the function name of the source function. If an unqualified name is provided, then the schemas of the user's SQL path are considered.

data-type
Must match the data type that was specified on the CREATE FUNCTION statement in the corresponding position (comma separated).

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.

AS TEMPLATE
Indicates that this statement will be used to create a function template, not a function with executable code.

Rules

Notes

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


Footnotes:

71
With the exception of COALESCE, NODENUMBER, NULLIF, PARTITION, TYPE_ID, TYPE_NAME, TYPE_SCHEMA and VALUE.


[ Top of Page | Previous Page | Next Page ]