User-Defined Types

As previously mentioned, there are two kinds of types in PostgreSQL: base types (defined in a programming language) and composite types. PostgreSQL allows the user to create new base types. Refer to the CREATE TYPE command in the Reference section of this guide for the syntax and more information on how to register new types with PostgreSQL.

A user-defined type must always have input and output functions. These functions determine how the type appears in strings (for input by the user and output to the user) and how the type is organized in memory. The input function takes a null-delimited character string as its input and returns the internal (in memory) representation of the type. The output function takes the internal representation of the type and returns a null delimited character string.

These functions are usually not hard to write, especially the output function. However, there are a number of points to remember:

Suppose we want to define a complex type which represents complex numbers. Naturally, we choose to represent a complex in memory as the following C structure:
typedef struct Complex {
    double      x;
    double      y;
} Complex;
     
and a string of the form (x,y) as the external string representation.

The input function could be:

Complex *
complex_in(char *str)
{
    double x, y;
    Complex *result;
    if (sscanf(str, " ( %lf , %lf )", &x, &y) != 2) {
        elog(ERROR, "complex_in: error in parsing %s", str);
        return NULL;
    }
    result = (Complex *)palloc(sizeof(Complex));
    result->x = x;
    result->y = y;
    return (result);
}

The output function can simply be:
char *
complex_out(Complex *complex)
{
    char *result;
    if (complex == NULL)
        return(NULL);
    result = (char *) palloc(60);
    sprintf(result, "(%g,%g)", complex->x, complex->y);
    return(result);
}

To define the complex type, we need to create the two user-defined functions complex_in and complex_out before creating the type:
CREATE FUNCTION complex_in(opaque)
    RETURNS complex
    AS 'PGROOT/tutorial/complex.so'
    LANGUAGE C;

CREATE FUNCTION complex_out(opaque)
    RETURNS opaque
    AS 'PGROOT/tutorial/complex.so'
    LANGUAGE C;

Now we can create the type:
  
CREATE TYPE complex (
    internallength = 16,
    input = complex_in,
    output = complex_out
);
    

Types and TOAST Tables

If the values of your datatype might exceed a few hundred bytes in size (in internal form), you should be careful to mark them TOASTable. To do this, the internal representation must follow the standard layout for variable-length data: the first four bytes must be an int32 containing the total length in bytes of the datum (including itself). Then, all your functions that accept values of the type must be careful to call pg_detoast_datum() on the supplied values — after checking that the value is not NULL, if your function is not strict. Finally, select the appropriate storage option when giving the CREATE TYPE command.

Array Types

As discussed earlier, PostgreSQL fully supports arrays of built-in base types. Additionally, PostgreSQL supports arrays of user-defined types as well. When you define a type, PostgreSQL automatically provides support for arrays of that type. For historical reasons, the array type has the same name as the user-defined type with the underscore character _ prepended.