2 doc/src/sgml/ref/create_type.sgml
3 PostgreSQL documentation
6 <refentry id="SQL-CREATETYPE">
8 <refentrytitle>CREATE TYPE</refentrytitle>
9 <manvolnum>7</manvolnum>
10 <refmiscinfo>SQL - Language Statements</refmiscinfo>
14 <refname>CREATE TYPE</refname>
15 <refpurpose>define a new data type</refpurpose>
18 <indexterm zone="sql-createtype">
19 <primary>CREATE TYPE</primary>
24 CREATE TYPE <replaceable class="parameter">name</replaceable> AS
25 ( [ <replaceable class="PARAMETER">attribute_name</replaceable> <replaceable class="PARAMETER">data_type</replaceable> [ COLLATE <replaceable>collation</replaceable> ] [, ... ] ] )
27 CREATE TYPE <replaceable class="parameter">name</replaceable> AS ENUM
28 ( [ '<replaceable class="parameter">label</replaceable>' [, ... ] ] )
30 CREATE TYPE <replaceable class="parameter">name</replaceable> AS RANGE (
31 SUBTYPE = <replaceable class="parameter">subtype</replaceable>
32 [ , SUBTYPE_OPCLASS = <replaceable class="parameter">subtype_operator_class</replaceable> ]
33 [ , COLLATION = <replaceable class="parameter">collation</replaceable> ]
34 [ , CANONICAL = <replaceable class="parameter">canonical_function</replaceable> ]
35 [ , SUBTYPE_DIFF = <replaceable class="parameter">subtype_diff_function</replaceable> ]
38 CREATE TYPE <replaceable class="parameter">name</replaceable> (
39 INPUT = <replaceable class="parameter">input_function</replaceable>,
40 OUTPUT = <replaceable class="parameter">output_function</replaceable>
41 [ , RECEIVE = <replaceable class="parameter">receive_function</replaceable> ]
42 [ , SEND = <replaceable class="parameter">send_function</replaceable> ]
43 [ , TYPMOD_IN = <replaceable class="parameter">type_modifier_input_function</replaceable> ]
44 [ , TYPMOD_OUT = <replaceable class="parameter">type_modifier_output_function</replaceable> ]
45 [ , ANALYZE = <replaceable class="parameter">analyze_function</replaceable> ]
46 [ , INTERNALLENGTH = { <replaceable class="parameter">internallength</replaceable> | VARIABLE } ]
48 [ , ALIGNMENT = <replaceable class="parameter">alignment</replaceable> ]
49 [ , STORAGE = <replaceable class="parameter">storage</replaceable> ]
50 [ , LIKE = <replaceable class="parameter">like_type</replaceable> ]
51 [ , CATEGORY = <replaceable class="parameter">category</replaceable> ]
52 [ , PREFERRED = <replaceable class="parameter">preferred</replaceable> ]
53 [ , DEFAULT = <replaceable class="parameter">default</replaceable> ]
54 [ , ELEMENT = <replaceable class="parameter">element</replaceable> ]
55 [ , DELIMITER = <replaceable class="parameter">delimiter</replaceable> ]
56 [ , COLLATABLE = <replaceable class="parameter">collatable</replaceable> ]
59 CREATE TYPE <replaceable class="parameter">name</replaceable>
64 <title>Description</title>
67 <command>CREATE TYPE</command> registers a new data type for use in
68 the current database. The user who defines a type becomes its
73 If a schema name is given then the type is created in the specified
74 schema. Otherwise it is created in the current schema. The type
75 name must be distinct from the name of any existing type or domain
76 in the same schema. (Because tables have associated data types,
77 the type name must also be distinct from the name of any existing
78 table in the same schema.)
82 There are five forms of <command>CREATE TYPE</command>, as shown in the
83 syntax synopsis above. They respectively create a <firstterm>composite
84 type</>, an <firstterm>enum type</>, a <firstterm>range type</>, a
85 <firstterm>base type</>, or a <firstterm>shell type</>. The first four
86 of these are discussed in turn below. A shell type is simply a placeholder
87 for a type to be defined later; it is created by issuing <command>CREATE
88 TYPE</command> with no parameters except for the type name. Shell types
89 are needed as forward references when creating range types and base types,
90 as discussed in those sections.
94 <title>Composite Types</title>
97 The first form of <command>CREATE TYPE</command>
98 creates a composite type.
99 The composite type is specified by a list of attribute names and data types.
100 An attribute's collation can be specified too, if its data type is
101 collatable. A composite type is essentially the same as the row type
102 of a table, but using <command>CREATE TYPE</command> avoids the need to
103 create an actual table when all that is wanted is to define a type.
104 A stand-alone composite type is useful, for example, as the argument or
105 return type of a function.
109 <refsect2 id="SQL-CREATETYPE-enum">
110 <title>Enumerated Types</title>
113 The second form of <command>CREATE TYPE</command> creates an enumerated
114 (enum) type, as described in <xref linkend="datatype-enum">.
115 Enum types take a list of one or more quoted labels, each of which
116 must be less than <symbol>NAMEDATALEN</symbol> bytes long (64 bytes in a
117 standard <productname>PostgreSQL</productname> build).
121 <refsect2 id="SQL-CREATETYPE-RANGE">
122 <title>Range Types</title>
125 The third form of <command>CREATE TYPE</command> creates a new
126 range type, as described in <xref linkend="rangetypes">.
130 The range type's <replaceable class="parameter">subtype</replaceable> can
131 be any type with an associated b-tree operator class (to determine the
132 ordering of values for the range type). Normally the subtype's default
133 b-tree operator class is used to determine ordering; to use a non-default
134 opclass, specify its name with <replaceable
135 class="parameter">subtype_opclass</replaceable>. If the subtype is
136 collatable, and you want to use a non-default collation in the range's
137 ordering, specify the desired collation with the <replaceable
138 class="parameter">collation</replaceable> option.
142 The optional <replaceable class="parameter">canonical</replaceable>
143 function must take one argument of the range type being defined, and
144 return a value of the same type. This is used to convert range values
145 to a canonical form, when applicable. See <xref
146 linkend="rangetypes-defining"> for more information. Creating a
147 <replaceable class="parameter">canonical</replaceable> function
148 is a bit tricky, since it must be defined before the range type can be
149 declared. To do this, you must first create a shell type, which is a
150 placeholder type that has no properties except a name and an
151 owner. This is done by issuing the command <literal>CREATE TYPE
152 <replaceable>name</></literal>, with no additional parameters. Then
153 the function can be declared using the shell type as argument and result,
154 and finally the range type can be declared using the same name. This
155 automatically replaces the shell type entry with a valid range type.
159 The optional <replaceable class="parameter">subtype_diff</replaceable>
160 function must take two values of the
161 <replaceable class="parameter">subtype</replaceable> type as argument,
162 and return a <type>double precision</type> value representing the
163 difference between the two given values. While this is optional,
164 providing it allows much greater efficiency of GiST indexes on columns of
165 the range type. See <xref linkend="rangetypes-defining"> for more
171 <title>Base Types</title>
174 The fourth form of <command>CREATE TYPE</command> creates a new base type
175 (scalar type). To create a new base type, you must be a superuser.
176 (This restriction is made because an erroneous type definition could
177 confuse or even crash the server.)
181 The parameters can appear in any order, not only that
182 illustrated above, and most are optional. You must register
183 two or more functions (using <command>CREATE FUNCTION</command>) before
184 defining the type. The support functions
185 <replaceable class="parameter">input_function</replaceable> and
186 <replaceable class="parameter">output_function</replaceable>
187 are required, while the functions
188 <replaceable class="parameter">receive_function</replaceable>,
189 <replaceable class="parameter">send_function</replaceable>,
190 <replaceable class="parameter">type_modifier_input_function</replaceable>,
191 <replaceable class="parameter">type_modifier_output_function</replaceable> and
192 <replaceable class="parameter">analyze_function</replaceable>
193 are optional. Generally these functions have to be coded in C
194 or another low-level language.
198 The <replaceable class="parameter">input_function</replaceable>
199 converts the type's external textual representation to the internal
200 representation used by the operators and functions defined for the type.
201 <replaceable class="parameter">output_function</replaceable>
202 performs the reverse transformation. The input function can be
203 declared as taking one argument of type <type>cstring</type>,
204 or as taking three arguments of types
205 <type>cstring</type>, <type>oid</type>, <type>integer</type>.
206 The first argument is the input text as a C string, the second
207 argument is the type's own OID (except for array types, which instead
208 receive their element type's OID),
209 and the third is the <literal>typmod</> of the destination column, if known
210 (-1 will be passed if not).
211 The input function must return a value of the data type itself.
212 Usually, an input function should be declared STRICT; if it is not,
213 it will be called with a NULL first parameter when reading a NULL
214 input value. The function must still return NULL in this case, unless
216 (This case is mainly meant to support domain input functions, which
217 might need to reject NULL inputs.)
218 The output function must be
219 declared as taking one argument of the new data type.
220 The output function must return type <type>cstring</type>.
221 Output functions are not invoked for NULL values.
225 The optional <replaceable class="parameter">receive_function</replaceable>
226 converts the type's external binary representation to the internal
227 representation. If this function is not supplied, the type cannot
228 participate in binary input. The binary representation should be
229 chosen to be cheap to convert to internal form, while being reasonably
230 portable. (For example, the standard integer data types use network
231 byte order as the external binary representation, while the internal
232 representation is in the machine's native byte order.) The receive
233 function should perform adequate checking to ensure that the value is
235 The receive function can be declared as taking one argument of type
236 <type>internal</type>, or as taking three arguments of types
237 <type>internal</type>, <type>oid</type>, <type>integer</type>.
238 The first argument is a pointer to a <type>StringInfo</type> buffer
239 holding the received byte string; the optional arguments are the
240 same as for the text input function.
241 The receive function must return a value of the data type itself.
242 Usually, a receive function should be declared STRICT; if it is not,
243 it will be called with a NULL first parameter when reading a NULL
244 input value. The function must still return NULL in this case, unless
246 (This case is mainly meant to support domain receive functions, which
247 might need to reject NULL inputs.)
248 Similarly, the optional
249 <replaceable class="parameter">send_function</replaceable> converts
250 from the internal representation to the external binary representation.
251 If this function is not supplied, the type cannot participate in binary
252 output. The send function must be
253 declared as taking one argument of the new data type.
254 The send function must return type <type>bytea</type>.
255 Send functions are not invoked for NULL values.
259 You should at this point be wondering how the input and output functions
260 can be declared to have results or arguments of the new type, when they
261 have to be created before the new type can be created. The answer is that
262 the type should first be defined as a <firstterm>shell type</>, which is a
263 placeholder type that has no properties except a name and an owner. This
264 is done by issuing the command <literal>CREATE TYPE
265 <replaceable>name</></literal>, with no additional parameters. Then the
266 I/O functions can be defined referencing the shell type. Finally,
267 <command>CREATE TYPE</> with a full definition replaces the shell entry
268 with a complete, valid type definition, after which the new type can be
274 <replaceable class="parameter">type_modifier_input_function</replaceable>
275 and <replaceable class="parameter">type_modifier_output_function</replaceable>
276 are needed if the type supports modifiers, that is optional constraints
277 attached to a type declaration, such as <literal>char(5)</> or
278 <literal>numeric(30,2)</>. <productname>PostgreSQL</productname> allows
279 user-defined types to take one or more simple constants or identifiers as
280 modifiers. However, this information must be capable of being packed into a
281 single non-negative integer value for storage in the system catalogs. The
282 <replaceable class="parameter">type_modifier_input_function</replaceable>
283 is passed the declared modifier(s) in the form of a <type>cstring</>
284 array. It must check the values for validity (throwing an error if they
285 are wrong), and if they are correct, return a single non-negative
286 <type>integer</> value that will be stored as the column <quote>typmod</>.
287 Type modifiers will be rejected if the type does not have a
288 <replaceable class="parameter">type_modifier_input_function</replaceable>.
289 The <replaceable class="parameter">type_modifier_output_function</replaceable>
290 converts the internal integer typmod value back to the correct form for
291 user display. It must return a <type>cstring</> value that is the exact
292 string to append to the type name; for example <type>numeric</>'s
293 function might return <literal>(30,2)</>.
294 It is allowed to omit the
295 <replaceable class="parameter">type_modifier_output_function</replaceable>,
296 in which case the default display format is just the stored typmod integer
297 value enclosed in parentheses.
301 The optional <replaceable class="parameter">analyze_function</replaceable>
302 performs type-specific statistics collection for columns of the data type.
303 By default, <command>ANALYZE</> will attempt to gather statistics using
304 the type's <quote>equals</> and <quote>less-than</> operators, if there
305 is a default b-tree operator class for the type. For non-scalar types
306 this behavior is likely to be unsuitable, so it can be overridden by
307 specifying a custom analysis function. The analysis function must be
308 declared to take a single argument of type <type>internal</>, and return
309 a <type>boolean</> result. The detailed API for analysis functions appears
310 in <filename>src/include/commands/vacuum.h</>.
314 While the details of the new type's internal representation are only
315 known to the I/O functions and other functions you create to work with
316 the type, there are several properties of the internal representation
317 that must be declared to <productname>PostgreSQL</productname>.
319 <replaceable class="parameter">internallength</replaceable>.
320 Base data types can be fixed-length, in which case
321 <replaceable class="parameter">internallength</replaceable> is a
322 positive integer, or variable length, indicated by setting
323 <replaceable class="parameter">internallength</replaceable>
324 to <literal>VARIABLE</literal>. (Internally, this is represented
325 by setting <literal>typlen</> to -1.) The internal representation of all
326 variable-length types must start with a 4-byte integer giving the total
327 length of this value of the type.
331 The optional flag <literal>PASSEDBYVALUE</literal> indicates that
332 values of this data type are passed by value, rather than by
333 reference. You cannot pass by value types whose internal
334 representation is larger than the size of the <type>Datum</> type
335 (4 bytes on most machines, 8 bytes on a few).
339 The <replaceable class="parameter">alignment</replaceable> parameter
340 specifies the storage alignment required for the data type. The
341 allowed values equate to alignment on 1, 2, 4, or 8 byte boundaries.
342 Note that variable-length types must have an alignment of at least
343 4, since they necessarily contain an <type>int4</> as their first component.
347 The <replaceable class="parameter">storage</replaceable> parameter
348 allows selection of storage strategies for variable-length data
349 types. (Only <literal>plain</literal> is allowed for fixed-length
350 types.) <literal>plain</literal> specifies that data of the type
351 will always be stored in-line and not compressed.
352 <literal>extended</literal> specifies that the system will first
353 try to compress a long data value, and will move the value out of
354 the main table row if it's still too long.
355 <literal>external</literal> allows the value to be moved out of the
356 main table, but the system will not try to compress it.
357 <literal>main</literal> allows compression, but discourages moving
358 the value out of the main table. (Data items with this storage
359 strategy might still be moved out of the main table if there is no
360 other way to make a row fit, but they will be kept in the main
361 table preferentially over <literal>extended</literal> and
362 <literal>external</literal> items.)
366 The <replaceable class="parameter">like_type</replaceable> parameter
367 provides an alternative method for specifying the basic representation
368 properties of a data type: copy them from some existing type. The values of
369 <replaceable class="parameter">internallength</replaceable>,
370 <replaceable class="parameter">passedbyvalue</replaceable>,
371 <replaceable class="parameter">alignment</replaceable>, and
372 <replaceable class="parameter">storage</replaceable> are copied from the
373 named type. (It is possible, though usually undesirable, to override
374 some of these values by specifying them along with the <literal>LIKE</>
375 clause.) Specifying representation this way is especially useful when
376 the low-level implementation of the new type <quote>piggybacks</> on an
377 existing type in some fashion.
381 The <replaceable class="parameter">category</replaceable> and
382 <replaceable class="parameter">preferred</replaceable> parameters can be
383 used to help control which implicit cast will be applied in ambiguous
384 situations. Each data type belongs to a category named by a single ASCII
385 character, and each type is either <quote>preferred</> or not within its
386 category. The parser will prefer casting to preferred types (but only from
387 other types within the same category) when this rule is helpful in
388 resolving overloaded functions or operators. For more details see <xref
389 linkend="typeconv">. For types that have no implicit casts to or from any
390 other types, it is sufficient to leave these settings at the defaults.
391 However, for a group of related types that have implicit casts, it is often
392 helpful to mark them all as belonging to a category and select one or two
393 of the <quote>most general</> types as being preferred within the category.
394 The <replaceable class="parameter">category</replaceable> parameter is
395 especially useful when adding a user-defined type to an existing built-in
396 category, such as the numeric or string types. However, it is also
397 possible to create new entirely-user-defined type categories. Select any
398 ASCII character other than an upper-case letter to name such a category.
402 A default value can be specified, in case a user wants columns of the
403 data type to default to something other than the null value.
404 Specify the default with the <literal>DEFAULT</literal> key word.
405 (Such a default can be overridden by an explicit <literal>DEFAULT</literal>
406 clause attached to a particular column.)
410 To indicate that a type is an array, specify the type of the array
411 elements using the <literal>ELEMENT</> key word. For example, to
412 define an array of 4-byte integers (<type>int4</type>), specify
413 <literal>ELEMENT = int4</literal>. More details about array types
418 To indicate the delimiter to be used between values in the external
419 representation of arrays of this type, <replaceable
420 class="parameter">delimiter</replaceable> can be
421 set to a specific character. The default delimiter is the comma
422 (<literal>,</literal>). Note that the delimiter is associated
423 with the array element type, not the array type itself.
427 If the optional Boolean
428 parameter <replaceable class="parameter">collatable</replaceable>
429 is true, column definitions and expressions of the type may carry
430 collation information through use of
431 the <literal>COLLATE</literal> clause. It is up to the
432 implementations of the functions operating on the type to actually
433 make use of the collation information; this does not happen
434 automatically merely by marking the type collatable.
439 <title>Array Types</title>
442 Whenever a user-defined type is created,
443 <productname>PostgreSQL</productname> automatically creates an
444 associated array type, whose name consists of the element type's
445 name prepended with an underscore, and truncated if necessary to keep
446 it less than <symbol>NAMEDATALEN</symbol> bytes long. (If the name
447 so generated collides with an existing type name, the process is
448 repeated until a non-colliding name is found.)
449 This implicitly-created array type is variable length and uses the
450 built-in input and output functions <literal>array_in</> and
451 <literal>array_out</>. The array type tracks any changes in its
452 element type's owner or schema, and is dropped if the element type is.
456 You might reasonably ask why there is an <option>ELEMENT</>
457 option, if the system makes the correct array type automatically.
458 The only case where it's useful to use <option>ELEMENT</> is when you are
459 making a fixed-length type that happens to be internally an array of a number of
460 identical things, and you want to allow these things to be accessed
461 directly by subscripting, in addition to whatever operations you plan
462 to provide for the type as a whole. For example, type <type>point</>
463 is represented as just two floating-point numbers, which it allows to be
464 accessed as <literal>point[0]</> and <literal>point[1]</>.
466 this facility only works for fixed-length types whose internal form
467 is exactly a sequence of identical fixed-length fields. A subscriptable
468 variable-length type must have the generalized internal representation
469 used by <literal>array_in</> and <literal>array_out</>.
470 For historical reasons (i.e., this is clearly wrong but it's far too
471 late to change it), subscripting of fixed-length array types starts from
472 zero, rather than from one as for variable-length arrays.
478 <title>Parameters</title>
482 <term><replaceable class="parameter">name</replaceable></term>
485 The name (optionally schema-qualified) of a type to be created.
491 <term><replaceable class="parameter">attribute_name</replaceable></term>
494 The name of an attribute (column) for the composite type.
500 <term><replaceable class="parameter">data_type</replaceable></term>
503 The name of an existing data type to become a column of the
510 <term><replaceable class="parameter">collation</replaceable></term>
513 The name of an existing collation to be associated with a column of
514 a composite type, or with a range type.
520 <term><replaceable class="parameter">label</replaceable></term>
523 A string literal representing the textual label associated with
524 one value of an enum type.
530 <term><replaceable class="parameter">subtype</replaceable></term>
533 The name of the element type that the range type will represent ranges
540 <term><replaceable class="parameter">subtype_operator_class</replaceable></term>
543 The name of a b-tree operator class for the subtype.
549 <term><replaceable class="parameter">canonical_function</replaceable></term>
552 The name of the canonicalization function for the range type.
558 <term><replaceable class="parameter">subtype_diff_function</replaceable></term>
561 The name of a difference function for the subtype.
567 <term><replaceable class="parameter">input_function</replaceable></term>
570 The name of a function that converts data from the type's
571 external textual form to its internal form.
577 <term><replaceable class="parameter">output_function</replaceable></term>
580 The name of a function that converts data from the type's
581 internal form to its external textual form.
587 <term><replaceable class="parameter">receive_function</replaceable></term>
590 The name of a function that converts data from the type's
591 external binary form to its internal form.
597 <term><replaceable class="parameter">send_function</replaceable></term>
600 The name of a function that converts data from the type's
601 internal form to its external binary form.
607 <term><replaceable class="parameter">type_modifier_input_function</replaceable></term>
610 The name of a function that converts an array of modifier(s) for the type
617 <term><replaceable class="parameter">type_modifier_output_function</replaceable></term>
620 The name of a function that converts the internal form of the type's
621 modifier(s) to external textual form.
627 <term><replaceable class="parameter">analyze_function</replaceable></term>
630 The name of a function that performs statistical analysis for the
637 <term><replaceable class="parameter">internallength</replaceable></term>
640 A numeric constant that specifies the length in bytes of the new
641 type's internal representation. The default assumption is that
642 it is variable-length.
648 <term><replaceable class="parameter">alignment</replaceable></term>
651 The storage alignment requirement of the data type. If specified,
652 it must be <literal>char</literal>, <literal>int2</literal>,
653 <literal>int4</literal>, or <literal>double</literal>; the
654 default is <literal>int4</literal>.
660 <term><replaceable class="parameter">storage</replaceable></term>
663 The storage strategy for the data type. If specified, must be
664 <literal>plain</literal>, <literal>external</literal>,
665 <literal>extended</literal>, or <literal>main</literal>; the
666 default is <literal>plain</literal>.
672 <term><replaceable class="parameter">like_type</replaceable></term>
675 The name of an existing data type that the new type will have the
676 same representation as. The values of
677 <replaceable class="parameter">internallength</replaceable>,
678 <replaceable class="parameter">passedbyvalue</replaceable>,
679 <replaceable class="parameter">alignment</replaceable>, and
680 <replaceable class="parameter">storage</replaceable>
681 are copied from that type, unless overridden by explicit
682 specification elsewhere in this <command>CREATE TYPE</> command.
688 <term><replaceable class="parameter">category</replaceable></term>
691 The category code (a single ASCII character) for this type.
692 The default is <literal>'U'</> for <quote>user-defined type</>.
693 Other standard category codes can be found in
694 <xref linkend="catalog-typcategory-table">. You may also choose
695 other ASCII characters in order to create custom categories.
701 <term><replaceable class="parameter">preferred</replaceable></term>
704 True if this type is a preferred type within its type category,
705 else false. The default is false. Be very careful about creating
706 a new preferred type within an existing type category, as this
707 could cause surprising changes in behavior.
713 <term><replaceable class="parameter">default</replaceable></term>
716 The default value for the data type. If this is omitted, the
723 <term><replaceable class="parameter">element</replaceable></term>
726 The type being created is an array; this specifies the type of
733 <term><replaceable class="parameter">delimiter</replaceable></term>
736 The delimiter character to be used between values in arrays made
743 <term><replaceable class="parameter">collatable</replaceable></term>
746 True if this type's operations can use collation information.
747 The default is false.
754 <refsect1 id="SQL-CREATETYPE-notes">
758 Because there are no restrictions on use of a data type once it's been
759 created, creating a base type or range type is tantamount to granting
760 public execute permission on the functions mentioned in the type definition.
762 not an issue for the sorts of functions that are useful in a type
763 definition. But you might want to think twice before designing a type
764 in a way that would require <quote>secret</> information to be used
765 while converting it to or from external form.
769 Before <productname>PostgreSQL</productname> version 8.3, the name of
770 a generated array type was always exactly the element type's name with one
771 underscore character (<literal>_</literal>) prepended. (Type names were
772 therefore restricted in length to one less character than other names.)
773 While this is still usually the case, the array type name may vary from
774 this in case of maximum-length names or collisions with user type names
775 that begin with underscore. Writing code that depends on this convention
776 is therefore deprecated. Instead, use
777 <structname>pg_type</>.<structfield>typarray</> to locate the array type
778 associated with a given type.
782 It may be advisable to avoid using type and table names that begin with
783 underscore. While the server will change generated array type names to
784 avoid collisions with user-given names, there is still risk of confusion,
785 particularly with old client software that may assume that type names
786 beginning with underscores always represent arrays.
790 Before <productname>PostgreSQL</productname> version 8.2, the shell-type
792 <literal>CREATE TYPE <replaceable>name</></literal> did not exist.
793 The way to create a new base type was to create its input function first.
794 In this approach, <productname>PostgreSQL</productname> will first see
795 the name of the new data type as the return type of the input function.
796 The shell type is implicitly created in this situation, and then it
797 can be referenced in the definitions of the remaining I/O functions.
798 This approach still works, but is deprecated and might be disallowed in
799 some future release. Also, to avoid accidentally cluttering
800 the catalogs with shell types as a result of simple typos in function
801 definitions, a shell type will only be made this way when the input
802 function is written in C.
806 In <productname>PostgreSQL</productname> versions before 7.3, it
807 was customary to avoid creating a shell type at all, by replacing the
808 functions' forward references to the type name with the placeholder
809 pseudotype <type>opaque</>. The <type>cstring</> arguments and
810 results also had to be declared as <type>opaque</> before 7.3. To
811 support loading of old dump files, <command>CREATE TYPE</> will
812 accept I/O functions declared using <type>opaque</>, but it will issue
813 a notice and change the function declarations to use the correct
820 <title>Examples</title>
823 This example creates a composite type and uses it in
824 a function definition:
826 CREATE TYPE compfoo AS (f1 int, f2 text);
828 CREATE FUNCTION getfoo() RETURNS SETOF compfoo AS $$
829 SELECT fooid, fooname FROM foo
835 This example creates an enumerated type and uses it in
838 CREATE TYPE bug_status AS ENUM ('new', 'open', 'closed');
849 This example creates a range type:
851 CREATE TYPE float8_range AS RANGE (subtype = float8, subtype_diff = float8mi);
856 This example creates the base data type <type>box</type> and then uses the
857 type in a table definition:
861 CREATE FUNCTION my_box_in_function(cstring) RETURNS box AS ... ;
862 CREATE FUNCTION my_box_out_function(box) RETURNS cstring AS ... ;
866 INPUT = my_box_in_function,
867 OUTPUT = my_box_out_function
870 CREATE TABLE myboxes (
878 If the internal structure of <type>box</type> were an array of four
879 <type>float4</> elements, we might instead use:
883 INPUT = my_box_in_function,
884 OUTPUT = my_box_out_function,
888 which would allow a box value's component numbers to be accessed
889 by subscripting. Otherwise the type behaves the same as before.
893 This example creates a large object type and uses it in
897 INPUT = lo_filein, OUTPUT = lo_fileout,
898 INTERNALLENGTH = VARIABLE
900 CREATE TABLE big_objs (
908 More examples, including suitable input and output functions, are
909 in <xref linkend="xtypes">.
913 <refsect1 id="SQL-CREATETYPE-compatibility">
914 <title>Compatibility</title>
917 The first form of the <command>CREATE TYPE</command> command, which
918 creates a composite type, conforms to the <acronym>SQL</> standard.
919 The other forms are <productname>PostgreSQL</productname>
920 extensions. The <command>CREATE TYPE</command> statement in
921 the <acronym>SQL</> standard also defines other forms that are not
922 implemented in <productname>PostgreSQL</>.
926 The ability to create a composite type with zero attributes is
927 a <productname>PostgreSQL</productname>-specific deviation from the
928 standard (analogous to the same case in <command>CREATE TABLE</command>).
932 <refsect1 id="SQL-CREATETYPE-see-also">
933 <title>See Also</title>
935 <simplelist type="inline">
936 <member><xref linkend="sql-altertype"></member>
937 <member><xref linkend="sql-createdomain"></member>
938 <member><xref linkend="sql-createfunction"></member>
939 <member><xref linkend="sql-droptype"></member>