<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<HTML
><HEAD
><TITLE
>User-Defined Types</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK
REV="MADE"
HREF="mailto:pgsql-docs@postgresql.org"><LINK
REL="HOME"
TITLE="PostgreSQL 8.3.6 Documentation"
HREF="index.html"><LINK
REL="UP"
TITLE="Extending SQL"
HREF="extend.html"><LINK
REL="PREVIOUS"
TITLE="User-Defined Aggregates"
HREF="xaggr.html"><LINK
REL="NEXT"
TITLE="User-Defined Operators"
HREF="xoper.html"><LINK
REL="STYLESHEET"
TYPE="text/css"
HREF="stylesheet.css"><META
HTTP-EQUIV="Content-Type"
CONTENT="text/html; charset=ISO-8859-1"><META
NAME="creation"
CONTENT="2009-02-03T04:34:16"></HEAD
><BODY
CLASS="SECT1"
><DIV
CLASS="NAVHEADER"
><TABLE
SUMMARY="Header navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="5"
ALIGN="center"
VALIGN="bottom"
>PostgreSQL 8.3.6 Documentation</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="xaggr.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="top"
><A
HREF="extend.html"
>Fast Backward</A
></TD
><TD
WIDTH="60%"
ALIGN="center"
VALIGN="bottom"
>Chapter 34. Extending <ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="extend.html"
>Fast Forward</A
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="top"
><A
HREF="xoper.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><DIV
CLASS="SECT1"
><H1
CLASS="SECT1"
><A
NAME="XTYPES"
>34.11. User-Defined Types</A
></H1
><A
NAME="AEN41571"
></A
><P
> As described in <A
HREF="extend-type-system.html"
>Section 34.2</A
>,
<SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> can be extended to support new
data types. This section describes how to define new base types,
which are data types defined below the level of the <ACRONYM
CLASS="ACRONYM"
>SQL</ACRONYM
>
language. Creating a new base type requires implementing functions
to operate on the type in a low-level language, usually C.
</P
><P
> The examples in this section can be found in
<TT
CLASS="FILENAME"
>complex.sql</TT
> and <TT
CLASS="FILENAME"
>complex.c</TT
>
in the <TT
CLASS="FILENAME"
>src/tutorial</TT
> directory of the source distribution.
See the <TT
CLASS="FILENAME"
>README</TT
> file in that directory for instructions
about running the examples.
</P
><P
> <A
NAME="AEN41584"
></A
>
<A
NAME="AEN41586"
></A
>
A user-defined type must always have input and output
functions.<A
NAME="AEN41588"
></A
><A
NAME="AEN41591"
></A
>
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 argument and returns the internal (in memory) representation
of the type. The output function takes the internal representation
of the type as argument and returns a null-terminated character
string. If we want to do anything more with the type than merely
store it, we must provide additional functions to implement whatever
operations we'd like to have for the type.
</P
><P
> Suppose we want to define a type <TT
CLASS="TYPE"
>complex</TT
> that represents
complex numbers. A natural way to represent a complex number in
memory would be the following C structure:
</P><PRE
CLASS="PROGRAMLISTING"
>typedef struct Complex {
double x;
double y;
} Complex;</PRE
><P>
We will need to make this a pass-by-reference type, since it's too
large to fit into a single <TT
CLASS="TYPE"
>Datum</TT
> value.
</P
><P
> As the external string representation of the type, we choose a
string of the form <TT
CLASS="LITERAL"
>(x,y)</TT
>.
</P
><P
> The input and output functions are usually not hard to write,
especially the output function. But when defining the external
string representation of the type, remember that you must eventually
write a complete and robust parser for that representation as your
input function. For instance:
</P><PRE
CLASS="PROGRAMLISTING"
>PG_FUNCTION_INFO_V1(complex_in);
Datum
complex_in(PG_FUNCTION_ARGS)
{
char *str = PG_GETARG_CSTRING(0);
double x,
y;
Complex *result;
if (sscanf(str, " ( %lf , %lf )", &x, &y) != 2)
ereport(ERROR,
(errcode(ERRCODE_INVALID_TEXT_REPRESENTATION),
errmsg("invalid input syntax for complex: \"%s\"",
str)));
result = (Complex *) palloc(sizeof(Complex));
result->x = x;
result->y = y;
PG_RETURN_POINTER(result);
}</PRE
><P>
The output function can simply be:
</P><PRE
CLASS="PROGRAMLISTING"
>PG_FUNCTION_INFO_V1(complex_out);
Datum
complex_out(PG_FUNCTION_ARGS)
{
Complex *complex = (Complex *) PG_GETARG_POINTER(0);
char *result;
result = (char *) palloc(100);
snprintf(result, 100, "(%g,%g)", complex->x, complex->y);
PG_RETURN_CSTRING(result);
}</PRE
><P>
</P
><P
> You should be careful 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. This
is a particularly common problem when floating-point numbers are
involved.
</P
><P
> Optionally, a user-defined type can provide binary input and output
routines. Binary I/O is normally faster but less portable than textual
I/O. As with textual I/O, it is up to you to define exactly what the
external binary representation is. Most of the built-in data types
try to provide a machine-independent binary representation. For
<TT
CLASS="TYPE"
>complex</TT
>, we will piggy-back on the binary I/O converters
for type <TT
CLASS="TYPE"
>float8</TT
>:
</P><PRE
CLASS="PROGRAMLISTING"
>PG_FUNCTION_INFO_V1(complex_recv);
Datum
complex_recv(PG_FUNCTION_ARGS)
{
StringInfo buf = (StringInfo) PG_GETARG_POINTER(0);
Complex *result;
result = (Complex *) palloc(sizeof(Complex));
result->x = pq_getmsgfloat8(buf);
result->y = pq_getmsgfloat8(buf);
PG_RETURN_POINTER(result);
}
PG_FUNCTION_INFO_V1(complex_send);
Datum
complex_send(PG_FUNCTION_ARGS)
{
Complex *complex = (Complex *) PG_GETARG_POINTER(0);
StringInfoData buf;
pq_begintypsend(&buf);
pq_sendfloat8(&buf, complex->x);
pq_sendfloat8(&buf, complex->y);
PG_RETURN_BYTEA_P(pq_endtypsend(&buf));
}</PRE
><P>
</P
><P
> Once we have written the I/O functions and compiled them into a shared
library, we can define the <TT
CLASS="TYPE"
>complex</TT
> type in SQL.
First we declare it as a shell type:
</P><PRE
CLASS="PROGRAMLISTING"
>CREATE TYPE complex;</PRE
><P>
This serves as a placeholder that allows us to reference the type while
defining its I/O functions. Now we can define the I/O functions:
</P><PRE
CLASS="PROGRAMLISTING"
>CREATE FUNCTION complex_in(cstring)
RETURNS complex
AS '<TT
CLASS="REPLACEABLE"
><I
>filename</I
></TT
>'
LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION complex_out(complex)
RETURNS cstring
AS '<TT
CLASS="REPLACEABLE"
><I
>filename</I
></TT
>'
LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION complex_recv(internal)
RETURNS complex
AS '<TT
CLASS="REPLACEABLE"
><I
>filename</I
></TT
>'
LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION complex_send(complex)
RETURNS bytea
AS '<TT
CLASS="REPLACEABLE"
><I
>filename</I
></TT
>'
LANGUAGE C IMMUTABLE STRICT;</PRE
><P>
</P
><P
> Finally, we can provide the full definition of the data type:
</P><PRE
CLASS="PROGRAMLISTING"
>CREATE TYPE complex (
internallength = 16,
input = complex_in,
output = complex_out,
receive = complex_recv,
send = complex_send,
alignment = double
);</PRE
><P>
</P
><P
> When you define a new base type,
<SPAN
CLASS="PRODUCTNAME"
>PostgreSQL</SPAN
> automatically provides support
for arrays of that
type.<A
NAME="AEN41620"
></A
> The array type typically
has the same name as the base type with the underscore character
(<TT
CLASS="LITERAL"
>_</TT
>) prepended.
</P
><P
> Once the data type exists, we can declare additional functions to
provide useful operations on the data type. Operators can then be
defined atop the functions, and if needed, operator classes can be
created to support indexing of the data type. These additional
layers are discussed in following sections.
</P
><P
> <A
NAME="AEN41626"
></A
>
If the values of your data type vary in size (in internal form), you should
make the data type <ACRONYM
CLASS="ACRONYM"
>TOAST</ACRONYM
>-able (see <A
HREF="storage-toast.html"
>Section 53.2</A
>). You should do this even if the data are always
too small to be compressed or stored externally, because
<ACRONYM
CLASS="ACRONYM"
>TOAST</ACRONYM
> can save space on small data too, by reducing header
overhead.
</P
><P
> To do this, the internal representation must follow the standard layout for
variable-length data: the first four bytes must be a <TT
CLASS="TYPE"
>char[4]</TT
>
field which is never accessed directly (customarily named
<TT
CLASS="STRUCTFIELD"
>vl_len_</TT
>). You
must use <CODE
CLASS="FUNCTION"
>SET_VARSIZE()</CODE
> to store the size of the datum
in this field and <CODE
CLASS="FUNCTION"
>VARSIZE()</CODE
> to retrieve it. The C
functions operating on the data type must always be careful to unpack any
toasted values they are handed, by using <CODE
CLASS="FUNCTION"
>PG_DETOAST_DATUM</CODE
>.
(This detail is customarily hidden by defining type-specific
<CODE
CLASS="FUNCTION"
>GETARG_DATATYPE_P</CODE
> macros.) Then, when running the
<TT
CLASS="COMMAND"
>CREATE TYPE</TT
> command, specify the internal length as
<TT
CLASS="LITERAL"
>variable</TT
> and select the appropriate storage option.
</P
><P
> If the alignment is unimportant (either just for a specific function or
because the data type specifies byte alignment anyways) then it's possible
to avoid some of the overhead of <CODE
CLASS="FUNCTION"
>PG_DETOAST_DATUM</CODE
>. You can use
<CODE
CLASS="FUNCTION"
>PG_DETOAST_DATUM_PACKED</CODE
> instead (customarily hidden by
defining a <CODE
CLASS="FUNCTION"
>GETARG_DATATYPE_PP</CODE
> macro) and using the macros
<CODE
CLASS="FUNCTION"
>VARSIZE_ANY_EXHDR</CODE
> and <CODE
CLASS="FUNCTION"
>VARDATA_ANY</CODE
> to access
a potentially-packed datum.
Again, the data returned by these macros is not aligned even if the data
type definition specifies an alignment. If the alignment is important you
must go through the regular <CODE
CLASS="FUNCTION"
>PG_DETOAST_DATUM</CODE
> interface.
</P
><DIV
CLASS="NOTE"
><BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Note: </B
> Older code frequently declares <TT
CLASS="STRUCTFIELD"
>vl_len_</TT
> as an
<TT
CLASS="TYPE"
>int32</TT
> field instead of <TT
CLASS="TYPE"
>char[4]</TT
>. This is OK as long as
the struct definition has other fields that have at least <TT
CLASS="TYPE"
>int32</TT
>
alignment. But it is dangerous to use such a struct definition when
working with a potentially unaligned datum; the compiler may take it as
license to assume the datum actually is aligned, leading to core dumps on
architectures that are strict about alignment.
</P
></BLOCKQUOTE
></DIV
><P
> For further details see the description of the
<A
HREF="sql-createtype.html"
><I
>CREATE TYPE</I
></A
> command.
</P
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
SUMMARY="Footer navigation table"
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="xaggr.html"
ACCESSKEY="P"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="index.html"
ACCESSKEY="H"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="xoper.html"
ACCESSKEY="N"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>User-Defined Aggregates</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="extend.html"
ACCESSKEY="U"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>User-Defined Operators</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>
distrib
>
Mandriva
>
2008.1
>
x86_64
>
media
>
main-testing
>
by-pkgid
>
bab02a23fa9f3df8d66a9a3231b50245
>
files
>
932
