Extending INFORMIX-Universal Server: User-Defined Routines

Extending INFORMIX-Universal Server: User-Defined Routines
Chapter 2: Routine Overloading and Routine Resolution

Home Contents Index Master Index New Book

What Is Routine Overloading?

Routine overloading refers to the ability to assign one name to multiple routines and specify different types of arguments on which the routines can operate. The advantage of routine overloading is that you do not need to invent a different name for a function that performs the same task for different arguments.

With Universal Server, you can have more than one routine with the same name but different parameter lists, as in the following situations:

You create a routine with the same name as a built-in function (such as equal()) to process a new user-defined data type.
You create type hierarchies, in which subtypes inherit data representation and functions from supertypes.
You create distinct types, which are data types that have the same internal storage representation as an existing data type, but have different names and cannot be compared to the source type without casting. Distinct types inherit functions from their source types.

For example, you might create each of the following routines to calculate the area of different data types (each data type represents a different geometric shape):

CREATE FUNCTION area(arg1 circle) RETURNING DECIMAL...
CREATE FUNCTION area(arg1 rectangle) RETURNING DECIMAL....
CREATE FUNCTION area(arg1 polygon) RETURNING DECIMAL....

In this way, you can overload a routine so that you have a customized area() routine for every data type that you want to evaluate. When a routine has been overloaded, the database server can execute different routines that have the same name. However, the database server cannot uniquely identify an overloaded routine by its name alone.

The Routine Signature

Due to routine overloading, the database server might not be able to uniquely identify a routine by its name alone. The database server uniquely identifies an overloaded routine by its signature. The routine signature includes the following information:

The type of routine (procedure or function)
The routine name
The number of parameters
The data types of the parameters
The order of the parameters
The owner name (ANSI database only)

You use the routine signature in SQL statements when you perform DBA tasks (DROP, GRANT, REVOKE, and UPDATE STATISTICS) on routines. The signature identifies the routine on which to perform the DBA task.

For example, the statement shown in Figure 2-1 uses a routine signature.

Figure 2-1
Example of Routine Signature

Important: The signature of a routine does not include return types. Consequently, you cannot create two functions that have the same signature but different return types.

In a database that is not ANSI compliant, the routine signature must be unique within the entire database, irrespective of the owner. If you explicitly qualify the routine name with your owner name, the signature includes your owner name as part of the routine name.

ANSI

In an ANSI-compliant database, the routine signature must be unique within the user's name space. The routine name always begins with the owner, in the format <owner>.<routine name>.

Specifying a Routine During Creation

You establish the routine signature when you create a function or procedure with the CREATE FUNCTION or CREATE PROCEDURE statements. When you create a function or procedure, you also specify the argument names.

For example, the following CREATE FUNCTION statement registers an equal() function that was written to take two arguments, arg1 and arg2, of data type udtype1 and return 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;

Using the Optional Specific Name

You can assign a specific name to a particular signature of a routine. You can use the unique specific name as a shorthand identifier to refer to a particular overloaded variation of a routine. You can use the specific name instead of the full signature in the following SQL statements:

DROP
GRANT
REVOKE
UPDATE STATISTICS

A specific name can be up to 18 characters long and is unique in the database. Two routines in the same database cannot have the same specific name, even if they have different owners. To assign a unique name to an overloaded routine with a particular data type, use the SPECIFIC keyword when you create the routine. You specify the specific name, in addition to the routine name, in the CREATE PROCEDURE or CREATE FUNCTION statement.

Figure 2-2 shows how to define the specific name eq_udtype1 in a CREATE FUNCTION statement that creates the equal() function.

Figure 2-2

CREATE FUNCTION equal ( arg1 udtype1, arg2 udtype1)

RETURNING BOOLEAN

	SPECIFIC eq_udtype1
EXTERNAL NAME 
"/usr/lib/udtype1/lib/libbtype1.so(udtype1_equal)"

LANGUAGE C

END FUNCTION;

Now you can refer to the routine defined in Figure 2-2 with either the routine signature or the specific name. The following sample DDL statement specifies the routine by its signature:

GRANT EXECUTE ON equal(udtype1, udtype1) to mary

The following example specifies the routine defined in Figure 2-2 with the SPECIFIC keyword and the specific name:

GRANT EXECUTE ON SPECIFIC eq_udtype1 to mary

Specifying Overloaded Routines During Invocation

When you invoke an overloaded routine, you must specify an argument list for the routine. If you invoke an overloaded routine by the routine name only, the routine-resolution process fails because the database server cannot uniquely identify the routine without the arguments

For example, the following SQL statement shows how you can invoke the equal function for a new data type, udtype1:

SELECT ... FROM employee 

	WHERE equal(col1_udtype1,col2_udtype1) ...

You can also invoke the equal function as in the following examples:

EXECUTE FUNCTION equal(arg1_udtype1,arg2_udtype1) INTO result
CALL equal(arg1_udtype1,arg2_udtype1) RETURNING boolean...

For more information on invoking a routine through an SQL expression, the EXECUTE statement, and the CALL statement, refer to "Invoking a Routine".