## 9.2.User Defined Types

A user-defined type is a schema object, identified by a user-defined type name. The definition of a user-defined type specifies a number of components, including in particular a list of attribute definitions. The representation of a user-defined type is expressed as a list of attribute definitions.

The definition of a user-defined type may include a method specification list consisting of one or more method specifications. A method specification is either an original method specification or an overriding method specification. Each original method specification specifies the method name, the SQL parameter declaration list, the returns data type, the <language clause>, the language (if the language is not SQL), and whether it is a STATIC or CONSTRUCTOR method.

Each overriding method specification specifies the method name,
the SQL parameter declaration list and the RETURNS data type. For
each overriding method specification, there must be an original
method specification with the same method name and SQL parameter
declaration list in some proper supertype of the user-defined type.
Every SQL-invoked method in a schema must correspond to exactly one
original method specification or overriding method specification
associated with some user-defined type existing in that schema. A
method `M`

that corresponds to an
original method specification in the definition of a structured
type `T1`

is an original method
of `T1`

. A method `M`

that corresponds to an overriding method
specification in the definition of `T1`

is an overriding method of `T1`

. A method `M`

is a method of type `T1`

if one of the following holds:

`M` is an original method of
`T1` |

`M` is an overriding method
of `T1` |

There is a proper supertype `T2` of `T1` such that `M` is an original or overriding method of
`T2` and such that there is no
method `M3` such that
`M3` has the same method name and
SQL parameter declaration list as `M` and `M3` is an original method or overriding
method of a type `T3` such that
`T2` is a proper supertype of
`T3` and `T3` is a supertype of `T1` . |

A user defined type can be a direct subtype of one (and only one) user defined type. The user defined type cannot be a subtype of itself.

A type `Ta`

is a direct
subtype of a type `Tb`

if
`Ta`

is a proper subtype of
`Tb`

and there does not exist a
type `Tc`

such that `Tc`

is a proper subtype of `Tb`

and a proper supertype of `Ta`

.

A type `Ta`

is a subtype of
type `Tb`

if one of the following
pertains:

`Ta` is a direct subtype of
`Tb` ; or |

`Ta` is a subtype of some
type `Tc` and `Tc` is a direct subtype of `Tb` . |

By the same token, `Tb`

is a
supertype of `Ta`

and is a direct
supertype of `Ta`

in the
particular case where `Ta`

is a
direct subtype of `Tb`

. If
`Ta`

is a subtype of `Tb`

, then `Ta`

is proper subtype of `Tb`

and `Tb`

is a proper supertype of `Ta`

. A type cannot be a proper supertype of
itself. A type with no proper supertypes is a maximal supertype. A
type with no proper subtypes is a leaf type.

Let `Ta`

be a maximal
supertype and let `T`

be a
subtype of `Ta`

. The set of all
subtypes of `Ta`

(which includes
`Ta`

itself) is called a subtype
family of `T`

or (equivalently)
of `Ta`

. A subtype family is not
permitted to have more than one maximal supertype. Every value in a
type `T`

is a value in every
supertype of `T`

. A value
`V`

in type `T`

has exactly one most specific type
`MST`

such that `MST`

is a subtype of `T`

and `V`

is not a value in any proper subtype of `MST`

. The most specific type of value need
not be a leaf type. For example, a type structure might consist of
a type `PERSON`

that has
`STUDENT`

and `EMPLOYEE`

as its two subtypes, while
`STUDENT`

has two direct subtypes
`UG_STUDENT`

and `PG_STUDENT`

. The invocation `STUDENT( )`

of the constructor function for
`STUDENT`

returns a value whose
most specific type is `STUDENT`

,
which is not a leaf type. If `Ta`

is a subtype of `Tb`

, then a
value in `Ta`

can be used
wherever a value in `Tb`

is
expected. In particular, a value in `Ta`

can be stored in a column of type
`Tb`

, can be substituted as an
argument for an input SQL parameter of data type `Tb`

, and can be the value of an invocation
of an SQL-invoked function whose result data type is `Tb`

. A type `T`

is said to be the minimal common
supertype of a set of types `S`

if `T`

is a supertype of every
type in `S`

and a subtype of
every type that is a supertype of every type in `S`

.

Note: | |
---|---|

Because a subtype family has exactly one maximal supertype, if two types have a common subtype, they must also have a minimal common supertype. Thus, for every set of types drawn from the same subtype family, there is some member of that family that is the minimal common supertype of all of the types in that set. |

A user-defined type is declared by a user-defined type CREATE TYPE statement .