1CREATE TYPE() SQL Commands CREATE TYPE()
2
6 CREATE TYPE - define a new data type
7
10 CREATE TYPE name AS
11 ( attribute_name data_type [, ... ] )
12 CREATE TYPE name (
14 INPUT = input_function,
15 OUTPUT = output_function
16 [ , RECEIVE = receive_function ]
17 [ , SEND = send_function ]
18 [ , ANALYZE = analyze_function ]
19 [ , INTERNALLENGTH = { internallength | VARIABLE } ]
20 [ , PASSEDBYVALUE ]
21 [ , ALIGNMENT = alignment ]
22 [ , STORAGE = storage ]
23 [ , DEFAULT = default ]
24 [ , ELEMENT = element ]
25 [ , DELIMITER = delimiter ]
26 )
27 CREATE TYPE name
29
32 CREATE TYPE registers a new data type for use in the current database.
33 The user who defines a type becomes its owner.
34 If a schema name is given then the type is created in the specified
36 schema. Otherwise it is created in the current schema. The type name
37 must be distinct from the name of any existing type or domain in the
38 same schema. (Because tables have associated data types, the type name
39 must also be distinct from the name of any existing table in the same
40 schema.)
41 COMPOSITE TYPES
43 The first form of CREATE TYPE creates a composite type. The composite
44 type is specified by a list of attribute names and data types. This is
45 essentially the same as the row type of a table, but using CREATE TYPE
46 avoids the need to create an actual table when all that is wanted is to
47 define a type. A stand-alone composite type is useful as the argument
48 or return type of a function.
49 BASE TYPES
51 The second form of CREATE TYPE creates a new base type (scalar type).
52 The parameters may appear in any order, not only that illustrated
53 above, and most are optional. You must register two or more functions
54 (using CREATE FUNCTION) before defining the type. The support functions
55 input_function and output_function are required, while the functions
56 receive_function, send_function and analyze_function are optional. Gen‐
57 erally these functions have to be coded in C or another low-level lan‐
58 guage.
59 The input_function converts the type's external textual representation
61 to the internal representation used by the operators and functions
62 defined for the type. output_function performs the reverse transforma‐
63 tion. The input function may be declared as taking one argument of type
64 cstring, or as taking three arguments of types cstring, oid, integer.
65 The first argument is the input text as a C string, the second argument
66 is the type's own OID (except for array types, which instead receive
67 their element type's OID), and the third is the typmod of the destina‐
68 tion column, if known (-1 will be passed if not). The input function
69 must return a value of the data type itself. Usually, an input func‐
70 tion should be declared STRICT; if it is not, it will be called with a
71 NULL first parameter when reading a NULL input value. The function must
72 still return NULL in this case, unless it raises an error. (This case
73 is mainly meant to support domain input functions, which may need to
74 reject NULL inputs.) The output function must be declared as taking
75 one argument of the new data type. The output function must return
76 type cstring. Output functions are not invoked for NULL values.
77 The optional receive_function converts the type's external binary rep‐
79 resentation to the internal representation. If this function is not
80 supplied, the type cannot participate in binary input. The binary rep‐
81 resentation should be chosen to be cheap to convert to internal form,
82 while being reasonably portable. (For example, the standard integer
83 data types use network byte order as the external binary representa‐
84 tion, while the internal representation is in the machine's native byte
85 order.) The receive function should perform adequate checking to ensure
86 that the value is valid. The receive function may be declared as tak‐
87 ing one argument of type internal, or as taking three arguments of
88 types internal, oid, integer. The first argument is a pointer to a
89 StringInfo buffer holding the received byte string; the optional argu‐
90 ments are the same as for the text input function. The receive func‐
91 tion must return a value of the data type itself. Usually, a receive
92 function should be declared STRICT; if it is not, it will be called
93 with a NULL first parameter when reading a NULL input value. The func‐
94 tion must still return NULL in this case, unless it raises an error.
95 (This case is mainly meant to support domain receive functions, which
96 may need to reject NULL inputs.) Similarly, the optional send_function
97 converts from the internal representation to the external binary repre‐
98 sentation. If this function is not supplied, the type cannot partici‐
99 pate in binary output. The send function must be declared as taking one
100 argument of the new data type. The send function must return type
101 bytea. Send functions are not invoked for NULL values.
102 You should at this point be wondering how the input and output func‐
104 tions can be declared to have results or arguments of the new type,
105 when they have to be created before the new type can be created. The
106 answer is that the type should first be defined as a shell type, which
107 is a placeholder type that has no properties except a name and an
108 owner. This is done by issuing the command CREATE TYPE name, with no
109 additional parameters. Then the I/O functions can be defined referenc‐
110 ing the shell type. Finally, CREATE TYPE with a full definition
111 replaces the shell entry with a complete, valid type definition, after
112 which the new type can be used normally.
113 The optional analyze_function performs type-specific statistics collec‐
115 tion for columns of the data type. By default, ANALYZE will attempt to
116 gather statistics using the type's ``equals'' and ``less-than'' opera‐
117 tors, if there is a default b-tree operator class for the type. For
118 non-scalar types this behavior is likely to be unsuitable, so it can be
119 overridden by specifying a custom analysis function. The analysis func‐
120 tion must be declared to take a single argument of type internal, and
121 return a boolean result. The detailed API for analysis functions
122 appears in src/include/commands/vacuum.h.
123 While the details of the new type's internal representation are only
125 known to the I/O functions and other functions you create to work with
126 the type, there are several properties of the internal representation
127 that must be declared to PostgreSQL. Foremost of these is internal‐
128 length. Base data types can be fixed-length, in which case internal‐
129 length is a positive integer, or variable length, indicated by setting
130 internallength to VARIABLE. (Internally, this is represented by setting
131 typlen to -1.) The internal representation of all variable-length types
132 must start with a 4-byte integer giving the total length of this value
133 of the type.
134 The optional flag PASSEDBYVALUE indicates that values of this data type
136 are passed by value, rather than by reference. You may not pass by
137 value types whose internal representation is larger than the size of
138 the Datum type (4 bytes on most machines, 8 bytes on a few).
139 The alignment parameter specifies the storage alignment required for
141 the data type. The allowed values equate to alignment on 1, 2, 4, or 8
142 byte boundaries. Note that variable-length types must have an align‐
143 ment of at least 4, since they necessarily contain an int4 as their
144 first component.
145 The storage parameter allows selection of storage strategies for vari‐
147 able-length data types. (Only plain is allowed for fixed-length types.)
148 plain specifies that data of the type will always be stored in-line and
149 not compressed. extended specifies that the system will first try to
150 compress a long data value, and will move the value out of the main ta‐
151 ble row if it's still too long. external allows the value to be moved
152 out of the main table, but the system will not try to compress it.
153 main allows compression, but discourages moving the value out of the
154 main table. (Data items with this storage strategy may still be moved
155 out of the main table if there is no other way to make a row fit, but
156 they will be kept in the main table preferentially over extended and
157 external items.)
158 A default value may be specified, in case a user wants columns of the
160 data type to default to something other than the null value. Specify
161 the default with the DEFAULT key word. (Such a default may be overrid‐
162 den by an explicit DEFAULT clause attached to a particular column.)
163 To indicate that a type is an array, specify the type of the array ele‐
165 ments using the ELEMENT key word. For example, to define an array of
166 4-byte integers (int4), specify ELEMENT = int4. More details about
167 array types appear below.
168 To indicate the delimiter to be used between values in the external
170 representation of arrays of this type, delimiter can be set to a spe‐
171 cific character. The default delimiter is the comma (,). Note that the
172 delimiter is associated with the array element type, not the array type
173 itself.
174 ARRAY TYPES
176 Whenever a user-defined base data type is created, PostgreSQL automati‐
177 cally creates an associated array type, whose name consists of the base
178 type's name prepended with an underscore. The parser understands this
179 naming convention, and translates requests for columns of type foo[]
180 into requests for type _foo. The implicitly-created array type is
181 variable length and uses the built-in input and output functions
182 array_in and array_out.
183 You might reasonably ask why there is an ELEMENT option, if the system
185 makes the correct array type automatically. The only case where it's
186 useful to use ELEMENT is when you are making a fixed-length type that
187 happens to be internally an array of a number of identical things, and
188 you want to allow these things to be accessed directly by subscripting,
189 in addition to whatever operations you plan to provide for the type as
190 a whole. For example, type name allows its constituent char elements to
191 be accessed this way. A 2-D point type could allow its two component
192 numbers to be accessed like point[0] and point[1]. Note that this
193 facility only works for fixed-length types whose internal form is
194 exactly a sequence of identical fixed-length fields. A subscriptable
195 variable-length type must have the generalized internal representation
196 used by array_in and array_out. For historical reasons (i.e., this is
197 clearly wrong but it's far too late to change it), subscripting of
198 fixed-length array types starts from zero, rather than from one as for
199 variable-length arrays.
200
202 name The name (optionally schema-qualified) of a type to be created.
203 attribute_name
205 The name of an attribute (column) for the composite type.
206 data_type
208 The name of an existing data type to become a column of the com‐
209 posite type.
210 input_function
212 The name of a function that converts data from the type's exter‐
213 nal textual form to its internal form.
214 output_function
216 The name of a function that converts data from the type's inter‐
217 nal form to its external textual form.
218 receive_function
220 The name of a function that converts data from the type's exter‐
221 nal binary form to its internal form.
222 send_function
224 The name of a function that converts data from the type's inter‐
225 nal form to its external binary form.
226 analyze_function
228 The name of a function that performs statistical analysis for
229 the data type.
230 internallength
232 A numeric constant that specifies the length in bytes of the new
233 type's internal representation. The default assumption is that
234 it is variable-length.
235 alignment
237 The storage alignment requirement of the data type. If speci‐
238 fied, it must be char, int2, int4, or double; the default is
239 int4.
240 storage
242 The storage strategy for the data type. If specified, must be
243 plain, external, extended, or main; the default is plain.
244 default
246 The default value for the data type. If this is omitted, the
247 default is null.
248 element
250 The type being created is an array; this specifies the type of
251 the array elements.
252 delimiter
254 The delimiter character to be used between values in arrays made
255 of this type.
256
258 User-defined type names cannot begin with the underscore character (_)
259 and can only be 62 characters long (or in general NAMEDATALEN - 2,
260 rather than the NAMEDATALEN - 1 characters allowed for other names).
261 Type names beginning with underscore are reserved for internally-cre‐
262 ated array type names.
263 Because there are no restrictions on use of a data type once it's been
265 created, creating a base type is tantamount to granting public execute
266 permission on the functions mentioned in the type definition. (The cre‐
267 ator of the type is therefore required to own these functions.) This is
268 usually not an issue for the sorts of functions that are useful in a
269 type definition. But you might want to think twice before designing a
270 type in a way that would require ``secret'' information to be used
271 while converting it to or from external form.
272 Before PostgreSQL version 8.2, the syntax CREATE TYPE name did not
274 exist. The way to create a new base type was to create its input func‐
275 tion first. In this approach, PostgreSQL will first see the name of
276 the new data type as the return type of the input function. The shell
277 type is implicitly created in this situation, and then it can be refer‐
278 enced in the definitions of the remaining I/O functions. This approach
279 still works, but is deprecated and may be disallowed in some future
280 release. Also, to avoid accidentally cluttering the catalogs with shell
281 types as a result of simple typos in function definitions, a shell type
282 will only be made this way when the input function is written in C.
283 In PostgreSQL versions before 7.3, it was customary to avoid creating a
285 shell type at all, by replacing the functions' forward references to
286 the type name with the placeholder pseudotype opaque. The cstring argu‐
287 ments and results also had to be declared as opaque before 7.3. To sup‐
288 port loading of old dump files, CREATE TYPE will accept I/O functions
289 declared using opaque, but it will issue a notice and change the func‐
290 tion declarations to use the correct types.
291
293 This example creates a composite type and uses it in a function defini‐
294 tion:
295 CREATE TYPE compfoo AS (f1 int, f2 text);
297 CREATE FUNCTION getfoo() RETURNS SETOF compfoo AS $$
299 SELECT fooid, fooname FROM foo
300 $$ LANGUAGE SQL;
301 This example creates the base data type box and then uses the type in a
304 table definition:
305 CREATE TYPE box;
307 CREATE FUNCTION my_box_in_function(cstring) RETURNS box AS ... ;
309 CREATE FUNCTION my_box_out_function(box) RETURNS cstring AS ... ;
310 CREATE TYPE box (
312 INTERNALLENGTH = 16,
313 INPUT = my_box_in_function,
314 OUTPUT = my_box_out_function
315 );
316 CREATE TABLE myboxes (
318 id integer,
319 description box
320 );
321 If the internal structure of box were an array of four float4 elements,
324 we might instead use
325 CREATE TYPE box (
327 INTERNALLENGTH = 16,
328 INPUT = my_box_in_function,
329 OUTPUT = my_box_out_function,
330 ELEMENT = float4
331 );
332 which would allow a box value's component numbers to be accessed by
334 subscripting. Otherwise the type behaves the same as before.
335 This example creates a large object type and uses it in a table defini‐
337 tion:
338 CREATE TYPE bigobj (
340 INPUT = lo_filein, OUTPUT = lo_fileout,
341 INTERNALLENGTH = VARIABLE
342 );
343 CREATE TABLE big_objs (
344 id integer,
345 obj bigobj
346 );
347 More examples, including suitable input and output functions, are in in
350 the documentation.
351
353 This CREATE TYPE command is a PostgreSQL extension. There is a CREATE
354 TYPE statement in the SQL standard that is rather different in detail.
355
357 CREATE FUNCTION [create_function(7)], DROP TYPE [drop_type(l)], ALTER
358 TYPE [alter_type(l)], CREATE DOMAIN [create_domain(l)]
359SQL - Language Statements 2008-06-08 CREATE TYPE()