Defining a Type Hierarchy

Figure 28 provides an example of a simple type hierarchy that contains three named row types.

Figure 28. Example of a Type Hierarchy

The supertype at the top of the type hierarchy contains a group of fields that all underlying subtypes inherit. A supertype must exist before you can create its subtype. The following example creates the person_t supertype of the type hierarchy that Figure 28 shows:

CREATE ROW TYPE person_t
(
   name     VARCHAR(30) NOT NULL,
   address  VARCHAR(20),
   city     VARCHAR(20),
   state    CHAR(2),
   zip      INTEGER,
   bdate    DATE
);

To create a subtype, specify the UNDER keyword and the name of the supertype whose properties the subtype inherits. The following example illustrates how you might define employee_t as a subtype that inherits all the fields of person_t. The example adds salary and manager fields that do not exist in the person_t type.

CREATE ROW TYPE employee_t 
(
   salary   INTEGER,
   manager  VARCHAR(30)
) 
UNDER person_t;

In the type hierarchy in Figure 28, sales_rep_t is a subtype of employee_t, which is the supertype of sales_rep_t in the same way that person_t is the supertype of employee_t. The following example creates sales_rep_t, which inherits all fields from person_t and employee_t and adds four new fields. Because the modifications on a subtype do not affect its supertype, employee_t does not have the four fields that are added for sales_rep_t.

CREATE ROW TYPE sales_rep_t 
(
rep_num      INT8,
region_num   INTEGER,
commission   DECIMAL,
home_office  BOOLEAN
) 
UNDER employee_t;

The sales_rep_t type contains 12 fields: name, address, city, state, zip, bdate, salary, manager, rep_num, region_num, commission, and home_office.

Instances of both the employee_t and sales_rep_t types inherit all the UDRs that are defined for the person_t type. Any additional UDRs that are defined on employee_t automatically apply to instances of the employee_t type and to instances of its subtype sales_rep_t, but not to instances of person_t.

The preceding type hierarchy is an example of single inheritance because each subtype inherits from a single supertype. Figure 29 illustrates how you can define multiple subtypes under a single supertype. Although single inheritance requires that every subtype inherits from one and only one supertype, no practical limit exists on the depth or breadth of the type hierarchy that you define.

Figure 29. Example of a Type Hierarchy That Is a Tree Structure

The topmost type of any hierarchy is referred to as the root supertype. In Figure 29, person_t is the root supertype of the hierarchy. Except for the root supertype, any type in the hierarchy can be potentially both a supertype and subtype at the same time. For example, customer_t is a subtype of person_t and a supertype of us_customer_t. A subtype at the lower levels of the hierarchy contains properties of the root supertype but does not directly inherit its properties from the root supertype. For example, us_customer_t has only one supertype, customer_t, but because customer_t is itself a subtype of person_t, the fields and routines that customer_t inherits from person_t are also inherited by us_customer_t.