Extending Informix Dynamic Server 2000 Developing a User-Defined Routine

The database server recognizes the following SQL statements for the registration of user-defined routines in the database:

• The CREATE FUNCTION statement registers UDRs that return a value.
• The CREATE PROCEDURE statement registers UDRs that do not return a value.

1. Ensure you have the correct privileges to register a UDR.
2. Use a CREATE FUNCTION or CREATE PROCEDURE statement to register the UDR:
• For SPL routines, the statement lists the routine code and then compiles and registers the routine.
• For external routines, the statement specifies the location of the routine code (with an EXTERNAL NAME clause) and registers the routine.

A user must have the following privileges to issue a CREATE FUNCTION or CREATE PROCEDURE statement that registers a user-defined routine in the database:

• Database-level privilege
• Language-level privilege

After you register the UDR, you can assign routine-level privileges.

Database-level privileges control the ability to extend the database by registering or dropping a user-defined routine. The following users qualify to register a new routine in the database:

• Any user with the DBA privilege can register a routine with or without the DBA keyword in the CREATE FUNCTION or CREATE PROCEDURE statement.
• A non-DBA user needs the Resource privilege to register a routine.

The creator has owner privileges on the routine. A user who does not have the DBA privilege cannot use the DBA keyword in the CREATE FUNCTION or CREATE PROCEDURE statement to register the routine.

Tip: For an explanation of the DBA keyword, see Executing a UDR as DBA.

A DBA must grant the Resource privilege required for any non-DBA user to create a routine. The DBA can revoke the Resource privilege, which prevents that user from creating additional routines.

A DBA or the routine owner can cancel the registration with the DROP ROUTINE, DROP FUNCTION or DROP PROCEDURE statement. A DBA or routine owner can register a modification to the routine with the ALTER ROUTINE, ALTER FUNCTION, or ALTER PROCEDURE statement.

The language-level Usage privilege controls the ability to write a user-defined routine in a particular UDR language. This privilege needs to be granted by user informix or by another user who been granted DBA privilege with WITH GRANT OPTION.

UDR languages have the following GRANT and REVOKE requirements for the Usage privilege:

• The DBA can grant or revoke the Usage privilege to any language that the database server supports.
• Another user can grant the Usage privilege if the DBA applied the WITH GRANT keywords in the GRANT EXECUTE ON statement.

The following GRANT statement grants Usage privilege on UDRs written in Java to the user named dorian:

GRANT USAGE ON LANGUAGE JAVA TO dorian

By default, the database server:

• does not grant Usage privilege on external languages to PUBLIC.
• does grants Usage privilege on SPL to PUBLIC.

For more information, see the description of privileges in the Informix Guide to Database Design and Implementation and the description of the GRANT statement in the Informix Guide to SQL: Syntax

When you register a user-defined routine, you automatically receive Execute privilege on that routine. Execute privilege allows you to invoke the user-defined routine. For information about allowing other users to execute your routine, see Assigning Execute Privilege to a Routine.

You write an SPL routine in SPL, the internal language of the database server. For an SPL routine, the CREATE FUNCTION or CREATE PROCEDURE statement performs the following tasks:

• Parses and optimizes all SQL statements, if possible

The database server puts the SQL statements in an execution plan. An execution plan is a structure that enables the database server to store and execute the SQL statements efficiently.

The database server optimizes each SQL statement within the SPL routine and includes the selected query plan in the execution plan. For more information on SPL routine optimization, refer to Optimizing an SPL Routine.

• Builds a dependency list

A dependency list contains items that the database server checks to decide if an SPL routine needs to be reoptimized at execution time. For example, the database server checks for the existence of all tables, indexes, and columns involved in the query.

• Parses SPL statements and convert them to p-code

The term p-code refers to pseudocode that an interpreter can execute quickly.

• Converts the p-code, execution plan, and dependency list to ASCII format

The database server stores these ASCII formats as character columns in the system catalog tables, sysprocbody and sysprocplan.

• Stores information about the procedure, such as routine name parameters, and modifiers, in the sysprocedures system catalog table
• Stores permissions for the procedure in the sysprocauth system catalog table

For information on how to optimize an SPL routine, see Chapter 13, Improving UDR Performance.

For a summary of the UDR information in the system catalog tables, refer to Reviewing Information about User-Defined Routines

Figure 4-1 shows the parts of a CREATE FUNCTION statement that registers a user-defined function called abs_eq().

Figure 4-1 Registering an SPL Function

When you create an SPL function, you can specify optional routine modifiers that affect how the database server executes the function. SPL procedures do not allow routine modifiers. Use the WITH clause of the CREATE FUNCTION statement to list function modifiers. SPL functions allow the following routine modifiers:

• ITERATOR
• INTERNAL
• NOT VARIANT
• INTERNAL

In Figure 4-1, the NOT VARIANT modifier indicates that the abs_eq() SPL function is written so that it always returns the same value when passed the same arguments.

For more information about the CREATE FUNCTION and CREATE PROCEDURE statements and about the syntax of SPL, refer to the Informix Guide to SQL: Syntax. For information about creating using SPL routines, refer to the Informix Guide to SQL: Tutorial.

You can write an external routine in an external language that the database server supports. Once you have a shared-object file for the external routine, you register the routine and its shared-object file in the database.

The CREATE FUNCTION and CREATE PROCEDURE statements provide the location of the external routine, as follows:

• For UDRs written in C, the location is the pathname of the shared-object module that contains the external routine.
• For UDRs written in Java, the location is the JAR file.

For example, Figure 4-2 shows a CREATE FUNCTION statement that registers a user-defined function called abs_eq(), whose corresponding C function is in a shared-object file called abs.so.

Figure 4-2 Registering an External Function

The format of the external name often depends on the language in which the UDR is written. You specify the path of the shared-object file in the CREATE FUNCTION (or CREATE PROCEDURE) statement when you register the external routine.

Figure 4-2 shows a CREATE FUNCTION that specifies a shared-object file for a C user-defined function.

Important: Before you can register an external routine, you must have Usage permission for the language in which you wrote the routine. For more information on the Usage privilege, see Language-Level Privilege.

Registering an External Function

To create an external function, write the body of the function in a language other than SPL and then use the CREATE FUNCTION statement to register the function. The RETURNING clause of CREATE FUNCTION specifies the return data type of the external function.

Important: To register an external UDR that is a function, you must use the CREATE FUNCTION statement. You cannot use the CREATE PROCEDURE statement to create an external function.

For example, the following CREATE FUNCTION statement registers an external function called equal() that takes two arguments, arg1 and arg2, of data type udtype1 and returns a single value of the data type BOOLEAN:

CREATE FUNCTION equal (arg1 udtype1, arg2 udtype1)
RETURNING BOOLEAN
EXTERNAL NAME 
"/usr/lib/udtype1/lib/libbtype1.so(udtype1_equal)"

LANGUAGE C
END FUNCTION;

In the preceding example, the END FUNCTION keywords are optional. For more information, see the CREATE FUNCTION statement in the Informix Guide to SQL: Syntax.

When you do not want your routine to return a value, you must create the routine as a procedure. To create an external procedure, write the body of the procedure in a language other than SPL and then use the CREATE PROCEDURE statement to register the procedure.

The following example shows how to register an external procedure that is written in C:

CREATE PROCEDURE log_compare (arg1 udtype2, arg2 udtype2)
EXTERNAL NAME 
	"/usr/lib/udtype1/lib/libbtype2.so(compare_n_insert)"
LANGUAGE C
END PROCEDURE

In the preceding example, the actual external procedure is located in a C routine called compare_n_insert(), which is located in the C-language shared library /usr/lib/udtype1/lib/libbtype2.so. If the EXTERNAL NAME clause does not specify an entry point within the library, the database server invokes the module at the default entry point, log_compare().

The following example also includes the SPECIFIC keyword to create a function alias, basetype2_lessthan. Once you use the SPECIFIC keyword to create a routine alias, you can use that alias in DROP statements.

For information about the CREATE PROCEDURE statement, see the CREATE PROCEDURE statement in the Informix Guide to SQL: Syntax.

When you create an external routine, you can specify optional modifiers that tell the database server about attributes of the UDR. Use the WITH clause of the CREATE FUNCTION and CREATE PROCEDURE statements to list routine modifiers. Following the WITH keyword, the modifiers that you want to specify are enclosed within parentheses and separated by commas.

Modifiers in a UDR Written in C

The following table shows the routine modifiers that are valid for external routines written in C.

The following example shows how to use the WITH clause to specify a set of modifiers when you create an external function:

CREATE FUNCTION lessthan (arg1 basetype2, arg2 basetype2)
RETURNING BOOLEAN
WITH (HANDLESNULLS, NOT VARIANT)
EXTERNAL NAME 
"/usr/lib/basetype2/lib/libbtype2.so(basetype2_lessthan)"
LANGUAGE C

The HANDLESNULLS modifier indicates that the basetype2_lessthan() function (in the shared library /usr/lib/basetype2/lib/libbtype2.so) is written so that it can recognize when an SQL null argument is passed.

Modifiers in a UDR Written in Java

The following table shows the routine modifiers that are valid for external routines written in Java.

The CREATE FUNCTION and CREATE PROCEDURE statements specify any parameters and return value for a C UDR. These statements use SQL data types for parameters and the return value. For example, suppose a C UDR has the following C declaration:

mi_double_precision *func1(parm1, parm2)
	mi_integer parm1;
	mi_double_precision *parm2;

The following CREATE FUNCTION statement registers the func1() user-defined function:

CREATE FUNCTION func1(INTEGER, FLOAT)
RETURNS FLOAT

Use the opaque SQL data type, POINTER, to specify a data type for an external routine whose parameter or return type has no equivalent SQL data type. The CREATE FUNCTION or CREATE PROCEDURE statement uses the POINTER data type when the data structure that an external routine receives or returns is a private data type, not one that is available to users.