3.28. User-defined types

User-defined types are objects that create new data types that can be used by other objects in the database. In PostgreSQL, it's possible to configure four kinds of user-defined types, being them: base, enumeration, composite and range. Each type has its own set of attributes and configuration complexity and in order to provide a better explanation about them this section was subdivided in four sections which will detail each type separately.

3.28.1. Base types

Base or scalar types are elementary data types used in the same way as the PostgreSQL built-in ones. Creating base types are quite advanced and requires the use of C-based functions. Details about these functions will not be covered by this documentation.

In general terms, in order to create a base type you will need to define at least two functions called, respectively, input and output. The input function converts the external textual representation of the type to its internal representation. The output function does the reverse operation, that is, converts the type from its internal representation to external textual one. There are another optional attributes that can be assigned to the type and they are described on the table below.

Due to the amount of attributes used to create base types, pgModeler separates them in two tabs: Functions and Attributes. The first tab is used to configure the two mandatory functions as well other optional ones, and the second tab is used to configure advanced attributes for the type. You seldom need to use the advanced attributes but they are present for those users intending to create a more elaborated data type.

Attribute Description
INPUT This function converts the external textual representation of the type to its internal representation. This function should have the signature: any function(cstring, oid, integer).
OUTPUT This function converts the internal representation of the type to its external textual representation. This function should have the signature: cstring function(any).
RECV This function converts the external binary representation of the type to its internal representation. This function should have the signature: any function(internal, oid, integer).
SEND This function converts the internal representation of the type to its external binary representation. This function should have the signature: byta function(any).
TPMOD_IN This function validates the literal type modifier for the one being created. An example of type modifier is the length for varchar(20) or the base and precision for numeric(10,2). The input for this function is a cstring array and it should be internally processed by the function and the return should be a non-negative integer indicating the type modifier. This function should have the signature: integer function(cstring[]).
TPMOD_OUT This function converts the internal type modifier representation (single non-negative integer) back to the literal representation. This function should have the signature: cstring function(integer).
ANALYZE This function is the responsible to perform type-specific statistics collection for columns of the data type being created. This function must have the signature: boolean function(internal).


Note: Unlike the stated on the official documentation the functions INPUT, OUTPUT, RECV and SEND should be constructed using the any data type where the name of the new data type should appear. This is merely a placeholder and pgModeler will replace the any by the name of the new type being configured when running the SQL code generation.

Continuing to detail the base type, the image below shows the advanced attributes used to create a more detailed data type. The most of them aren't often used anyway they all will be described.

Attribute Description
Internal Length Defines the internal length of the type being configured. The default value 0 means that the type has a variable length.
Storage Specifies the storage mode for a variable length type. Four modes are accepted: plain, extended, external and main. The plain mode indicates that the type is stored in-line and uncompressed. The extended mode tells the system try first to compress a long value or move this value out of the main table if it's still too long. The external mode allows the value to be moved to the main table without try to compress it. The main mode allows value compression but discourages moving it to the main table.
Options A set of optional flags assigned to the type. By value indicates that values of the new type are passed only by value rather than by reference. Preferred indicates that the type is the preferred on its category. This parameter also helps the system to use implicit casts in case of ambiguity. Collatable indicates that the type's operations can use collation information.
Category Specifies in which category the new data type fits. Details about categories can be found on official documentation.
Delimiter Defines the character used as delimiter between values when this type is used as array.
Default Value Defines the default values for the data type.
Alignment Specifies the storage alignment for the new type. Four values are accepted: integer, char, smallint and double precision.
Like Type Indicates a previous defined type from which the new type will have the same representation.
Element Type Indicates the type of the elements when the new configured type is an array.

3.28.2. Enumeration types

Enumeration types are data types which are composed by static set of literal values. This data types is equivalent to the enum types existent in many of the programming languages. Its attributes are described below.

Attribute Description
Enumerations This list controls the set of enumerations available for the type. To create a new element use the field Enumeration.
Enumeration Creates a single enumeration in the type.

3.28.3. Composite types

Composite types are objects which contains a set of named attributes each one with its own data type. These types resemble to the structs present in some programming languages. Additionally, a composite type is basically the same as a row data type of a table but the advantage of the former is that user doen't need to explicitly create a table when all that is desired is to define a single type. Composite types can be quite useful as function's arguments and return type.

Attribute Description
Attributes This group of fields reunites all attributes needed to configure a composite type.
Name Name of an attribute of the type.
Data Type Data type used by the attribute.

3.28.4. Range types

Range data types represent an interval of values of an element type (also know as range subtype). These types are very useful because they are capable to represent many element values in a single range value. In other words, range types can be used when you need to work with an interval of values without the need to create auxiliary columns to denote its start and end. For instance, let's say you need to create a range of timestamp values, instead of use two columns like start_date and end_date you can just use one column configured with the built-in type tsrange (timestamp range). This type will handle all the operations needed to compare a single value against the entire range and many other features. A complete list of built-in range data types are available on PostgreSQL manuals.

Attribute Description
Operator Class The name of the btree operator class associated with the range subtype. This operator class is used to determine how values can be sorted.
Canonical Func. This function is used to convert the range internal values to a canonical form. It must have the signature: any function(any). The function differs from the stated on the PostgreSQL documentation, so refer to the note at end of section Base types above to understand why this function is written this way.
Subtype Diff Func. This function compares two values of Subtype and returns a double precision value representing the difference between them. This provides better efficiency of GiST indexes associated to columns with this range data type.
Subtype Configures the internal element type handled by the range type.

Jul 9, 2018 at 11:30