CREATE FUNCTION statement

Use the CREATE FUNCTION statement to create a user-defined function, to register an external function, or to write and register an SPL function.

This statement is an extension to the ANSI/ISO standard for SQL.

Syntax

>>-CREATE--+--------------+--+---------+------------------------------------------>
           |    (1)       |  |  (2)    |
           '-OR REPLACE---'  '-DBA-----'   
 
>--FUNCTION--+---------------+--function(-+--------------------------------+-)-->
             '-IF NOT EXISTS-'            |                            (3) |     
                                          '-| Routine Parameter List |-----'     


>--FUNCTION--+---------------+--function(-+--------------------------------+-)-->
             '-IF NOT EXISTS-'            |                            (3) |     
                                          '-| Routine Parameter List |-----'     

>--+-----------------------------------------------------------------+-->
   |                        (4)                                      |   
   '-| REFERENCING Clause |------FOR -+--------------+--table_object-'   
                                      '-'--owner--'.-'                   

                     (5)                                        
>--| Return Clause |------+---------------------------------+--->
                          |                             (6) |   
                          '-SPECIFIC--| Specific Name |-----'   

>--+--------------------------------------+--+---+-------------->
   |       .-,------------------------.   |  '-;-'   
   |       V                      (7) |   |          
   '-WITH(---| Routine Modifier |-----+-)-'          

      (8)                      (9)                               
>--+-------| Statement Block |-----------------+--END FUNCTION-->
   |  (10)                                 (11) |                 
   '-------| External Routine Reference |------'                 

>--+--------------------------------------+--------------------->
   |           .-,----------------------. |   
   |           V                   (12) | |   
   '-DOCUMENT----| Quoted String |------+-'   

>--+----------------------------+------------------------------><
   '-WITH LISTING IN 'pathname'-'

Note:

See OR REPLACE Clause
See DBA Keyword and Privileges on the Procedure
Stored Procedure Language only
See Routine Parameter List
See The REFERENCING and FOR Clauses
See Return Clause
See Specific Name
See Routine modifier
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 statement and Overloading the Name of a Function.	Identifier
owner	Owner of table_object	Must own table_object	Owner 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.
table_object	Name or synonym of the table or view whose triggers can call function	Must exist in the local database	Identifier

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.

Usage

HCL OneDB™ supports user-defined functions written in these languages:

The HCL OneDB stored procedure language (SPL).
One of the external languages (C or Java™) that HCL OneDB supports (external functions).

When the IFX_EXTEND_ROLE configuration parameter is set to ON, only users to whom the DBSA grants the built-in EXTEND role can create external functions. Additional requirements for using the CREATE FUNCTION statement are identified in the topic Privileges necessary for using CREATE FUNCTION.

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 SPL and Java languages, from INOUT parameters) that HCL OneDB can process as statement-local variables (SLVs).

Return values from OUT and INOUT parameters of an SPL function can be processed as SLVs. You can also use local variables or parameters of an SPL routine to retrieve values from SPL or C routines that have OUT or INOUT parameters.

For information about how this document 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.

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.

If you include the optional IF NOT EXISTS keywords, the database server takes no action (rather than sending an exception to the application) if a function of the specified name is already registered in the current database. (Because the identifier of a function can be overloaded, it might be unnecessary to include these keywords, if the database server can resolve the argument list of the new function as different from that of any other function of the same name in the current database.)

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