Aims

• explore the mechanisms provided by PostgreSQL for adding user-defined types
• add a domain, an enumerated type, and a new base type

Background

One thing that PostgreSQL does better than many other DBMSs, is to provide well-defined and relatively easy-to-use mechanisms for adding new data types. There are several possible ways to add new types, depending on the requirements of the type. PostgreSQL's view of data types is the standard abstract data type view; a type is a domain of values and a collection of operators on those values. In addition, the existence of an ordering on the values of a data type and operations that use the ordering allow indexes to be built on attributes of that type. PostgreSQL has several different kinds of types:

• base types ... defined via C functions and providing genuine new data types; built-in types such as integer, date and varchar(n) are base types; users can also define new base types
• domains ... data types based on a constrained version of an existing data type
• enumerated types ... defined by enumerating the values of the type; values are specified as a list of strings and an ordering is defined on the values based on the order they appear in the list
• composite types ... these are essentially tuple types; a composite type is composed of a collection of named fields, where the fields can have different types; a composite type is created implicitly whenever a table is defined, but composite types can also be defined without the need for a table
• polymorphic types ... define classes of types (e.g. anyarray) and are used primarily in the definition of polymorphic functions
• pseudo-types ... special types (such as trigger) used internally by the system; polymorphic types are also considered to be pseudo-types

In this exercise, we'll look at domains, enumerated types and base types. Assignment 1, which this exercise leads into, is concerned only with base types.

Setup

Re-install your PostgreSQL server (after first removing the old one) as described in Prac Exercise 01. Once the server is installed, initialised and running (don't forget to source the env file), access the server and create an empty database called test.

Exercises

In the first exercise, we will create a domain and an enumerated type for a similar purpose, and examine the differences. In the second exercise we will look at the process of creating a new base type.

Exercise #1

Consider the problem of defining a data type for the days of the week. We will generally want to represent the days by their names, e.g.

Monday  Tuesday  Wednesday  Thursday  Friday  Saturday  Sunday

We also normally want some kind of ordering to indicate the order in which days occur, although it is an open question (application specific) which day starts the week. Let's assume that, as above, we start with Monday and we will use the above ordering of day names.

The day names are best represented in SQL as strings, so we need a new type that can be represented by a set of strings. There are two ways to produce a type like this in PostgreSQL:

create domain Days1 as varchar(9)
       check (value in ('Monday','Tuesday','Wednesday',
                        'Thursday','Friday','Saturday','Sunday'));
	
create type Days2 as enum
       ('Monday','Tuesday','Wednesday',
        'Thursday','Friday','Saturday','Sunday');

Now define a pair of tables that are identical, except that one uses the domain and the other uses the enumerated type:

create table Log1 ( name text, day Days1, starting time, ending time );
create table Log2 ( name text, day Days2, starting time, ending time );

Populate the tables via the following two commands:

copy Log1 (name, day, starting, ending) from '/LogData';
copy Log2 (name, day, starting, ending) from '/LogData';

Examine the contents of the tables via select statements and then run the following two commands:

select * from Log1 where name='John' order by day;
select * from Log2 where name='John' order by day;

Explain why they are different. Comment on which kind of data type definition is more appropriate in this context.

Exercise #2

In order to define a new base data type, a user needs to provide:

• input and output functions (in C) for values of the type
• C data structure definitions to represent type values internally
• an SQL definition for the type, giving its length, alignment and i/o functions
• SQL definitions for operators on the type
• C functions to implement the operators

The methods for defining the various aspects of a new base type are given in the following sections of the PostgreSQL manual: