The External Name

The EXTERNAL NAME clause of the CREATE FUNCTION or CREATE PROCEDURE statement tells the database server where to find the object code for the UDR. These statements store this location in the externalname column of the sysprocedures system catalog table. When the database server executes an SQL statement that contains a UDR, it loads into memory the shared-object file that contains its executable code. The database server examines the externalname column to determine which shared-object file to load.

In Figure 64, the EXTERNAL NAME clause of this CREATE FUNCTION statement tells the database server that the object code for the abs_eq( ) user-defined function is in a Solaris shared-object file named abs.so, which resides in the /usr/code directory.

By default, the database server uses the same name for the entry point into the shared-object file for the UDR object code as the name of the UDR. For example, the CREATE FUNCTION statement in Figure 64 specifies that the entry point in the abs.so shared-object file for the abs_eq( ) user-defined function is a C function named abs_eq( ).

The EXTERNAL NAME clause provides the following features to allow flexibility in the UDR external-name specification:

• Specifying a different entry point
• Including environment variables in the pathname

Each of these features is described in more detail below. For more information about the EXTERNAL NAME clause, see the External Reference segment of the IBM Informix: Guide to SQL Syntax.

Specifying the Entry Point

To specify an entry point whose name is different from the name of the UDR, put the name of the actual entry point in parentheses after the name of the shared-object file. You must specify an entry point when your UDR has a different name from the UDR that it implements. The following CREATE FUNCTION statement specifies that the entry point for the object code of the abs_eq( ) UDR is a C function named abs_equal( ):

CREATE FUNCTION abs_eq(INTEGER, INTEGER)
   RETURNS boolean 
   EXTERNAL NAME '/usr/code/abs.so(abs_equal)'
   LANGUAGE C;

The database server invokes the C function abs_equal( ) whenever an SQL statement calls the abs_eq( ) function with two arguments of INTEGER data type.

Using Environment Variables

You can include environment variables in the external-name specification of the EXTERNAL NAME clause. These environment variables must be set in the database server environment; that is, they must be set before the database server starts. For example, the following function registration specifies to evaluate the USERFUNCDIR environment variable when determining the location of the my_func( ) user-defined function:

CREATE FUNCTION my_func(arg INTEGER)
RETURNING FLOAT
EXTERNAL NAME "$USERFUNCDIR/funcs.udr"
LANGUAGE C;