Extending Informix Dynamic Server 2000 Running a User-Defined Routine

This section describes the ways that you can invoke a user-defined routine:

• In an SQL statement
• In an SPL routine, with the CALL statement

You can invoke a user-defined routine from within an SQL statement in the following ways:

• You can directly invoke a UDR with the EXECUTE FUNCTION or EXECUTE PROCEDURE statement.
• You can invoke a user-defined function within an expression.

For details about the syntax of the EXECUTE FUNCTION and EXECUTE PROCEDURE statements, see the Informix Guide to SQL: Syntax. For more information about creating UDRs, refer to Chapter 4, Developing a User-Defined Routine.

Suppose result is a function variable of type INTEGER. The following example shows how to register and invoke a C user-defined function called nFact() that returns N-factorial (n!):

CREATE FUNCTION nFact(arg1 n)
	RETURNING INTEGER;
	SPECIFIC nFactorial
	WITH (HANDLESNULLS, NOT VARIANT)
	EXTERNAL NAME 	"/usr/lib/udtype2.so(nFactorial)"
	LANGUAGE C;
EXECUTE FUNCTION equal (arg1) INTO result;

The INTO clause must always be present when you invoke a user-defined function with the EXECUTE FUNCTION statement.

Important: You cannot use the EXECUTE FUNCTION statement to invoke a user-defined function that contains an OUT parameter. Using a SELECT Statement in a Function Argument

As another example, suppose you create the following type hierarchy and functions:

CREATE ROW TYPE emp_t 	
        (name VARCHAR(30), emp_num INT, salary DECIMAL(10,2));
CREATE ROW TYPE trainee_t (mentor VARCHAR(30)) UNDER emp_t;
CREATE TABLE trainee OF TYPE trainee_t;
INSERT INTO  trainee VALUES ('sam', 1234, 44.90, 'joe');

CREATE FUNCTION func1 (arg1 trainee_t) RETURNING row;
DEFINE newrow trainee_t;
LET newrow = ROW( 'sam', 1234, 44.90, 'juliette');
RETURN newrow;
END FUNCTION;

The following EXECUTE FUNCTION statement invokes the func1() function, which has an argument that is a query that returns a row type:

EXECUTE FUNCTION 

	func1 ((SELECT * from trainee where emp_num = 1234)) ...


Important: When you use a query for the argument of a user-defined function invoked with the EXECUTE FUNCTION statement, you must enclose the query in another set of parentheses.

The following EXECUTE PROCEDURE statement invokes the log_compare() function:

EXECUTE PROCEDURE log_compare (arg1, arg2) 

The INTO clause is never present when you invoke a user-defined procedure with the EXECUTE PROCEDURE statement because a procedure does not return a value.

You can invoke a user-defined function in an expression in the select list of a SELECT statement, or in the WHERE clause of an INSERT, SELECT, UPDATE, or DELETE statement.

For example, with the factorial function described in Invoking a Function, you might write the following SELECT statement:

SELECT * FROM tab_1 WHERE nFact(col1) > col3

Functions that are bound to specific operators get invoked automatically without explicit invocation. Suppose an equal() function exists that takes two arguments of type1 and returns a Boolean. If the equal operator (=) is used for comparisons between two columns, col_1 and col_2, that are of type1 the equal() function gets invoked automatically. For example, the following query implicitly invokes the appropriate equal() function to evaluate the WHERE clause:

SELECT * FROM tab_1 
WHERE col_1 = col_2

The preceding query evaluates as though it had been specified as follows:

SELECT * FROM tab_1 
WHERE equal (col_1, col_2)

You use the CALL statement only to invoke a UDR from within an SPL program. You can use CALL to invoke both user-defined functions and user-defined procedures, as follows:

• When you invoke a user-defined function with the CALL statement, you must include a RETURNING clause and the name of the value or values that the function returns.

The following statement invokes the equal() function:

CALL equal (arg1, arg2) RETURNING result

You cannot use the CALL statement to invoke a user-defined function that contains an OUT parameter.

• A RETURNING clause is never present when you invoke a user-defined procedure with the CALL statement because a procedure does not return a value.

The following CALL statement invokes the log_compare() procedure:

CALL log_compare (arg1, arg2)