Invoking a UDR with an EXECUTE Statement

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

Invoking a Function

Suppose result is a 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 nFact (arg1);

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)) ...

Invoking a Procedure

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

EXECUTE PROCEDURE log_compare (arg1, arg2)