1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
|
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>32.11.User-Defined Types</title>
<link rel="stylesheet" href="stylesheet.css" type="text/css">
<link rev="made" href="pgsql-docs@postgresql.org">
<meta name="generator" content="DocBook XSL Stylesheets V1.70.0">
<link rel="start" href="index.html" title="PostgreSQL 8.1.4 Documentation">
<link rel="up" href="extend.html" title="Chapter32.Extending SQL">
<link rel="prev" href="xaggr.html" title="32.10.User-Defined Aggregates">
<link rel="next" href="xoper.html" title="32.12.User-Defined Operators">
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="sect1" lang="en">
<div class="titlepage"><div><div><h2 class="title" style="clear: both">
<a name="xtypes"></a>32.11.User-Defined Types</h2></div></div></div>
<a name="id710918"></a><p> As described in <a href="extend-type-system.html" title="32.2.The PostgreSQL Type System">Section32.2, “The <span class="productname">PostgreSQL</span> Type System”</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
<code class="filename">complex.sql</code> and <code class="filename">complex.c</code>
in the <code class="filename">src/tutorial</code> directory of the source distribution.
See the <code class="filename">README</code> file in that directory for instructions
about running the examples.
</p>
<p> <a name="id710991"></a>
<a name="id710998"></a>
A user-defined type must always have input and output
functions.<a name="id711007"></a><a name="id711016"></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 <code class="type">complex</code> 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 <code class="type">Datum</code> value.
</p>
<p> As the external string representation of the type, we choose a
string of the form <code class="literal">(x,y)</code>.
</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
<code class="type">complex</code>, we will piggy-back on the binary I/O converters
for type <code class="type">float8</code>:
</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> To define the <code class="type">complex</code> type, we need to create the
user-defined I/O functions before creating the type:
</p>
<pre class="programlisting">CREATE FUNCTION complex_in(cstring)
RETURNS complex
AS '<em class="replaceable"><code>filename</code></em>'
LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION complex_out(complex)
RETURNS cstring
AS '<em class="replaceable"><code>filename</code></em>'
LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION complex_recv(internal)
RETURNS complex
AS '<em class="replaceable"><code>filename</code></em>'
LANGUAGE C IMMUTABLE STRICT;
CREATE FUNCTION complex_send(complex)
RETURNS bytea
AS '<em class="replaceable"><code>filename</code></em>'
LANGUAGE C IMMUTABLE STRICT;</pre>
<p>
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. The input function must
appear first.
</p>
<p> Finally, we can declare 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="id711237"></a> For historical reasons, the array type
has the same name as the base type with the underscore character
(<code class="literal">_</code>) 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="id711266"></a>
If the values of your data type might exceed a few hundred bytes in
size (in internal form), you should make the data type
<acronym class="acronym">TOAST</acronym>-able (see <a href="storage-toast.html" title="50.2.TOAST">Section50.2, “TOAST”</a>).
To do this, the internal
representation must follow the standard layout for variable-length
data: the first four bytes must be an <code class="type">int32</code> containing
the total length in bytes of the datum (including itself). The C
functions operating on the data type must 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</code> macros.) Then,
when running the <code class="command">CREATE TYPE</code> command, specify the
internal length as <code class="literal">variable</code> and select the appropriate
storage option.
</p>
<p> For further details see the description of the
<a href="sql-createtype.html">CREATE TYPE</a> command.
</p>
</div></body>
</html>
|
