CREATE FUNCTION FROM statement

Use the CREATE FUNCTION FROM statement to access a user-defined function whose CREATE FUNCTION statement resides in a separate file.

This statement is an extension to the ANSI/ISO standard for SQL. Use this statement with ESQL/C.

Syntax


Element	Description	Restrictions	Syntax
file	Path and filename of a file that contains the full CREATE FUNCTION statement text. Default pathname is current directory.	Must exist and contain exactly one CREATE FUNCTION statement	Must conform to operating-system rules.
file_var	Variable storing value of file	Same as for file	Language specific

Usage

Functions written in the C or Java™ language are called external functions. When the IFX_EXTEND_ROLE configuration parameter is set to ON, only users who have been granted the built-in EXTEND role can create external functions.

programs cannot directly create user-defined functions. That is, they cannot contain the CREATE FUNCTION statement.

To create these functions within programs:

Create a source file with the CREATE FUNCTION statement.
Use the CREATE FUNCTION FROM statement to send the contents of this source file to the database server for execution.
The file that you specify in the file parameter can contain only one CREATE FUNCTION statement.

For example, suppose that the following CREATE FUNCTION statement is in a separate file, called del_ord.sql:

CREATE FUNCTION delete_order( p_order_num INT) RETURNING INT, INT;
   DEFINE item_count INT;
   SELECT count(*) INTO item_count FROM items
      WHERE order_num = p_order_num;
   DELETE FROM orders WHERE order_num = p_order_num;
   RETURN p_order_num, item_count;
END FUNCTION;

In the program, you can access the delete_order( ) SPL function with the following CREATE FUNCTION FROM statement:

EXEC SQL create function from 'del_ord.sql';

If you are not sure whether the UDR in the file is a user-defined function or a user-defined procedure, use the CREATE ROUTINE FROM statement.

The filename that you provide is relative. If you provide a simple filename with no pathname (as in the preceding example), the client application looks for the file in the current directory.

Important: The preprocessor does not process the contents of the file that you specify. It only sends the contents to the database server for execution. Therefore, there is no syntactic check that the file that you specify in CREATE FUNCTION FROM actually contains a CREATE FUNCTION statement. To improve readability of the code, however, it is recommended that you match these two statements.