Informix Guide to SQL: Syntax SQL Statements

Use the CREATE FUNCTION FROM statement to access a user-defined function. The actual text of the CREATE FUNCTION statement resides in a separate file.

An ESQL/C program cannot directly create a user-defined function. That is, it cannot contain the CREATE FUNCTION statement. However, you can create these functions within an ESQL/C program with the following steps:

1. Create a source file with the CREATE FUNCTION statement.
2. 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 ESQL/C 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 (as in the preceding example), the client application looks for the file in the current directory.

Important: The ESQL/C preprocessor does not process the contents of the file that you specify. It just 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. However, to improve readability of the code, Informix recommends that you match these two statements.

Related Information

Related statements: CREATE FUNCTION, CREATE PROCEDURE, CREATE PROCEDURE FROM, and CREATE ROUTINE FROM