Define a type hierarchy

The following figure provides an example of a simple type hierarchy that contains three named row types.

Figure 1. Example of a type hierarchy

There are three types. At the top is person_t. The child of person_t is employee_t. The child of employee_t is sales_rep_t.

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 1 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;

Important: You must have the UNDER privilege on the supertype before you can create a subtype that inherits the properties of the supertype.

In the type hierarchy in Figure 1, 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 2 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 2. Example of a type hierarchy that is a tree structure

The top type is "person_t". "person_t" has two children: "employee_t" and "customer_t". "employee_t" has two children: "sales_rep_t" and "engineer_t". Below "sales_rep_t" and "engineer_t" there are vertical ellipses indicating that the hierarchy continues but is not shown here. "customer_t" (sibling of "employee_t" and child of "person_t") has two children: "us_customer_t" and "non_us_customer_t". "non_us_customer_t" has no children. "us_customer_t" has two children: "local_customers_t" and "regional customers_t". Below "local_customers_t" and "regional customers_t" there are vertical ellipses indicating that the hierarchy continues but is not shown.

The topmost type of any hierarchy is referred to as the root supertype. In Figure 2, 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.