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 |-----'        

>--+-----------------------------------------------------------------+-->
   |                        (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:
  1. See OR REPLACE Clause
  2. See DBA Keyword and Privileges on the Procedure
  3. Stored Procedure Language only
  4. See Routine Parameter List
  5. See The REFERENCING and FOR Clauses
  6. See Return Clause
  7. See Specific Name
  8. See Routine modifier
  9. See Statement Block
  10. External routines only
  11. See External Routine Reference
  12. 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 Informix® supports user-defined functions written in these languages:
  • The HCL Informix® stored procedure language (SPL).
  • One of the external languages (C or Java™) that Informix® 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 Informix® 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.