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
|
<!--
$Header: /cvsroot/pgsql/doc/src/sgml/xtypes.sgml,v 1.17 2003/04/10 01:22:45 petere Exp $
-->
<sect1 id="xtypes">
<title>User-Defined Types</title>
<indexterm zone="xtypes">
<primary>data types</primary>
<secondary>extending</secondary>
</indexterm>
<comment>
This section needs to be updated for the version-1 function manager
interface.
</comment>
<para>
As described above, there are two kinds of data types in
<productname>PostgreSQL</productname>: base types and composite
types. This section describes how to define new base types.
</para>
<para>
The examples in this section can be found in
<filename>complex.sql</filename> and <filename>complex.c</filename>
in the tutorial directory.
</para>
<para>
<indexterm>
<primary>input function</primary>
</indexterm>
<indexterm>
<primary>output function</primary>
</indexterm>
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 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.
</para>
<para>
Suppose we want to define a type <type>complex</> that represents
complex numbers. A natural way to to represent a complex number in
memory would be the following C structure:
<programlisting>
typedef struct Complex {
double x;
double y;
} Complex;
</programlisting>
As the external string representation of the type, we choose a
string of the form <literal>(x,y)</literal>.
</para>
<para>
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:
<programlisting>
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;
}
</programlisting>
The output function can simply be:
<programlisting>
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;
}
</programlisting>
</para>
<para>
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. This
is a particularly common problem when floating-point numbers are
involved.
</para>
<para>
To define the <type>complex</type> type, we need to create the two
user-defined functions <function>complex_in</function> and
<function>complex_out</function> before creating the type:
<programlisting>
CREATE FUNCTION complex_in(cstring)
RETURNS complex
AS '<replaceable>filename</replaceable>'
LANGUAGE C;
CREATE FUNCTION complex_out(complex)
RETURNS cstring
AS '<replaceable>filename</replaceable>'
LANGUAGE C;
</programlisting>
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.
</para>
<para>
Finally, we can declare the data type:
<programlisting>
CREATE TYPE complex (
internallength = 16,
input = complex_in,
output = complex_out
);
</programlisting>
</para>
<para>
When you define a new base type,
<productname>PostgreSQL</productname> automatically provides support
for arrays of that
type.<indexterm><primary>array</primary><secondary>of user-defined
type</secondary></indexterm> For historical reasons, the array type
has the same name as the base type with the underscore character
(<literal>_</>) prepended.
</para>
<para>
If the values of your data type might exceed a few hundred bytes in
size (in internal form), you should mark them
TOAST-able.<indexterm><primary>TOAST</primary><secondary>and
user-defined types</secondary></indexterm> To do this, the internal
representation must follow the standard layout for variable-length
data: the first four bytes must be an <type>int32</type> containing
the total length in bytes of the datum (including itself). Also,
when running the <command>CREATE TYPE</command> command, specify the
internal length as <literal>variable</> and select the appropriate
storage option.
</para>
<para>
For further details see the description of the <command>CREATE
TYPE</command> command in <xref linkend="reference">.
</para>
</sect1>
<!-- Keep this comment at the end of the file
Local variables:
mode:sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"./reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:("/usr/lib/sgml/catalog")
sgml-local-ecat-files:nil
End:
-->
|
