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] 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 .