CREATE FUNCTION

>>-CREATE--+-----+---------------------------------------------->
           '-DBA-'
 
>--FUNCTION--function(-+---------------------------------+-)---->
                       |                            (1)  |
                       '-| Routine Parameter List |------'
 
                     (2)
>--| Return Clause |-------------------------------------------->
 
>--+----------------------------------+------------------------->
   |                             (3)  |
   '-SPECIFIC--| Specific Name |------'
 
>--+---------------------------------------+--+---+------------->
   |       .-,-------------------------.   |  '-;-'
   |       V                      (4)  |   |
   '-WITH(---| Routine Modifier |------+-)-'
 
      (5)                       (6)
>--+--------| Statement Block |-----------------+--END FUNCTION-->
   |  (7)                                  (8)  |
   '--------| External Routine Reference |------'
 
>--+--------------------------------------+--------------------->
   |           .-,----------------------. |
   |           V                   (9)  | |
   '-DOCUMENT----| Quoted String |------+-'
 
>--+----------------------------+------------------------------><
   '-WITH LISTING IN 'pathname'-'

Notes:

See Routine Parameter List
See Return Clause
See Specific Name
See Routine Modifier
Stored Procedure Language only
See Statement Block
External routines only
See External Routine Reference
See Quoted String

Element	Description	Restrictions	Syntax
function	Name of new function that is defined here	You must have the appropriate language privileges. See GRANT and Overloading the Name of a Function.	Database Object Name
pathname	Pathname to a file in which compile-time warnings are stored	The specified pathname must exist on the computer where the database resides	The path and filename must conform to your operating-system rules.

Tip:

If you are trying to create a function from text of source code that is in a separate file, use the CREATE FUNCTION FROM statement.

Dynamic Server supports user-defined functions written in these languages:

Stored Procedure Language (SPL)
One of the external languages (C or Java) that Dynamic Server supports (external functions)

When the IFX_EXTEND_ROLE configuration parameter of is set to ON, only users to whom the DBSA grants the built-in EXTEND role can create external functions.

How many values a function can return is language-dependent. Functions written in SPL can return one or more values. External functions written in the C or Java languages must return exactly one value. But a C function can return a collection type, and external functions in queries can return additional values indirectly from OUT parameters (and for the Java language, from INOUT parameters) that Dynamic Server can process as statement-local variables (SVLs).

For information on how this manual uses the terms UDR, function, and procedure as well as recommended usage, see Relationship Between Routines, Functions, and Procedures and Using CREATE PROCEDURE Versus CREATE FUNCTION, respectively.

The entire length of a CREATE FUNCTION statement must be less than 64 kilobytes. This length is the literal length of the statement, including whitespace characters such as blank spaces and tabs.

In ESQL/C, you can use a CREATE FUNCTION statement only within a PREPARE statement. If you want to create a user-defined function for which the text is known at compile time, you must put the text in a file and specify this file with the CREATE FUNCTION FROM statement.

Functions use the collating order that was in effect when they were created. See SET COLLATION for information about using non-default collation.

CREATE FUNCTION

Syntax

Usage