Overloading Routines

Routine overloading refers to the ability to assign one name to multiple routines and specify parameters of different data types on which the routines can operate. Because the database server supports routine overloading, you can register more than one UDR with the same name.

Creating Overloaded Routines

The database server can support routine overloading because it supports polymorphism: the ability to have many entities with the same name and to choose the entity most relevant to a particular usage.

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 UDT.
• 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. Distinct types cannot be compared to the source type without casting. Distinct types inherit UDRs from their source types.

For example, you might create each of the following user-defined functions 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....

These three CREATE FUNCTION statements create an overloaded routine called area(). Each CREATE FUNCTION statement registers an area() function for a particular argument type. You can overload a routine so that you have a customized area() routine for every data type that you want to evaluate.

The advantage of routine overloading is that you do not need to invent a different name for a routine that performs the same task for different arguments. When a routine has been overloaded, the database server can choose which routine to execute based on the arguments of the routine when it is invoked.

Assigning a Specific Routine Name

Due to routine overloading, the database server might not be able to uniquely identify a routine by its name alone. When you register an overloaded UDR, you can assign a specific name to a particular signature of a routine. The specific name serves as a shorthand identifier that refers to a particular overloaded version of a routine.

A specific name can be up to 128 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.

You can use the specific name instead of the full routine signature in the following SQL statements:

• ALTER FUNCTION, ALTER PROCEDURE, ALTER ROUTINE
• DROP FUNCTION, DROP PROCEDURE, DROP ROUTINE
• GRANT
• REVOKE
• UPDATE STATISTICS

For example, suppose you assign the specific name eq_udtype1 to the UDR that the following statement creates:

CREATE FUNCTION equal (arg1 udtype1, arg2 udtype1)
   RETURNING BOOLEAN
   SPECIFIC eq_udtype1
   EXTERNAL NAME
     '/usr/lib/udtype1/lib/libbtype1.so(udtype1_equal)'
   LANGUAGE C

You can then refer to the UDR with either the routine signature or the specific name. The following two GRANT statements are equivalent:

GRANT EXECUTE ON equal(udtype1, udtype1) to mary
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 overloaded equal() function on a new data type, udtype1:

CREATE TABLE atest (col1 udtype1, col2 udtype1, ...)
...
SELECT * FROM employee WHERE equal(col1, col2)

Because the equal() function is an operator function bound to the equal (=) symbol, you can also invoke the equal() function with an argument on either side of the operator symbol, as follows:

SELECT * FROM employee WHERE col1 = col2

EXECUTE FUNCTION equal(col1, col2) INTO result

CALL equal(col1, col2) RETURNING result
LET result = equal(col1, col2)

For more information about overloaded operator functions, refer to Extending Operators and Built-In Functions.

Overloading Built-In SQL Functions

The database server provides built-in SQL functions that provide some basic mathematical operations. You can overload most of these built-in SQL functions. For example, you might want to create a sin() function on a UDT that represents complex numbers. For a complete list of built-in SQL functions that you can overload, see Built-In Functions.