CREATE FUNCTION (SQL Scalar)

This statement is used to define a user-defined SQL scalar function. A scalar function returns a single value each time it is invoked. Specifying a function is generally valid wherever an SQL expression is valid.
Restriction: This topic applies to Windows environments only.

Invocation

This statement can be embedded in an application program or issued interactively. It is an executable statement that can be dynamically prepared only if DYNAMICRULES run behavior is implicitly or explicitly specified.

Authorization

The privilege set defined below must include at least one of the following:

  • The CREATEIN privilege for the schema or all schemas
  • SYSADM or SYSCTRL authority

The authorization ID that matches the schema name implicitly has the CREATEIN privilege on the schema.

Syntax

CREATE FUNCTION function-name ([parameter-declaration[,...]])
RETURNS data-type2 
[LANGUAGE SQL] option-list RETURN-statement

parameter-declaration: 

[parameter-name] parameter-type

parameter-type: 

{data-type [AS LOCATOR] | TABLE LIKE {table-name | view-name} [AS LOCATOR]}

data-type: 

{built-in-data-type | distinct-type-name}

built-in-data-type: 

SMALLINT |
{INTEGER | INT} |
{DECIMAL | DEC | NUMERIC} [integer[,...]] |
{FLOAT [integer] | REAL [PRECISION] | DOUBLE} |
{ {CHARACTER | CHAR} [(integer)] | {CHARACTER | CHAR} VARYING (integer)
| VARCHAR (integer) } 
[FOR {SBCS | MIXED | BIT} DATA] [CCSID {EBCDIC | ASCII}] |
{{CHARACTER | CHAR} LARGE OBJECT | CLOB} [(integer [K|M|G])] } 
[FOR {SBCS | MIXED} DATA] [CCSID {EBCDIC | ASCII}] } |
{BINARY LARGE OBJECT | BLOB} (integer [K|M|G]) |
{GRAPHIC [(integer)] | VARGRAPHIC (integer) | DBCLOB (integer [K|M|G])} [CCSID {EBCDIC | ASCII}] |
{DATE | TIME | TIMESTAMP} |
ROWID

Description

function-name

Names the user-defined function. The name is implicitly or explicitly qualified by a schema name. The combination of name, schema name, the number of parameters, and the data type each parameter (without regard for any length, precision, scale, subtype, or encoding scheme attributes of the data type) must not identify a user-defined function that exists at the current server.

If the function is sourced on an existing function to enable the use of the existing function with a distinct type, the name can be the same name as the existing function. In general, more than one function can have the same name if the function signature of each function is unique.

  • The unqualified form of function-name is an SQL identifier.

    The name must not be any of the following system-reserved keywords even if you specify them as delimited identifiers:

    ALL LIKE UNIQUE
    AND MATCH UNKNOWN
    ANY NOT =
    BETWEEN NULL ¬=
    DISTINCT ONLY <
    EXCEPT OR <=
    EXISTS OVERLAPS ¬<
    FALSE SIMILAR >
    FOR SOME >=
    FROM TABLE ¬>
    IN TRUE <>
    IS TYPE  

    The unqualified function name is implicitly qualified with a schema name according to the following rules:

    • If the statement is embedded in a program, the schema name is the authorization ID in the QUALIFIER bind option when the plan or package was created or last rebound. If QUALIFIER was not specified, the schema name is the owner of the plan or package.
    • If the statement is dynamically prepared, the schema name is the SQL authorization ID in the CURRENT SQLID special register.
  • The qualified form of function-name is an SQL identifier (the schema name) followed by a period and an SQL identifier.

    The schema name can be "SYSTOOLS" if the user who executes the CREATE statement has SYSADM or SYSCTRL privilege. Otherwise, the schema name must not begin with "SYS" unless the schema name is "SYSADM".

The owner of the function is determined by how the CREATE FUNCTION statement is invoked:

  • If the statement is embedded in a program, the owner is the authorization ID of the owner of the plan or package.
  • If the statement is dynamically prepared, the owner is the SQL authorization ID in the CURRENT SQLID special register.

The owner is implicitly given the EXECUTE privilege with the GRANT option for the function.

(parameter-declaration,...)

Specifies the number of input parameters of the function and the data type of each parameter. All the parameters for a function are input parameters and are nullable. There must be one entry in the list for each parameter that the function expects to receive. Although not required, you can give each parameter a name.

A function can have no parameters. In this case, you must code an empty set of parentheses, for example: