Figure 2-3 shows the CREATE statements to define a sample hierarchy.

Figure 2-3
Sample Data Type Hierarchy

CREATE ROW TYPE emp 

	(name varchar(30), 

	 age int, 

	 salary numeric(10,2));
CREATE ROW TYPE trainee UNDER emp ...

CREATE ROW TYPE student_emp (gpa float) UNDER trainee;

For example, suppose you create an overload function, bonus(), for the sample type hierarchy in Figure 2-3. You create the bonus() function on the root supertype and a subtype and invoke the bonus() function with the following statements:

CREATE FUNCTION bonus (emp,int) RETURNS numeric(10,2) ...
CREATE FUNCTION bonus(trainee,float) RETURNS numeric(10,2)...
EXECUTE FUNCTION bonus(student_emp, int);

1. Processes the leftmost argument first. a. Looks for a candidate routine named bonus with a row type parameter of student_emp.

No candidate routines exist with this parameter, so the database server continues with the next data type precedence, as described in "Precedence List of Data Types".

b. Because student_emp is a subtype of trainee, the database server looks for a candidate routine with a parameter of type trainee in the first position.

The second function, bonus(trainee,float), matches the first argument in the routine invocation.

2. The database server processes the second argument next. a. Looks for a candidate routine with a second parameter of data type integer.

The matching candidate routine from step 1b above has a second parameter of data type float. Therefore, the database server continues with the next data type precedence as described in "Precedence List of Data Types".

b. Because the second parameter is a built-in data type, the database server goes to the precedence list in Figure 2-5.

The database server searches the candidate list of routines for a parameter that matches one of the data types listed in precedence list for the integer data type.

c. Because a built-in cast exists from the integer to float data types, the database server casts the integer argument to float before the execution of the bonus function.

3. Because of the left-to-right rule for processing the arguments, the database server executes the second function, bonus(trainee,float).

When a distinct data type in the argument list does not match the data type of the parameter in the same position of the routine signature, the database server searches for a routine that accepts the source type in the position of that argument. If the source type is itself a distinct type, the database server checks the source type of that distinct type.

The candidate list can contain a routine with an argument that is the source type of the invoked routine argument. However, if the source type is not in the precedence list for that data type, then the routine-resolution process eliminates that candidate.

For example, suppose you create the following distinct data types and table:

CREATE DISTINCT TYPE pounds AS int;
CREATE DISTINCT TYPE stones AS int;
CREATE TABLE test(p pounds, s stones);

Figure 2-4
Sample Distinct Type Invocation

SELECT * FROM test WHERE p=s;

Important: The routine-resolution process cannot match two different distinct types.

The database server chooses the built-in equals function when you explicitly cast the arguments, as the following query shows:

SELECT * FROM test WHERE p::int = s::int;

• An overloaded function equals(pounds,stones) to handle the two distinct data types. The advantage of creating an overloaded equals function is that the SQL user does not need to know that these are new data types that require explicitly casting.
• Implicit cast functions from the data type pounds to stones and stones to int.

Figure 2-5
Precedence of Built-In Data Types

Data Type	Precedence List
CHAR	VARCHAR, LVARCHAR
VARCHAR	LVARCHAR
NCHAR	NVARCHAR
NVARCHAR	None
SMALLINT	INT, INT8, DECIMAL, SMALLFLOAT, FLOAT
INT	INT8, DECIMAL, SMALLFLOAT, FLOAT, SMALLINT
INT8	DECIMAL, SMALLFLOAT, FLOAT, INT, SMALLINT
SERIAL	INT, INT8, DECIMAL, SMALLFLOAT, FLOAT, SMALLINT
SERIAL8	INT8, DECIMAL, SMALLFLOAT, FLOAT, INT, SMALLINT
DECIMAL	SMALLFLOAT, FLOAT, INT8, INT, SMALLINT
SMALLFLOAT	FLOAT, DECIMAL, INT8, INT, SMALLINT
FLOAT	SMALLFLOAT, DECIMAL, INT8, INT, SMALLINT
MONEY	DECIMAL, SMALLFLOAT, FLOAT, INT8, INT, SMALLINT
DATE	None
DATETIME	None
INTERVAL	None
BYTE	None
TEXT	None

Figure 2-6 shows sample overloaded test functions and a query that invokes the test function. This query invokes the function with a DECIMAL argument, test(2.0). Because a test function for a DECIMAL argument does not exist, the routine-resolution process checks for the existence of a test function for each data type shown in the precedence list in Figure 2-5.

Figure 2-6 Example of Data Type Precedence During Routine Resolution

Figure 2-6 shows the order in which the database server performs a search for the overloaded function, test(). The database server searches for a qualifying test() function that takes a single argument of type INT.

Routine Resolution with Casts

For example, suppose you create the following two casts and two routines:

CREATE IMPLICIT CAST (foo AS bar)
CREATE IMPLICIT CAST (bar AS foo)
CREATE FUNCTION g(foo, foo) ...
CREATE FUNCTION g(bar, bar) ...

EXECUTE FUNCTION g(foo, bar);

Because the database server performs implicit casts during routine resolution, a different routine than the one that the user expects might execute. For example, suppose the database has the following two routines named func():

func(arg1 INT)
func(arg1 DECIMAL)

Tip: Consider the order in which the database casts data and resolves routines as part of your decision to overload a routine.

Null Arguments in Overloaded Routines

• The argument list of the routine contains a null value.
• The routine invoked is an overloaded routine.

CREATE FUNCTION foo(arg1 INT, arg2 INT) RETURNS BOOLEAN...
CREATE FUNCTION foo(arg1 MONEY, arg2 INT) RETURNS BOOLEAN...
CREATE FUNCTION foo(arg1 REAL, arg2 INT) RETURNS BOOLEAN...

CREATE TABLE new_tab ( col_int INT);

SELECT *
FROM new_tab
WHERE foo(col_int, NULL) = "t";

If more than one qualifying routine exists, the database server returns an error. The following query returns an error because the database server cannot determine which foo() function to invoke. The null value in the first argument matches the first parameter of each function; all three foo() functions expect a second argument of type INT.

SELECT *
FROM new_tab
WHERE foo(NULL, col_int) = "t";