Object-relational data management systems generally provide a mechanism through which a user can extend the built-in data types provided by the DBMS with additional, user-defined data types. For example, a mapping application might need to operate on a LOCATION data type that consists of a pair of latitude and longitude measurements, each consisting of hours, minutes, and seconds. To effectively process location data, the application may need to define special functions, such as a DISTANCE(X,Y) function that computes the distance between two locations. The meanings of some built-in operations, such as a test for equality (=), will need to be redefined for location type data.
One way that Informix Universal Server supports user-defined data types is through its OPAQUE data type. An OPAQUE data type is (not surprisingly) opaque to the DBMS. The DBMS can store and retrieve data with this type, but it has no knowledge of the internal workings of the type. In object-oriented terms, the data is completely encapsulated. The user must explicitly provide (in external routines, written in C or some similar programming language) the data structure for the type, code to implement the functions or operations that can be performed on the type (such as comparing two data items of the type for equality), and code to convert the opaque type between internal and external representations. Thus, OPAQUE data types represent a low-level capability to extend the core functionality of the DBMS with data types that appear as if they were built-in.
A more basic user-defined data type capability is provided by the implementation of DISTINCT data types within Informix. A DISTINCT type is useful to distinguish among different types of data, all of which use one of the DBMS built-in data types. For example, the city and company name data items in a database might both be defined with the data type VARCHAR(2 0). Even though they share the same underlying DBMS data type, these data items really represent quite different types of data. You would never normally compare a city value to a company name, and yet the DBMS will let you do this because the two VARCHAR(2 0) columns are directly comparable.
To maintain a higher level of database integrity, you could define each of these three data items as having a DISTINCT data type:
CREATE DISTINCT TYPE CITY_TYPE AS VARCHAR(20);
CREATE DISTINCT TYPE CO_NAME_TYPE AS VARCHAR(20);
Now tables can be created containing city and customer name data items in terms of the CITY_TYPE and CO_NAME_TYPE data types. If you try to compare columns with these two different data types, the DBMS automatically detects the situation and generates an error. You can compare them, but only by explicitly casting the data type of one item to match the data type of the other. As a result, the distinct data types assigned to the different columns help to maintain the integrity of the database and prevent inadvertent errors in programs and ad hoc queries that use the database.
Source: Liang Y. Daniel (2013), Introduction to programming with SQL, Pearson; 3rd edition.