Routine resolution in a type hierarchy

A type hierarchy is a relationship that you define among named row types in which subtypes inherit representation (data fields) and behavior (routines, operators, rules) from a named row above it (supertype) and can add additional fields and routines. The subtype is said to inherit the attributes and behavior from the supertype.

For information about creating type hierarchies, refer to the discussion of type and table hierarchies in the HCL OneDB™ Database Design and Implementation Guide.

When a UDR has named row types in its parameter list, the database server must resolve which type in the type hierarchy to pass to the UDR. When a 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 with a parameter in the same position that is the closest supertype of that argument.

Suppose you create the following type hierarchy and register the overloaded function bonus() on the root supertype, emp, and the trainee subtype:

CREATE ROW TYPE emp 
   (name VARCHAR(30), 
    age INT, 
    salary DECIMAL(10,2));
CREATE ROW TYPE trainee UNDER emp ...
CREATE ROW TYPE student_emp (gpa FLOAT) UNDER trainee;

CREATE FUNCTION bonus (emp,INT) RETURNS DECIMAL(10,2) ...
CREATE FUNCTION bonus(trainee,FLOAT) RETURNS DECIMAL(10,2).

Then you invoke the bonus() function with the following statement:

EXECUTE FUNCTION bonus(student_emp, INT);

To resolve the data type of the UDR parameter when it is a named row type, the database server takes the following steps:

The database server processes the leftmost argument first:
1. It 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.
2. 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 first parameter of the second function, bonus(trainee,float), matches the first argument in the routine invocation. Therefore, this version of bonus() goes on the precedence list.
The database server processes the second argument next:
1. It looks for a candidate routine with a second parameter of data type INTEGER.
  The matching candidate routine from step 1b has a second parameter of data type FLOAT. Therefore, the database server continues with the next data type precedence as Precedence list of data types describes.
2. Because the second parameter is the INTEGER built-in data type, the database server goes to the precedence list that Precedence of built-in data types shows.
  The database server searches the candidate list of routines for a second parameter that matches one of the data types in the precedence list for the integer data type.
3. Because a built-in cast exists from the integer data type to the float data type, the database server casts the integer argument to float before the execution of the bonus() function.
Because of the left-to-right rule for processing the arguments, the database server executes the second function, bonus(trainee,float).