As previously mentioned, there are two kinds of types in
PostgreSQL: base types (defined in a
programming language) and composite types. This chapter describes
how to define new base types.
The examples in this section can be found in
complex.sql and complex.c
in the tutorial directory. Composite examples are in
funcs.sql.
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-terminated 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-terminated character string.
Suppose we want to define a complex type which represents complex
numbers. Naturally, we would 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 functions are usually not hard to write, especially the output
function. However, there are a number of points to remember:
When defining your external (string) representation, remember
that you must eventually write a complete and robust parser for
that representation as your input function!
For instance:
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);
}
You should try to make the input and output functions inverses of
each other. If you do not, you will have severe problems when
you need to dump your data into a file and then read it back in
(say, into someone else's database on another computer). This is
a particularly common problem when floating-point numbers are
involved.
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(cstring)
RETURNS complex
AS 'PGROOT/tutorial/complex'
LANGUAGE C;
CREATE FUNCTION complex_out(complex)
RETURNS cstring
AS 'PGROOT/tutorial/complex'
LANGUAGE C;
Finally, we can declare the data type:
CREATE TYPE complex (
internallength = 16,
input = complex_in,
output = complex_out
);
Notice that the declarations of the input and output functions must
reference the not-yet-defined type. This is allowed, but will draw
warning messages that may be ignored.
As discussed earlier, PostgreSQL fully
supports arrays of 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.
Composite types do not need any function defined on them, since the
system already understands what they look like inside.
If the values of your data type might exceed a few hundred bytes in
size (in internal form), you should be careful to mark them
TOAST-able. 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.
