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
|
<chapter id="xtypes">
<title>Extending <acronym>SQL</acronym>: Types</title>
<indexterm zone="xtypes">
<primary>data types</primary>
<secondary>extending</secondary>
</indexterm>
<para>
As previously mentioned, there are two kinds of types
in <productname>PostgreSQL</productname>: base types (defined in a programming language)
and composite types.
Examples in this section up to interfacing indexes can
be found in <filename>complex.sql</filename> and <filename>complex.c</filename>. Composite examples
are in <filename>funcs.sql</filename>.
</para>
<sect1 id="xtypes-userdefined">
<title>User-Defined Types</title>
<sect2>
<title>Functions Needed for a User-Defined Type</title>
<para>
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-delimited 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
delimited character string.
Suppose we want to define a complex type which represents
complex numbers. Naturally, we choose to represent a
complex in memory as the following <acronym>C</acronym> structure:
<programlisting>
typedef struct Complex {
double x;
double y;
} Complex;
</programlisting>
and a string of the form (x,y) as the external string
representation.
These functions are usually not hard to write, especially
the output function. However, there are a number of points
to remember:
<itemizedlist>
<listitem>
<para> When defining your external (string) representation,
remember that you must eventually write a
complete and robust parser for that representation
as your input function!
<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>
</listitem>
<listitem>
<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 (say, into someone else's database on
another computer). This is a particularly common
problem when floating-point numbers are
involved.
</para>
</listitem>
</itemizedlist>
</para>
<para>
To define the <acronym>complex</acronym> type, we need to create the two
user-defined functions complex_in and complex_out
before creating the type:
<programlisting>
CREATE FUNCTION complex_in(opaque)
RETURNS complex
AS '<replaceable>PGROOT</replaceable>/tutorial/complex'
LANGUAGE C;
CREATE FUNCTION complex_out(opaque)
RETURNS opaque
AS '<replaceable>PGROOT</replaceable>/tutorial/complex'
LANGUAGE C;
CREATE TYPE complex (
internallength = 16,
input = complex_in,
output = complex_out
);
</programlisting>
</para>
<para>
As discussed earlier, <productname>PostgreSQL</productname> fully supports arrays of
base types. Additionally, <productname>PostgreSQL</productname> supports arrays of
user-defined types as well. When you define a type,
<productname>PostgreSQL</productname> 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.
</para>
</sect2>
<sect2>
<title>Large Objects</title>
<para>
If the values of your datatype might exceed a few hundred bytes in
size (in internal form), you should be careful to mark them TOASTable.
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.
</para>
</sect2>
</sect1>
</chapter>
<!-- 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:
-->
|
