The following figure provides an example of a simple type
hierarchy that contains three named row types.
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 Example of a type hierarchy 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 Example of a type hierarchy, 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. Example
of a type hierarchy that is a tree structure 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.
The topmost type of any hierarchy is referred to as the root
supertype. In Example
of a type hierarchy that is a tree structure, 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.