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
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
|
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>CREATE TYPE</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="sql-commands.html" title="SQL Commands">
<link rel="prev" href="sql-createtrigger.html" title="CREATE TRIGGER">
<link rel="next" href="sql-createuser.html" title="CREATE USER">
<link rel="copyright" href="ln-legalnotice.html" title="Legal Notice">
</head>
<body bgcolor="white" text="black" link="#0000FF" vlink="#840084" alink="#0000FF"><div class="refentry" lang="en">
<a name="sql-createtype"></a><div class="titlepage"></div>
<div class="refnamediv">
<h2>Name</h2>
<p>CREATE TYPE — define a new data type</p>
</div>
<a name="id766024"></a><div class="refsynopsisdiv">
<h2>Synopsis</h2>
<pre class="synopsis">CREATE TYPE <em class="replaceable"><code>name</code></em> AS
( <em class="replaceable"><code>attribute_name</code></em> <em class="replaceable"><code>data_type</code></em> [, ... ] )
CREATE TYPE <em class="replaceable"><code>name</code></em> (
INPUT = <em class="replaceable"><code>input_function</code></em>,
OUTPUT = <em class="replaceable"><code>output_function</code></em>
[ , RECEIVE = <em class="replaceable"><code>receive_function</code></em> ]
[ , SEND = <em class="replaceable"><code>send_function</code></em> ]
[ , ANALYZE = <em class="replaceable"><code>analyze_function</code></em> ]
[ , INTERNALLENGTH = { <em class="replaceable"><code>internallength</code></em> | VARIABLE } ]
[ , PASSEDBYVALUE ]
[ , ALIGNMENT = <em class="replaceable"><code>alignment</code></em> ]
[ , STORAGE = <em class="replaceable"><code>storage</code></em> ]
[ , DEFAULT = <em class="replaceable"><code>default</code></em> ]
[ , ELEMENT = <em class="replaceable"><code>element</code></em> ]
[ , DELIMITER = <em class="replaceable"><code>delimiter</code></em> ]
)</pre>
</div>
<div class="refsect1" lang="en">
<a name="id766146"></a><h2>Description</h2>
<p> <code class="command">CREATE TYPE</code> registers a new data type for use in
the current database. The user who defines a type becomes its
owner.
</p>
<p> If a schema name is given then the type is created in the specified
schema. Otherwise it is created in the current schema. The type
name must be distinct from the name of any existing type or domain
in the same schema. (Because tables have associated data types,
the type name must also be distinct from the name of any existing
table in the same schema.)
</p>
<div class="refsect2" lang="en">
<a name="id766170"></a><h3>Composite Types</h3>
<p> The first form of <code class="command">CREATE TYPE</code>
creates a composite type.
The composite type is specified by a list of attribute names and data types.
This is essentially the same as the row type
of a table, but using <code class="command">CREATE TYPE</code> avoids the need to
create an actual table when all that is wanted is to define a type.
A stand-alone composite type is useful as the argument or return type of a
function.
</p>
</div>
<div class="refsect2" lang="en">
<a name="id766196"></a><h3>Base Types</h3>
<p> The second form of <code class="command">CREATE TYPE</code> creates a new base type
(scalar type). The parameters may appear in any order, not only that
illustrated above, and most are optional. You must register
two or more functions (using <code class="command">CREATE FUNCTION</code>) before
defining the type. The support functions
<em class="replaceable"><code>input_function</code></em> and
<em class="replaceable"><code>output_function</code></em>
are required, while the functions
<em class="replaceable"><code>receive_function</code></em>,
<em class="replaceable"><code>send_function</code></em> and
<em class="replaceable"><code>analyze_function</code></em>
are optional. Generally these functions have to be coded in C
or another low-level language.
</p>
<p> The <em class="replaceable"><code>input_function</code></em>
converts the type's external textual representation to the internal
representation used by the
operators and functions defined for the type.
<em class="replaceable"><code>output_function</code></em>
performs the reverse transformation. The input function may be
declared as taking one argument of type <code class="type">cstring</code>,
or as taking three arguments of types
<code class="type">cstring</code>, <code class="type">oid</code>, <code class="type">integer</code>.
The first argument is the input text as a C string, the second
argument is the type's own OID (except for array types, which instead
receive their element type's OID),
and the third is the <code class="literal">typmod</code> of the destination column, if known
(-1 will be passed if not).
The input function must return a value of the data type itself.
The output function must be
declared as taking one argument of the new data type.
The output function must return type <code class="type">cstring</code>.
</p>
<p> The optional <em class="replaceable"><code>receive_function</code></em>
converts the type's external binary representation to the internal
representation. If this function is not supplied, the type cannot
participate in binary input. The binary representation should be
chosen to be cheap to convert to internal form, while being reasonably
portable. (For example, the standard integer data types use network
byte order as the external binary representation, while the internal
representation is in the machine's native byte order.) The receive
function should perform adequate checking to ensure that the value is
valid.
The receive function may be declared as taking one argument of type
<code class="type">internal</code>, or as taking three arguments of types
<code class="type">internal</code>, <code class="type">oid</code>, <code class="type">integer</code>.
The first argument is a pointer to a <code class="type">StringInfo</code> buffer
holding the received byte string; the optional arguments are the
same as for the text input function.
The receive function must return a value of the data type itself.
Similarly, the optional
<em class="replaceable"><code>send_function</code></em> converts
from the internal representation to the external binary representation.
If this function is not supplied, the type cannot participate in binary
output. The send function must be
declared as taking one argument of the new data type.
The send function must return type <code class="type">bytea</code>.
</p>
<p> You should at this point be wondering how the input and output functions
can be declared to have results or arguments of the new type, when they have
to be created before the new type can be created. The answer is that the
input function must be created first, then the output function (and
the binary I/O functions if wanted), and finally the data type.
<span class="productname">PostgreSQL</span> will first see the name of the new
data type as the return type of the input function. It will create a
“<span class="quote">shell</span>” type, which is simply a placeholder entry in
the system catalog, and link the input function definition to the shell
type. Similarly the other functions will be linked to the (now already
existing) shell type. Finally, <code class="command">CREATE TYPE</code> replaces the
shell entry with a complete type definition, and the new type can be used.
</p>
<p> The optional <em class="replaceable"><code>analyze_function</code></em>
performs type-specific statistics collection for columns of the data type.
By default, <code class="command">ANALYZE</code> will attempt to gather statistics using
the type's “<span class="quote">equals</span>” and “<span class="quote">less-than</span>” operators, if there
is a default b-tree operator class for the type. For non-scalar types
this behavior is likely to be unsuitable, so it can be overridden by
specifying a custom analysis function. The analysis function must be
declared to take a single argument of type <code class="type">internal</code>, and return
a <code class="type">boolean</code> result. The detailed API for analysis functions appears
in <code class="filename">src/include/commands/vacuum.h</code>.
</p>
<p> While the details of the new type's internal representation are only
known to the I/O functions and other functions you create to work with
the type, there are several properties of the internal representation
that must be declared to <span class="productname">PostgreSQL</span>.
Foremost of these is
<em class="replaceable"><code>internallength</code></em>.
Base data types can be fixed-length, in which case
<em class="replaceable"><code>internallength</code></em> is a
positive integer, or variable length, indicated by setting
<em class="replaceable"><code>internallength</code></em>
to <code class="literal">VARIABLE</code>. (Internally, this is represented
by setting <code class="literal">typlen</code> to -1.) The internal representation of all
variable-length types must start with a 4-byte integer giving the total
length of this value of the type.
</p>
<p> The optional flag <code class="literal">PASSEDBYVALUE</code> indicates that
values of this data type are passed by value, rather than by
reference. You may not pass by value types whose internal
representation is larger than the size of the <code class="type">Datum</code> type
(4 bytes on most machines, 8 bytes on a few).
</p>
<p> The <em class="replaceable"><code>alignment</code></em> parameter
specifies the storage alignment required for the data type. The
allowed values equate to alignment on 1, 2, 4, or 8 byte boundaries.
Note that variable-length types must have an alignment of at least
4, since they necessarily contain an <code class="type">int4</code> as their first component.
</p>
<p> The <em class="replaceable"><code>storage</code></em> parameter
allows selection of storage strategies for variable-length data
types. (Only <code class="literal">plain</code> is allowed for fixed-length
types.) <code class="literal">plain</code> specifies that data of the type
will always be stored in-line and not compressed.
<code class="literal">extended</code> specifies that the system will first
try to compress a long data value, and will move the value out of
the main table row if it's still too long.
<code class="literal">external</code> allows the value to be moved out of the
main table, but the system will not try to compress it.
<code class="literal">main</code> allows compression, but discourages moving
the value out of the main table. (Data items with this storage
strategy may still be moved out of the main table if there is no
other way to make a row fit, but they will be kept in the main
table preferentially over <code class="literal">extended</code> and
<code class="literal">external</code> items.)
</p>
<p> A default value may be specified, in case a user wants columns of the
data type to default to something other than the null value.
Specify the default with the <code class="literal">DEFAULT</code> key word.
(Such a default may be overridden by an explicit <code class="literal">DEFAULT</code>
clause attached to a particular column.)
</p>
<p> To indicate that a type is an array, specify the type of the array
elements using the <code class="literal">ELEMENT</code> key word. For example, to
define an array of 4-byte integers (<code class="type">int4</code>), specify
<code class="literal">ELEMENT = int4</code>. More details about array types
appear below.
</p>
<p> To indicate the delimiter to be used between values in the external
representation of arrays of this type, <em class="replaceable"><code>delimiter</code></em> can be
set to a specific character. The default delimiter is the comma
(<code class="literal">,</code>). Note that the delimiter is associated
with the array element type, not the array type itself.
</p>
</div>
<div class="refsect2" lang="en">
<a name="id766677"></a><h3>Array Types</h3>
<p> Whenever a user-defined base data type is created,
<span class="productname">PostgreSQL</span> automatically creates an
associated array type, whose name consists of the base type's
name prepended with an underscore. The parser understands this
naming convention, and translates requests for columns of type
<code class="literal">foo[]</code> into requests for type <code class="literal">_foo</code>.
The implicitly-created array type is variable length and uses the
built-in input and output functions <code class="literal">array_in</code> and
<code class="literal">array_out</code>.
</p>
<p> You might reasonably ask why there is an <code class="option">ELEMENT</code>
option, if the system makes the correct array type automatically.
The only case where it's useful to use <code class="option">ELEMENT</code> is when you are
making a fixed-length type that happens to be internally an array of a number of
identical things, and you want to allow these things to be accessed
directly by subscripting, in addition to whatever operations you plan
to provide for the type as a whole. For example, type <code class="type">name</code>
allows its constituent <code class="type">char</code> elements to be accessed this way.
A 2-D <code class="type">point</code> type could allow its two component numbers to be
accessed like <code class="literal">point[0]</code> and <code class="literal">point[1]</code>.
Note that
this facility only works for fixed-length types whose internal form
is exactly a sequence of identical fixed-length fields. A subscriptable
variable-length type must have the generalized internal representation
used by <code class="literal">array_in</code> and <code class="literal">array_out</code>.
For historical reasons (i.e., this is clearly wrong but it's far too
late to change it), subscripting of fixed-length array types starts from
zero, rather than from one as for variable-length arrays.
</p>
</div>
</div>
<div class="refsect1" lang="en">
<a name="id766791"></a><h2>Parameters</h2>
<div class="variablelist"><dl>
<dt><span class="term"><em class="replaceable"><code>name</code></em></span></dt>
<dd><p> The name (optionally schema-qualified) of a type to be created.
</p></dd>
<dt><span class="term"><em class="replaceable"><code>attribute_name</code></em></span></dt>
<dd><p> The name of an attribute (column) for the composite type.
</p></dd>
<dt><span class="term"><em class="replaceable"><code>data_type</code></em></span></dt>
<dd><p> The name of an existing data type to become a column of the
composite type.
</p></dd>
<dt><span class="term"><em class="replaceable"><code>input_function</code></em></span></dt>
<dd><p> The name of a function that converts data from the type's
external textual form to its internal form.
</p></dd>
<dt><span class="term"><em class="replaceable"><code>output_function</code></em></span></dt>
<dd><p> The name of a function that converts data from the type's
internal form to its external textual form.
</p></dd>
<dt><span class="term"><em class="replaceable"><code>receive_function</code></em></span></dt>
<dd><p> The name of a function that converts data from the type's
external binary form to its internal form.
</p></dd>
<dt><span class="term"><em class="replaceable"><code>send_function</code></em></span></dt>
<dd><p> The name of a function that converts data from the type's
internal form to its external binary form.
</p></dd>
<dt><span class="term"><em class="replaceable"><code>analyze_function</code></em></span></dt>
<dd><p> The name of a function that performs statistical analysis for the
data type.
</p></dd>
<dt><span class="term"><em class="replaceable"><code>internallength</code></em></span></dt>
<dd><p> A numeric constant that specifies the length in bytes of the new
type's internal representation. The default assumption is that
it is variable-length.
</p></dd>
<dt><span class="term"><em class="replaceable"><code>alignment</code></em></span></dt>
<dd><p> The storage alignment requirement of the data type. If specified,
it must be <code class="literal">char</code>, <code class="literal">int2</code>,
<code class="literal">int4</code>, or <code class="literal">double</code>; the
default is <code class="literal">int4</code>.
</p></dd>
<dt><span class="term"><em class="replaceable"><code>storage</code></em></span></dt>
<dd><p> The storage strategy for the data type. If specified, must be
<code class="literal">plain</code>, <code class="literal">external</code>,
<code class="literal">extended</code>, or <code class="literal">main</code>; the
default is <code class="literal">plain</code>.
</p></dd>
<dt><span class="term"><em class="replaceable"><code>default</code></em></span></dt>
<dd><p> The default value for the data type. If this is omitted, the
default is null.
</p></dd>
<dt><span class="term"><em class="replaceable"><code>element</code></em></span></dt>
<dd><p> The type being created is an array; this specifies the type of
the array elements.
</p></dd>
<dt><span class="term"><em class="replaceable"><code>delimiter</code></em></span></dt>
<dd><p> The delimiter character to be used between values in arrays made
of this type.
</p></dd>
</dl></div>
</div>
<div class="refsect1" lang="en">
<a name="sql-createtype-notes"></a><h2>Notes</h2>
<p> User-defined type names cannot begin with the underscore character
(<code class="literal">_</code>) and can only be 62 characters
long (or in general <code class="symbol">NAMEDATALEN</code> - 2, rather than
the <code class="symbol">NAMEDATALEN</code> - 1 characters allowed for other
names). Type names beginning with underscore are reserved for
internally-created array type names.
</p>
<p> In <span class="productname">PostgreSQL</span> versions before 7.3, it
was customary to avoid creating a shell type by replacing the
functions' forward references to the type name with the placeholder
pseudotype <code class="type">opaque</code>. The <code class="type">cstring</code> arguments and
results also had to be declared as <code class="type">opaque</code> before 7.3. To
support loading of old dump files, <code class="command">CREATE TYPE</code> will
accept functions declared using <code class="type">opaque</code>, but it will issue
a notice and change the function's declaration to use the correct
types.
</p>
</div>
<div class="refsect1" lang="en">
<a name="id767118"></a><h2>Examples</h2>
<p> This example creates a composite type and uses it in
a function definition:
</p>
<pre class="programlisting">CREATE TYPE compfoo AS (f1 int, f2 text);
CREATE FUNCTION getfoo() RETURNS SETOF compfoo AS $$
SELECT fooid, fooname FROM foo
$$ LANGUAGE SQL;</pre>
<p>
</p>
<p> This example creates the base data type <code class="type">box</code> and then uses the
type in a table definition:
</p>
<pre class="programlisting">CREATE TYPE box (
INTERNALLENGTH = 16,
INPUT = my_box_in_function,
OUTPUT = my_box_out_function
);
CREATE TABLE myboxes (
id integer,
description box
);</pre>
<p>
</p>
<p> If the internal structure of <code class="type">box</code> were an array of four
<code class="type">float4</code> elements, we might instead use
</p>
<pre class="programlisting">CREATE TYPE box (
INTERNALLENGTH = 16,
INPUT = my_box_in_function,
OUTPUT = my_box_out_function,
ELEMENT = float4
);</pre>
<p>
which would allow a box value's component numbers to be accessed
by subscripting. Otherwise the type behaves the same as before.
</p>
<p> This example creates a large object type and uses it in
a table definition:
</p>
<pre class="programlisting">CREATE TYPE bigobj (
INPUT = lo_filein, OUTPUT = lo_fileout,
INTERNALLENGTH = VARIABLE
);
CREATE TABLE big_objs (
id integer,
obj bigobj
);</pre>
<p>
</p>
<p> More examples, including suitable input and output functions, are
in <a href="xtypes.html" title="32.11.User-Defined Types">Section32.11, “User-Defined Types”</a>.
</p>
</div>
<div class="refsect1" lang="en">
<a name="sql-createtype-compatibility"></a><h2>Compatibility</h2>
<p> This <code class="command">CREATE TYPE</code> command is a
<span class="productname">PostgreSQL</span> extension. There is a
<code class="command">CREATE TYPE</code> statement in the <acronym class="acronym">SQL</acronym> standard
that is rather different in detail.
</p>
</div>
<div class="refsect1" lang="en">
<a name="sql-createtype-see-also"></a><h2>See Also</h2>
<span class="simplelist"><a href="sql-createfunction.html">CREATE FUNCTION</a>, <a href="sql-droptype.html">DROP TYPE</a>, <a href="sql-altertype.html">ALTER TYPE</a></span>
</div>
</div></body>
</html>
|
