Extending Types

I l @ ve RuBoard

PostgreSQL has a plethora of built-in data types (see Chapter 2, "PostgreSQL Data Types"). However, in specific cases, it might be advantageous to create custom-defined data types.

All the data types in PostgreSQL can be defined as belonging to one of the following cases: base types or composites.

Base types, like int4 , are written in C and are compiled into the system. However, custom data types can be compiled as shared objects and linked to the back end by using the CREATE TYPE command.

Composite types are created whenever a new table is created. At first it might seem counterintuitive to think of a table as a type . However, tables are merely collections of single data types grouped in a specific order. In that way, a table can be seen as just a "composite," or complex collection, of simpler single-element data types.

Creating Data Types

To create a custom base type, two functions must be defined: an input function and an output function.

The input function is responsible for accepting a NULL delimited character string into memory, and it returns an internal representation value.

The output function accesses the internal representation of the data element and returns it as the original NULL -delimited character string.

The PostgreSQL 7.1 Programmer's Guide contains a good example of how a custom data type could be created.

First you must define the structure of your complex data type:

 typedef struct Complex {     double      x;      double      y;  } Complex; 

Next, the input and output functions must be specified:

 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);  }  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);  } 

Care should be taken to ensure that the input and output functions are the reciprocal of each other. If not, data that is dumped out (that is, copied to a file) will not be able to be read back in.

After the preceding code has been compiled to static objects, the corresponding SQL function must be created to register them with the database:

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

Lastly, the CREATE TYPE command is used to define the characteristics of the newly created custom base type:

 CREATE TYPE complex (     internallength = 16,      input = complex_in,      output = complex_out); 

I l @ ve RuBoard