| 1 |
<!-- |
| 2 |
doc/src/sgml/ref/create_type.sgml |
| 3 |
PostgreSQL documentation |
| 4 |
--> |
| 5 |
|
| 6 |
<refentry id="sql-createtype"> |
| 7 |
<indexterm zone="sql-createtype"> |
| 8 |
<primary>CREATE TYPE</primary> |
| 9 |
</indexterm> |
| 10 |
|
| 11 |
<refmeta> |
| 12 |
<refentrytitle>CREATE TYPE</refentrytitle> |
| 13 |
<manvolnum>7</manvolnum> |
| 14 |
<refmiscinfo>SQL - Language Statements</refmiscinfo> |
| 15 |
</refmeta> |
| 16 |
|
| 17 |
<refnamediv> |
| 18 |
<refname>CREATE TYPE</refname> |
| 19 |
<refpurpose>define a new data type</refpurpose> |
| 20 |
</refnamediv> |
| 21 |
|
| 22 |
<refsynopsisdiv> |
| 23 |
<synopsis> |
| 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> ] [, ... ] ] ) |
| 26 |
|
| 27 |
CREATE TYPE <replaceable class="parameter">name</replaceable> AS ENUM |
| 28 |
( [ '<replaceable class="parameter">label</replaceable>' [, ... ] ] ) |
| 29 |
|
| 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> ] |
| 36 |
[ , MULTIRANGE_TYPE_NAME = <replaceable class="parameter">multirange_type_name</replaceable> ] |
| 37 |
) |
| 38 |
|
| 39 |
CREATE TYPE <replaceable class="parameter">name</replaceable> ( |
| 40 |
INPUT = <replaceable class="parameter">input_function</replaceable>, |
| 41 |
OUTPUT = <replaceable class="parameter">output_function</replaceable> |
| 42 |
[ , RECEIVE = <replaceable class="parameter">receive_function</replaceable> ] |
| 43 |
[ , SEND = <replaceable class="parameter">send_function</replaceable> ] |
| 44 |
[ , TYPMOD_IN = <replaceable class="parameter">type_modifier_input_function</replaceable> ] |
| 45 |
[ , TYPMOD_OUT = <replaceable class="parameter">type_modifier_output_function</replaceable> ] |
| 46 |
[ , ANALYZE = <replaceable class="parameter">analyze_function</replaceable> ] |
| 47 |
[ , SUBSCRIPT = <replaceable class="parameter">subscript_function</replaceable> ] |
| 48 |
[ , INTERNALLENGTH = { <replaceable class="parameter">internallength</replaceable> | VARIABLE } ] |
| 49 |
[ , PASSEDBYVALUE ] |
| 50 |
[ , ALIGNMENT = <replaceable class="parameter">alignment</replaceable> ] |
| 51 |
[ , STORAGE = <replaceable class="parameter">storage</replaceable> ] |
| 52 |
[ , LIKE = <replaceable class="parameter">like_type</replaceable> ] |
| 53 |
[ , CATEGORY = <replaceable class="parameter">category</replaceable> ] |
| 54 |
[ , PREFERRED = <replaceable class="parameter">preferred</replaceable> ] |
| 55 |
[ , DEFAULT = <replaceable class="parameter">default</replaceable> ] |
| 56 |
[ , ELEMENT = <replaceable class="parameter">element</replaceable> ] |
| 57 |
[ , DELIMITER = <replaceable class="parameter">delimiter</replaceable> ] |
| 58 |
[ , COLLATABLE = <replaceable class="parameter">collatable</replaceable> ] |
| 59 |
) |
| 60 |
|
| 61 |
CREATE TYPE <replaceable class="parameter">name</replaceable> |
| 62 |
</synopsis> |
| 63 |
</refsynopsisdiv> |
| 64 |
|
| 65 |
<refsect1> |
| 66 |
<title>Description</title> |
| 67 |
|
| 68 |
<para> |
| 69 |
<command>CREATE TYPE</command> registers a new data type for use in |
| 70 |
the current database. The user who defines a type becomes its |
| 71 |
owner. |
| 72 |
</para> |
| 73 |
|
| 74 |
<para> |
| 75 |
If a schema name is given then the type is created in the specified |
| 76 |
schema. Otherwise it is created in the current schema. The type |
| 77 |
name must be distinct from the name of any existing type or domain |
| 78 |
in the same schema. (Because tables have associated data types, |
| 79 |
the type name must also be distinct from the name of any existing |
| 80 |
table in the same schema.) |
| 81 |
</para> |
| 82 |
|
| 83 |
<para> |
| 84 |
There are five forms of <command>CREATE TYPE</command>, as shown in the |
| 85 |
syntax synopsis above. They respectively create a <firstterm>composite |
| 86 |
type</firstterm>, an <firstterm>enum type</firstterm>, a <firstterm>range type</firstterm>, a |
| 87 |
<firstterm>base type</firstterm>, or a <firstterm>shell type</firstterm>. The first four |
| 88 |
of these are discussed in turn below. A shell type is simply a placeholder |
| 89 |
for a type to be defined later; it is created by issuing <command>CREATE |
| 90 |
TYPE</command> with no parameters except for the type name. Shell types |
| 91 |
are needed as forward references when creating range types and base types, |
| 92 |
as discussed in those sections. |
| 93 |
</para> |
| 94 |
|
| 95 |
<refsect2> |
| 96 |
<title>Composite Types</title> |
| 97 |
|
| 98 |
<para> |
| 99 |
The first form of <command>CREATE TYPE</command> |
| 100 |
creates a composite type. |
| 101 |
The composite type is specified by a list of attribute names and data types. |
| 102 |
An attribute's collation can be specified too, if its data type is |
| 103 |
collatable. A composite type is essentially the same as the row type |
| 104 |
of a table, but using <command>CREATE TYPE</command> avoids the need to |
| 105 |
create an actual table when all that is wanted is to define a type. |
| 106 |
A stand-alone composite type is useful, for example, as the argument or |
| 107 |
return type of a function. |
| 108 |
</para> |
| 109 |
|
| 110 |
<para> |
| 111 |
To be able to create a composite type, you must |
| 112 |
have <literal>USAGE</literal> privilege on all attribute types. |
| 113 |
</para> |
| 114 |
</refsect2> |
| 115 |
|
| 116 |
<refsect2 id="sql-createtype-enum"> |
| 117 |
<title>Enumerated Types</title> |
| 118 |
|
| 119 |
<para> |
| 120 |
The second form of <command>CREATE TYPE</command> creates an enumerated |
| 121 |
(enum) type, as described in <xref linkend="datatype-enum"/>. |
| 122 |
Enum types take a list of quoted labels, each of which |
| 123 |
must be less than <symbol>NAMEDATALEN</symbol> bytes long (64 bytes in a |
| 124 |
standard <productname>PostgreSQL</productname> build). (It is possible to |
| 125 |
create an enumerated type with zero labels, but such a type cannot be used |
| 126 |
to hold values before at least one label is added using <link |
| 127 |
linkend="sql-altertype"><command>ALTER TYPE</command></link>.) |
| 128 |
</para> |
| 129 |
</refsect2> |
| 130 |
|
| 131 |
<refsect2 id="sql-createtype-range"> |
| 132 |
<title>Range Types</title> |
| 133 |
|
| 134 |
<para> |
| 135 |
The third form of <command>CREATE TYPE</command> creates a new |
| 136 |
range type, as described in <xref linkend="rangetypes"/>. |
| 137 |
</para> |
| 138 |
|
| 139 |
<para> |
| 140 |
The range type's <replaceable class="parameter">subtype</replaceable> can |
| 141 |
be any type with an associated b-tree operator class (to determine the |
| 142 |
ordering of values for the range type). Normally the subtype's default |
| 143 |
b-tree operator class is used to determine ordering; to use a non-default |
| 144 |
operator class, specify its name with <replaceable |
| 145 |
class="parameter">subtype_opclass</replaceable>. If the subtype is |
| 146 |
collatable, and you want to use a non-default collation in the range's |
| 147 |
ordering, specify the desired collation with the <replaceable |
| 148 |
class="parameter">collation</replaceable> option. |
| 149 |
</para> |
| 150 |
|
| 151 |
<para> |
| 152 |
The optional <replaceable class="parameter">canonical</replaceable> |
| 153 |
function must take one argument of the range type being defined, and |
| 154 |
return a value of the same type. This is used to convert range values |
| 155 |
to a canonical form, when applicable. See <xref |
| 156 |
linkend="rangetypes-defining"/> for more information. Creating a |
| 157 |
<replaceable class="parameter">canonical</replaceable> function |
| 158 |
is a bit tricky, since it must be defined before the range type can be |
| 159 |
declared. To do this, you must first create a shell type, which is a |
| 160 |
placeholder type that has no properties except a name and an |
| 161 |
owner. This is done by issuing the command <literal>CREATE TYPE |
| 162 |
<replaceable>name</replaceable></literal>, with no additional parameters. Then |
| 163 |
the function can be declared using the shell type as argument and result, |
| 164 |
and finally the range type can be declared using the same name. This |
| 165 |
automatically replaces the shell type entry with a valid range type. |
| 166 |
</para> |
| 167 |
|
| 168 |
<para> |
| 169 |
The optional <replaceable class="parameter">subtype_diff</replaceable> |
| 170 |
function must take two values of the |
| 171 |
<replaceable class="parameter">subtype</replaceable> type as argument, |
| 172 |
and return a <type>double precision</type> value representing the |
| 173 |
difference between the two given values. While this is optional, |
| 174 |
providing it allows much greater efficiency of GiST indexes on columns of |
| 175 |
the range type. See <xref linkend="rangetypes-defining"/> for more |
| 176 |
information. |
| 177 |
</para> |
| 178 |
|
| 179 |
<para> |
| 180 |
The optional <replaceable class="parameter">multirange_type_name</replaceable> |
| 181 |
parameter specifies the name of the corresponding multirange type. If not |
| 182 |
specified, this name is chosen automatically as follows. |
| 183 |
If the range type name contains the substring <literal>range</literal>, then |
| 184 |
the multirange type name is formed by replacement of the <literal>range</literal> |
| 185 |
substring with <literal>multirange</literal> in the range |
| 186 |
type name. Otherwise, the multirange type name is formed by appending a |
| 187 |
<literal>_multirange</literal> suffix to the range type name. |
| 188 |
</para> |
| 189 |
|
| 190 |
<para> |
| 191 |
To be able to create a range type, you must have <literal>USAGE</literal> |
| 192 |
privilege on the subtype. |
| 193 |
</para> |
| 194 |
</refsect2> |
| 195 |
|
| 196 |
<refsect2> |
| 197 |
<title>Base Types</title> |
| 198 |
|
| 199 |
<para> |
| 200 |
The fourth form of <command>CREATE TYPE</command> creates a new base type |
| 201 |
(scalar type). To create a new base type, you must be a superuser. |
| 202 |
(This restriction is made because an erroneous type definition could |
| 203 |
confuse or even crash the server.) |
| 204 |
</para> |
| 205 |
|
| 206 |
<para> |
| 207 |
The parameters can appear in any order, not only that |
| 208 |
illustrated above, and most are optional. You must register |
| 209 |
two or more functions (using <command>CREATE FUNCTION</command>) before |
| 210 |
defining the type. The support functions |
| 211 |
<replaceable class="parameter">input_function</replaceable> and |
| 212 |
<replaceable class="parameter">output_function</replaceable> |
| 213 |
are required, while the functions |
| 214 |
<replaceable class="parameter">receive_function</replaceable>, |
| 215 |
<replaceable class="parameter">send_function</replaceable>, |
| 216 |
<replaceable class="parameter">type_modifier_input_function</replaceable>, |
| 217 |
<replaceable class="parameter">type_modifier_output_function</replaceable>, |
| 218 |
<replaceable class="parameter">analyze_function</replaceable>, and |
| 219 |
<replaceable class="parameter">subscript_function</replaceable> |
| 220 |
are optional. Generally these functions have to be coded in C |
| 221 |
or another low-level language. |
| 222 |
</para> |
| 223 |
|
| 224 |
<para> |
| 225 |
The <replaceable class="parameter">input_function</replaceable> |
| 226 |
converts the type's external textual representation to the internal |
| 227 |
representation used by the operators and functions defined for the type. |
| 228 |
<replaceable class="parameter">output_function</replaceable> |
| 229 |
performs the reverse transformation. The input function can be |
| 230 |
declared as taking one argument of type <type>cstring</type>, |
| 231 |
or as taking three arguments of types |
| 232 |
<type>cstring</type>, <type>oid</type>, <type>integer</type>. |
| 233 |
The first argument is the input text as a C string, the second |
| 234 |
argument is the type's own OID (except for array types, which instead |
| 235 |
receive their element type's OID), |
| 236 |
and the third is the <literal>typmod</literal> of the destination column, if known |
| 237 |
(-1 will be passed if not). |
| 238 |
The input function must return a value of the data type itself. |
| 239 |
Usually, an input function should be declared STRICT; if it is not, |
| 240 |
it will be called with a NULL first parameter when reading a NULL |
| 241 |
input value. The function must still return NULL in this case, unless |
| 242 |
it raises an error. |
| 243 |
(This case is mainly meant to support domain input functions, which |
| 244 |
might need to reject NULL inputs.) |
| 245 |
The output function must be |
| 246 |
declared as taking one argument of the new data type. |
| 247 |
The output function must return type <type>cstring</type>. |
| 248 |
Output functions are not invoked for NULL values. |
| 249 |
</para> |
| 250 |
|
| 251 |
<para> |
| 252 |
The optional <replaceable class="parameter">receive_function</replaceable> |
| 253 |
converts the type's external binary representation to the internal |
| 254 |
representation. If this function is not supplied, the type cannot |
| 255 |
participate in binary input. The binary representation should be |
| 256 |
chosen to be cheap to convert to internal form, while being reasonably |
| 257 |
portable. (For example, the standard integer data types use network |
| 258 |
byte order as the external binary representation, while the internal |
| 259 |
representation is in the machine's native byte order.) The receive |
| 260 |
function should perform adequate checking to ensure that the value is |
| 261 |
valid. |
| 262 |
The receive function can be declared as taking one argument of type |
| 263 |
<type>internal</type>, or as taking three arguments of types |
| 264 |
<type>internal</type>, <type>oid</type>, <type>integer</type>. |
| 265 |
The first argument is a pointer to a <type>StringInfo</type> buffer |
| 266 |
holding the received byte string; the optional arguments are the |
| 267 |
same as for the text input function. |
| 268 |
The receive function must return a value of the data type itself. |
| 269 |
Usually, a receive function should be declared STRICT; if it is not, |
| 270 |
it will be called with a NULL first parameter when reading a NULL |
| 271 |
input value. The function must still return NULL in this case, unless |
| 272 |
it raises an error. |
| 273 |
(This case is mainly meant to support domain receive functions, which |
| 274 |
might need to reject NULL inputs.) |
| 275 |
Similarly, the optional |
| 276 |
<replaceable class="parameter">send_function</replaceable> converts |
| 277 |
from the internal representation to the external binary representation. |
| 278 |
If this function is not supplied, the type cannot participate in binary |
| 279 |
output. The send function must be |
| 280 |
declared as taking one argument of the new data type. |
| 281 |
The send function must return type <type>bytea</type>. |
| 282 |
Send functions are not invoked for NULL values. |
| 283 |
</para> |
| 284 |
|
| 285 |
<para> |
| 286 |
You should at this point be wondering how the input and output functions |
| 287 |
can be declared to have results or arguments of the new type, when they |
| 288 |
have to be created before the new type can be created. The answer is that |
| 289 |
the type should first be defined as a <firstterm>shell type</firstterm>, which is a |
| 290 |
placeholder type that has no properties except a name and an owner. This |
| 291 |
is done by issuing the command <literal>CREATE TYPE |
| 292 |
<replaceable>name</replaceable></literal>, with no additional parameters. Then the |
| 293 |
C I/O functions can be defined referencing the shell type. Finally, |
| 294 |
<command>CREATE TYPE</command> with a full definition replaces the shell entry |
| 295 |
with a complete, valid type definition, after which the new type can be |
| 296 |
used normally. |
| 297 |
</para> |
| 298 |
|
| 299 |
<para> |
| 300 |
The optional |
| 301 |
<replaceable class="parameter">type_modifier_input_function</replaceable> |
| 302 |
and <replaceable class="parameter">type_modifier_output_function</replaceable> |
| 303 |
are needed if the type supports modifiers, that is optional constraints |
| 304 |
attached to a type declaration, such as <literal>char(5)</literal> or |
| 305 |
<literal>numeric(30,2)</literal>. <productname>PostgreSQL</productname> allows |
| 306 |
user-defined types to take one or more simple constants or identifiers as |
| 307 |
modifiers. However, this information must be capable of being packed into a |
| 308 |
single non-negative integer value for storage in the system catalogs. The |
| 309 |
<replaceable class="parameter">type_modifier_input_function</replaceable> |
| 310 |
is passed the declared modifier(s) in the form of a <type>cstring</type> |
| 311 |
array. It must check the values for validity (throwing an error if they |
| 312 |
are wrong), and if they are correct, return a single non-negative |
| 313 |
<type>integer</type> value that will be stored as the column <quote>typmod</quote>. |
| 314 |
Type modifiers will be rejected if the type does not have a |
| 315 |
<replaceable class="parameter">type_modifier_input_function</replaceable>. |
| 316 |
The <replaceable class="parameter">type_modifier_output_function</replaceable> |
| 317 |
converts the internal integer typmod value back to the correct form for |
| 318 |
user display. It must return a <type>cstring</type> value that is the exact |
| 319 |
string to append to the type name; for example <type>numeric</type>'s |
| 320 |
function might return <literal>(30,2)</literal>. |
| 321 |
It is allowed to omit the |
| 322 |
<replaceable class="parameter">type_modifier_output_function</replaceable>, |
| 323 |
in which case the default display format is just the stored typmod integer |
| 324 |
value enclosed in parentheses. |
| 325 |
</para> |
| 326 |
|
| 327 |
<para> |
| 328 |
The optional <replaceable class="parameter">analyze_function</replaceable> |
| 329 |
performs type-specific statistics collection for columns of the data type. |
| 330 |
By default, <command>ANALYZE</command> will attempt to gather statistics using |
| 331 |
the type's <quote>equals</quote> and <quote>less-than</quote> operators, if there |
| 332 |
is a default b-tree operator class for the type. For non-scalar types |
| 333 |
this behavior is likely to be unsuitable, so it can be overridden by |
| 334 |
specifying a custom analysis function. The analysis function must be |
| 335 |
declared to take a single argument of type <type>internal</type>, and return |
| 336 |
a <type>boolean</type> result. The detailed API for analysis functions appears |
| 337 |
in <filename>src/include/commands/vacuum.h</filename>. |
| 338 |
</para> |
| 339 |
|
| 340 |
<para> |
| 341 |
The optional <replaceable class="parameter">subscript_function</replaceable> |
| 342 |
allows the data type to be subscripted in SQL commands. Specifying this |
| 343 |
function does not cause the type to be considered a <quote>true</quote> |
| 344 |
array type; for example, it will not be a candidate for the result type |
| 345 |
of <literal>ARRAY[]</literal> constructs. But if subscripting a value |
| 346 |
of the type is a natural notation for extracting data from it, then |
| 347 |
a <replaceable class="parameter">subscript_function</replaceable> can |
| 348 |
be written to define what that means. The subscript function must be |
| 349 |
declared to take a single argument of type <type>internal</type>, and |
| 350 |
return an <type>internal</type> result, which is a pointer to a struct |
| 351 |
of methods (functions) that implement subscripting. |
| 352 |
The detailed API for subscript functions appears |
| 353 |
in <filename>src/include/nodes/subscripting.h</filename>. |
| 354 |
It may also be useful to read the array implementation |
| 355 |
in <filename>src/backend/utils/adt/arraysubs.c</filename>, |
| 356 |
or the simpler code |
| 357 |
in <filename>contrib/hstore/hstore_subs.c</filename>. |
| 358 |
Additional information appears in |
| 359 |
<xref linkend="sql-createtype-array"/> below. |
| 360 |
</para> |
| 361 |
|
| 362 |
<para> |
| 363 |
While the details of the new type's internal representation are only |
| 364 |
known to the I/O functions and other functions you create to work with |
| 365 |
the type, there are several properties of the internal representation |
| 366 |
that must be declared to <productname>PostgreSQL</productname>. |
| 367 |
Foremost of these is |
| 368 |
<replaceable class="parameter">internallength</replaceable>. |
| 369 |
Base data types can be fixed-length, in which case |
| 370 |
<replaceable class="parameter">internallength</replaceable> is a |
| 371 |
positive integer, or variable-length, indicated by setting |
| 372 |
<replaceable class="parameter">internallength</replaceable> |
| 373 |
to <literal>VARIABLE</literal>. (Internally, this is represented |
| 374 |
by setting <literal>typlen</literal> to -1.) The internal representation of all |
| 375 |
variable-length types must start with a 4-byte integer giving the total |
| 376 |
length of this value of the type. (Note that the length field is often |
| 377 |
encoded, as described in <xref linkend="storage-toast"/>; it's unwise |
| 378 |
to access it directly.) |
| 379 |
</para> |
| 380 |
|
| 381 |
<para> |
| 382 |
The optional flag <literal>PASSEDBYVALUE</literal> indicates that |
| 383 |
values of this data type are passed by value, rather than by |
| 384 |
reference. Types passed by value must be fixed-length, and their internal |
| 385 |
representation cannot be larger than the size of the <type>Datum</type> type |
| 386 |
(4 bytes on some machines, 8 bytes on others). |
| 387 |
</para> |
| 388 |
|
| 389 |
<para> |
| 390 |
The <replaceable class="parameter">alignment</replaceable> parameter |
| 391 |
specifies the storage alignment required for the data type. The |
| 392 |
allowed values equate to alignment on 1, 2, 4, or 8 byte boundaries. |
| 393 |
Note that variable-length types must have an alignment of at least |
| 394 |
4, since they necessarily contain an <type>int4</type> as their first component. |
| 395 |
</para> |
| 396 |
|
| 397 |
<para> |
| 398 |
The <replaceable class="parameter">storage</replaceable> parameter |
| 399 |
allows selection of storage strategies for variable-length data |
| 400 |
types. (Only <literal>plain</literal> is allowed for fixed-length |
| 401 |
types.) <literal>plain</literal> specifies that data of the type |
| 402 |
will always be stored in-line and not compressed. |
| 403 |
<literal>extended</literal> specifies that the system will first |
| 404 |
try to compress a long data value, and will move the value out of |
| 405 |
the main table row if it's still too long. |
| 406 |
<literal>external</literal> allows the value to be moved out of the |
| 407 |
main table, but the system will not try to compress it. |
| 408 |
<literal>main</literal> allows compression, but discourages moving |
| 409 |
the value out of the main table. (Data items with this storage |
| 410 |
strategy might still be moved out of the main table if there is no |
| 411 |
other way to make a row fit, but they will be kept in the main |
| 412 |
table preferentially over <literal>extended</literal> and |
| 413 |
<literal>external</literal> items.) |
| 414 |
</para> |
| 415 |
|
| 416 |
<para> |
| 417 |
All <replaceable class="parameter">storage</replaceable> values other |
| 418 |
than <literal>plain</literal> imply that the functions of the data type |
| 419 |
can handle values that have been <firstterm>toasted</firstterm>, as described |
| 420 |
in <xref linkend="storage-toast"/> and <xref linkend="xtypes-toast"/>. |
| 421 |
The specific other value given merely determines the default TOAST |
| 422 |
storage strategy for columns of a toastable data type; users can pick |
| 423 |
other strategies for individual columns using <literal>ALTER TABLE |
| 424 |
SET STORAGE</literal>. |
| 425 |
</para> |
| 426 |
|
| 427 |
<para> |
| 428 |
The <replaceable class="parameter">like_type</replaceable> parameter |
| 429 |
provides an alternative method for specifying the basic representation |
| 430 |
properties of a data type: copy them from some existing type. The values of |
| 431 |
<replaceable class="parameter">internallength</replaceable>, |
| 432 |
<replaceable class="parameter">passedbyvalue</replaceable>, |
| 433 |
<replaceable class="parameter">alignment</replaceable>, and |
| 434 |
<replaceable class="parameter">storage</replaceable> are copied from the |
| 435 |
named type. (It is possible, though usually undesirable, to override |
| 436 |
some of these values by specifying them along with the <literal>LIKE</literal> |
| 437 |
clause.) Specifying representation this way is especially useful when |
| 438 |
the low-level implementation of the new type <quote>piggybacks</quote> on an |
| 439 |
existing type in some fashion. |
| 440 |
</para> |
| 441 |
|
| 442 |
<para> |
| 443 |
The <replaceable class="parameter">category</replaceable> and |
| 444 |
<replaceable class="parameter">preferred</replaceable> parameters can be |
| 445 |
used to help control which implicit cast will be applied in ambiguous |
| 446 |
situations. Each data type belongs to a category named by a single ASCII |
| 447 |
character, and each type is either <quote>preferred</quote> or not within its |
| 448 |
category. The parser will prefer casting to preferred types (but only from |
| 449 |
other types within the same category) when this rule is helpful in |
| 450 |
resolving overloaded functions or operators. For more details see <xref |
| 451 |
linkend="typeconv"/>. For types that have no implicit casts to or from any |
| 452 |
other types, it is sufficient to leave these settings at the defaults. |
| 453 |
However, for a group of related types that have implicit casts, it is often |
| 454 |
helpful to mark them all as belonging to a category and select one or two |
| 455 |
of the <quote>most general</quote> types as being preferred within the category. |
| 456 |
The <replaceable class="parameter">category</replaceable> parameter is |
| 457 |
especially useful when adding a user-defined type to an existing built-in |
| 458 |
category, such as the numeric or string types. However, it is also |
| 459 |
possible to create new entirely-user-defined type categories. Select any |
| 460 |
ASCII character other than an upper-case letter to name such a category. |
| 461 |
</para> |
| 462 |
|
| 463 |
<para> |
| 464 |
A default value can be specified, in case a user wants columns of the |
| 465 |
data type to default to something other than the null value. |
| 466 |
Specify the default with the <literal>DEFAULT</literal> key word. |
| 467 |
(Such a default can be overridden by an explicit <literal>DEFAULT</literal> |
| 468 |
clause attached to a particular column.) |
| 469 |
</para> |
| 470 |
|
| 471 |
<para> |
| 472 |
To indicate that a type is a fixed-length array type, |
| 473 |
specify the type of the array |
| 474 |
elements using the <literal>ELEMENT</literal> key word. For example, to |
| 475 |
define an array of 4-byte integers (<type>int4</type>), specify |
| 476 |
<literal>ELEMENT = int4</literal>. For more details, |
| 477 |
see <xref linkend="sql-createtype-array"/> below. |
| 478 |
</para> |
| 479 |
|
| 480 |
<para> |
| 481 |
To indicate the delimiter to be used between values in the external |
| 482 |
representation of arrays of this type, <replaceable |
| 483 |
class="parameter">delimiter</replaceable> can be |
| 484 |
set to a specific character. The default delimiter is the comma |
| 485 |
(<literal>,</literal>). Note that the delimiter is associated |
| 486 |
with the array element type, not the array type itself. |
| 487 |
</para> |
| 488 |
|
| 489 |
<para> |
| 490 |
If the optional Boolean |
| 491 |
parameter <replaceable class="parameter">collatable</replaceable> |
| 492 |
is true, column definitions and expressions of the type may carry |
| 493 |
collation information through use of |
| 494 |
the <literal>COLLATE</literal> clause. It is up to the |
| 495 |
implementations of the functions operating on the type to actually |
| 496 |
make use of the collation information; this does not happen |
| 497 |
automatically merely by marking the type collatable. |
| 498 |
</para> |
| 499 |
</refsect2> |
| 500 |
|
| 501 |
<refsect2 id="sql-createtype-array" xreflabel="Array Types"> |
| 502 |
<title>Array Types</title> |
| 503 |
|
| 504 |
<para> |
| 505 |
Whenever a user-defined type is created, |
| 506 |
<productname>PostgreSQL</productname> automatically creates an |
| 507 |
associated array type, whose name consists of the element type's |
| 508 |
name prepended with an underscore, and truncated if necessary to keep |
| 509 |
it less than <symbol>NAMEDATALEN</symbol> bytes long. (If the name |
| 510 |
so generated collides with an existing type name, the process is |
| 511 |
repeated until a non-colliding name is found.) |
| 512 |
This implicitly-created array type is variable length and uses the |
| 513 |
built-in input and output functions <literal>array_in</literal> and |
| 514 |
<literal>array_out</literal>. Furthermore, this type is what the system |
| 515 |
uses for constructs such as <literal>ARRAY[]</literal> over the |
| 516 |
user-defined type. The array type tracks any changes in its |
| 517 |
element type's owner or schema, and is dropped if the element type is. |
| 518 |
</para> |
| 519 |
|
| 520 |
<para> |
| 521 |
You might reasonably ask why there is an <option>ELEMENT</option> |
| 522 |
option, if the system makes the correct array type automatically. |
| 523 |
The main case where it's useful to use <option>ELEMENT</option> is when you are |
| 524 |
making a fixed-length type that happens to be internally an array of a number of |
| 525 |
identical things, and you want to allow these things to be accessed |
| 526 |
directly by subscripting, in addition to whatever operations you plan |
| 527 |
to provide for the type as a whole. For example, type <type>point</type> |
| 528 |
is represented as just two floating-point numbers, which can be accessed |
| 529 |
using <literal>point[0]</literal> and <literal>point[1]</literal>. |
| 530 |
Note that |
| 531 |
this facility only works for fixed-length types whose internal form |
| 532 |
is exactly a sequence of identical fixed-length fields. |
| 533 |
For historical reasons (i.e., this is clearly wrong but it's far too |
| 534 |
late to change it), subscripting of fixed-length array types starts from |
| 535 |
zero, rather than from one as for variable-length arrays. |
| 536 |
</para> |
| 537 |
|
| 538 |
<para> |
| 539 |
Specifying the <option>SUBSCRIPT</option> option allows a data type to |
| 540 |
be subscripted, even though the system does not otherwise regard it as |
| 541 |
an array type. The behavior just described for fixed-length arrays is |
| 542 |
actually implemented by the <option>SUBSCRIPT</option> handler |
| 543 |
function <function>raw_array_subscript_handler</function>, which is |
| 544 |
used automatically if you specify <option>ELEMENT</option> for a |
| 545 |
fixed-length type without also writing <option>SUBSCRIPT</option>. |
| 546 |
</para> |
| 547 |
|
| 548 |
<para> |
| 549 |
When specifying a custom <option>SUBSCRIPT</option> function, it is |
| 550 |
not necessary to specify <option>ELEMENT</option> unless |
| 551 |
the <option>SUBSCRIPT</option> handler function needs to |
| 552 |
consult <structfield>typelem</structfield> to find out what to return. |
| 553 |
Be aware that specifying <option>ELEMENT</option> causes the system to |
| 554 |
assume that the new type contains, or is somehow physically dependent on, |
| 555 |
the element type; thus for example changing properties of the element |
| 556 |
type won't be allowed if there are any columns of the dependent type. |
| 557 |
</para> |
| 558 |
</refsect2> |
| 559 |
</refsect1> |
| 560 |
|
| 561 |
<refsect1> |
| 562 |
<title>Parameters</title> |
| 563 |
|
| 564 |
<variablelist> |
| 565 |
<varlistentry> |
| 566 |
<term><replaceable class="parameter">name</replaceable></term> |
| 567 |
<listitem> |
| 568 |
<para> |
| 569 |
The name (optionally schema-qualified) of a type to be created. |
| 570 |
</para> |
| 571 |
</listitem> |
| 572 |
</varlistentry> |
| 573 |
|
| 574 |
<varlistentry> |
| 575 |
<term><replaceable class="parameter">attribute_name</replaceable></term> |
| 576 |
<listitem> |
| 577 |
<para> |
| 578 |
The name of an attribute (column) for the composite type. |
| 579 |
</para> |
| 580 |
</listitem> |
| 581 |
</varlistentry> |
| 582 |
|
| 583 |
<varlistentry> |
| 584 |
<term><replaceable class="parameter">data_type</replaceable></term> |
| 585 |
<listitem> |
| 586 |
<para> |
| 587 |
The name of an existing data type to become a column of the |
| 588 |
composite type. |
| 589 |
</para> |
| 590 |
</listitem> |
| 591 |
</varlistentry> |
| 592 |
|
| 593 |
<varlistentry> |
| 594 |
<term><replaceable class="parameter">collation</replaceable></term> |
| 595 |
<listitem> |
| 596 |
<para> |
| 597 |
The name of an existing collation to be associated with a column of |
| 598 |
a composite type, or with a range type. |
| 599 |
</para> |
| 600 |
</listitem> |
| 601 |
</varlistentry> |
| 602 |
|
| 603 |
<varlistentry> |
| 604 |
<term><replaceable class="parameter">label</replaceable></term> |
| 605 |
<listitem> |
| 606 |
<para> |
| 607 |
A string literal representing the textual label associated with |
| 608 |
one value of an enum type. |
| 609 |
</para> |
| 610 |
</listitem> |
| 611 |
</varlistentry> |
| 612 |
|
| 613 |
<varlistentry> |
| 614 |
<term><replaceable class="parameter">subtype</replaceable></term> |
| 615 |
<listitem> |
| 616 |
<para> |
| 617 |
The name of the element type that the range type will represent ranges |
| 618 |
of. |
| 619 |
</para> |
| 620 |
</listitem> |
| 621 |
</varlistentry> |
| 622 |
|
| 623 |
<varlistentry> |
| 624 |
<term><replaceable class="parameter">subtype_operator_class</replaceable></term> |
| 625 |
<listitem> |
| 626 |
<para> |
| 627 |
The name of a b-tree operator class for the subtype. |
| 628 |
</para> |
| 629 |
</listitem> |
| 630 |
</varlistentry> |
| 631 |
|
| 632 |
<varlistentry> |
| 633 |
<term><replaceable class="parameter">canonical_function</replaceable></term> |
| 634 |
<listitem> |
| 635 |
<para> |
| 636 |
The name of the canonicalization function for the range type. |
| 637 |
</para> |
| 638 |
</listitem> |
| 639 |
</varlistentry> |
| 640 |
|
| 641 |
<varlistentry> |
| 642 |
<term><replaceable class="parameter">subtype_diff_function</replaceable></term> |
| 643 |
<listitem> |
| 644 |
<para> |
| 645 |
The name of a difference function for the subtype. |
| 646 |
</para> |
| 647 |
</listitem> |
| 648 |
</varlistentry> |
| 649 |
|
| 650 |
<varlistentry> |
| 651 |
<term><replaceable class="parameter">multirange_type_name</replaceable></term> |
| 652 |
<listitem> |
| 653 |
<para> |
| 654 |
The name of the corresponding multirange type. |
| 655 |
</para> |
| 656 |
</listitem> |
| 657 |
</varlistentry> |
| 658 |
|
| 659 |
<varlistentry> |
| 660 |
<term><replaceable class="parameter">input_function</replaceable></term> |
| 661 |
<listitem> |
| 662 |
<para> |
| 663 |
The name of a function that converts data from the type's |
| 664 |
external textual form to its internal form. |
| 665 |
</para> |
| 666 |
</listitem> |
| 667 |
</varlistentry> |
| 668 |
|
| 669 |
<varlistentry> |
| 670 |
<term><replaceable class="parameter">output_function</replaceable></term> |
| 671 |
<listitem> |
| 672 |
<para> |
| 673 |
The name of a function that converts data from the type's |
| 674 |
internal form to its external textual form. |
| 675 |
</para> |
| 676 |
</listitem> |
| 677 |
</varlistentry> |
| 678 |
|
| 679 |
<varlistentry> |
| 680 |
<term><replaceable class="parameter">receive_function</replaceable></term> |
| 681 |
<listitem> |
| 682 |
<para> |
| 683 |
The name of a function that converts data from the type's |
| 684 |
external binary form to its internal form. |
| 685 |
</para> |
| 686 |
</listitem> |
| 687 |
</varlistentry> |
| 688 |
|
| 689 |
<varlistentry> |
| 690 |
<term><replaceable class="parameter">send_function</replaceable></term> |
| 691 |
<listitem> |
| 692 |
<para> |
| 693 |
The name of a function that converts data from the type's |
| 694 |
internal form to its external binary form. |
| 695 |
</para> |
| 696 |
</listitem> |
| 697 |
</varlistentry> |
| 698 |
|
| 699 |
<varlistentry> |
| 700 |
<term><replaceable class="parameter">type_modifier_input_function</replaceable></term> |
| 701 |
<listitem> |
| 702 |
<para> |
| 703 |
The name of a function that converts an array of modifier(s) for the type |
| 704 |
into internal form. |
| 705 |
</para> |
| 706 |
</listitem> |
| 707 |
</varlistentry> |
| 708 |
|
| 709 |
<varlistentry> |
| 710 |
<term><replaceable class="parameter">type_modifier_output_function</replaceable></term> |
| 711 |
<listitem> |
| 712 |
<para> |
| 713 |
The name of a function that converts the internal form of the type's |
| 714 |
modifier(s) to external textual form. |
| 715 |
</para> |
| 716 |
</listitem> |
| 717 |
</varlistentry> |
| 718 |
|
| 719 |
<varlistentry> |
| 720 |
<term><replaceable class="parameter">analyze_function</replaceable></term> |
| 721 |
<listitem> |
| 722 |
<para> |
| 723 |
The name of a function that performs statistical analysis for the |
| 724 |
data type. |
| 725 |
</para> |
| 726 |
</listitem> |
| 727 |
</varlistentry> |
| 728 |
|
| 729 |
<varlistentry> |
| 730 |
<term><replaceable class="parameter">subscript_function</replaceable></term> |
| 731 |
<listitem> |
| 732 |
<para> |
| 733 |
The name of a function that defines what subscripting a value of the |
| 734 |
data type does. |
| 735 |
</para> |
| 736 |
</listitem> |
| 737 |
</varlistentry> |
| 738 |
|
| 739 |
<varlistentry> |
| 740 |
<term><replaceable class="parameter">internallength</replaceable></term> |
| 741 |
<listitem> |
| 742 |
<para> |
| 743 |
A numeric constant that specifies the length in bytes of the new |
| 744 |
type's internal representation. The default assumption is that |
| 745 |
it is variable-length. |
| 746 |
</para> |
| 747 |
</listitem> |
| 748 |
</varlistentry> |
| 749 |
|
| 750 |
<varlistentry> |
| 751 |
<term><replaceable class="parameter">alignment</replaceable></term> |
| 752 |
<listitem> |
| 753 |
<para> |
| 754 |
The storage alignment requirement of the data type. If specified, |
| 755 |
it must be <literal>char</literal>, <literal>int2</literal>, |
| 756 |
<literal>int4</literal>, or <literal>double</literal>; the |
| 757 |
default is <literal>int4</literal>. |
| 758 |
</para> |
| 759 |
</listitem> |
| 760 |
</varlistentry> |
| 761 |
|
| 762 |
<varlistentry> |
| 763 |
<term><replaceable class="parameter">storage</replaceable></term> |
| 764 |
<listitem> |
| 765 |
<para> |
| 766 |
The storage strategy for the data type. If specified, must be |
| 767 |
<literal>plain</literal>, <literal>external</literal>, |
| 768 |
<literal>extended</literal>, or <literal>main</literal>; the |
| 769 |
default is <literal>plain</literal>. |
| 770 |
</para> |
| 771 |
</listitem> |
| 772 |
</varlistentry> |
| 773 |
|
| 774 |
<varlistentry> |
| 775 |
<term><replaceable class="parameter">like_type</replaceable></term> |
| 776 |
<listitem> |
| 777 |
<para> |
| 778 |
The name of an existing data type that the new type will have the |
| 779 |
same representation as. The values of |
| 780 |
<replaceable class="parameter">internallength</replaceable>, |
| 781 |
<replaceable class="parameter">passedbyvalue</replaceable>, |
| 782 |
<replaceable class="parameter">alignment</replaceable>, and |
| 783 |
<replaceable class="parameter">storage</replaceable> |
| 784 |
are copied from that type, unless overridden by explicit |
| 785 |
specification elsewhere in this <command>CREATE TYPE</command> command. |
| 786 |
</para> |
| 787 |
</listitem> |
| 788 |
</varlistentry> |
| 789 |
|
| 790 |
<varlistentry> |
| 791 |
<term><replaceable class="parameter">category</replaceable></term> |
| 792 |
<listitem> |
| 793 |
<para> |
| 794 |
The category code (a single ASCII character) for this type. |
| 795 |
The default is <literal>'U'</literal> for <quote>user-defined type</quote>. |
| 796 |
Other standard category codes can be found in |
| 797 |
<xref linkend="catalog-typcategory-table"/>. You may also choose |
| 798 |
other ASCII characters in order to create custom categories. |
| 799 |
</para> |
| 800 |
</listitem> |
| 801 |
</varlistentry> |
| 802 |
|
| 803 |
<varlistentry> |
| 804 |
<term><replaceable class="parameter">preferred</replaceable></term> |
| 805 |
<listitem> |
| 806 |
<para> |
| 807 |
True if this type is a preferred type within its type category, |
| 808 |
else false. The default is false. Be very careful about creating |
| 809 |
a new preferred type within an existing type category, as this |
| 810 |
could cause surprising changes in behavior. |
| 811 |
</para> |
| 812 |
</listitem> |
| 813 |
</varlistentry> |
| 814 |
|
| 815 |
<varlistentry> |
| 816 |
<term><replaceable class="parameter">default</replaceable></term> |
| 817 |
<listitem> |
| 818 |
<para> |
| 819 |
The default value for the data type. If this is omitted, the |
| 820 |
default is null. |
| 821 |
</para> |
| 822 |
</listitem> |
| 823 |
</varlistentry> |
| 824 |
|
| 825 |
<varlistentry> |
| 826 |
<term><replaceable class="parameter">element</replaceable></term> |
| 827 |
<listitem> |
| 828 |
<para> |
| 829 |
The type being created is an array; this specifies the type of |
| 830 |
the array elements. |
| 831 |
</para> |
| 832 |
</listitem> |
| 833 |
</varlistentry> |
| 834 |
|
| 835 |
<varlistentry> |
| 836 |
<term><replaceable class="parameter">delimiter</replaceable></term> |
| 837 |
<listitem> |
| 838 |
<para> |
| 839 |
The delimiter character to be used between values in arrays made |
| 840 |
of this type. |
| 841 |
</para> |
| 842 |
</listitem> |
| 843 |
</varlistentry> |
| 844 |
|
| 845 |
<varlistentry> |
| 846 |
<term><replaceable class="parameter">collatable</replaceable></term> |
| 847 |
<listitem> |
| 848 |
<para> |
| 849 |
True if this type's operations can use collation information. |
| 850 |
The default is false. |
| 851 |
</para> |
| 852 |
</listitem> |
| 853 |
</varlistentry> |
| 854 |
</variablelist> |
| 855 |
</refsect1> |
| 856 |
|
| 857 |
<refsect1 id="sql-createtype-notes"> |
| 858 |
<title>Notes</title> |
| 859 |
|
| 860 |
<para> |
| 861 |
Because there are no restrictions on use of a data type once it's been |
| 862 |
created, creating a base type or range type is tantamount to granting |
| 863 |
public execute permission on the functions mentioned in the type definition. |
| 864 |
This is usually |
| 865 |
not an issue for the sorts of functions that are useful in a type |
| 866 |
definition. But you might want to think twice before designing a type |
| 867 |
in a way that would require <quote>secret</quote> information to be used |
| 868 |
while converting it to or from external form. |
| 869 |
</para> |
| 870 |
|
| 871 |
<para> |
| 872 |
Before <productname>PostgreSQL</productname> version 8.3, the name of |
| 873 |
a generated array type was always exactly the element type's name with one |
| 874 |
underscore character (<literal>_</literal>) prepended. (Type names were |
| 875 |
therefore restricted in length to one fewer character than other names.) |
| 876 |
While this is still usually the case, the array type name may vary from |
| 877 |
this in case of maximum-length names or collisions with user type names |
| 878 |
that begin with underscore. Writing code that depends on this convention |
| 879 |
is therefore deprecated. Instead, use |
| 880 |
<structname>pg_type</structname>.<structfield>typarray</structfield> to locate the array type |
| 881 |
associated with a given type. |
| 882 |
</para> |
| 883 |
|
| 884 |
<para> |
| 885 |
It may be advisable to avoid using type and table names that begin with |
| 886 |
underscore. While the server will change generated array type names to |
| 887 |
avoid collisions with user-given names, there is still risk of confusion, |
| 888 |
particularly with old client software that may assume that type names |
| 889 |
beginning with underscores always represent arrays. |
| 890 |
</para> |
| 891 |
|
| 892 |
<para> |
| 893 |
Before <productname>PostgreSQL</productname> version 8.2, the shell-type |
| 894 |
creation syntax |
| 895 |
<literal>CREATE TYPE <replaceable>name</replaceable></literal> did not exist. |
| 896 |
The way to create a new base type was to create its input function first. |
| 897 |
In this approach, <productname>PostgreSQL</productname> will first see |
| 898 |
the name of the new data type as the return type of the input function. |
| 899 |
The shell type is implicitly created in this situation, and then it |
| 900 |
can be referenced in the definitions of the remaining I/O functions. |
| 901 |
This approach still works, but is deprecated and might be disallowed in |
| 902 |
some future release. Also, to avoid accidentally cluttering |
| 903 |
the catalogs with shell types as a result of simple typos in function |
| 904 |
definitions, a shell type will only be made this way when the input |
| 905 |
function is written in C. |
| 906 |
</para> |
| 907 |
|
| 908 |
<para> |
| 909 |
In <productname>PostgreSQL</productname> version 16 and later, |
| 910 |
it is desirable for base types' input functions to |
| 911 |
return <quote>soft</quote> errors using the |
| 912 |
new <function>errsave()</function>/<function>ereturn()</function> |
| 913 |
mechanism, rather than throwing <function>ereport()</function> |
| 914 |
exceptions as in previous versions. |
| 915 |
See <filename>src/backend/utils/fmgr/README</filename> for more |
| 916 |
information. |
| 917 |
</para> |
| 918 |
|
| 919 |
</refsect1> |
| 920 |
|
| 921 |
<refsect1> |
| 922 |
<title>Examples</title> |
| 923 |
|
| 924 |
<para> |
| 925 |
This example creates a composite type and uses it in |
| 926 |
a function definition: |
| 927 |
<programlisting> |
| 928 |
CREATE TYPE compfoo AS (f1 int, f2 text); |
| 929 |
|
| 930 |
CREATE FUNCTION getfoo() RETURNS SETOF compfoo AS $$ |
| 931 |
SELECT fooid, fooname FROM foo |
| 932 |
$$ LANGUAGE SQL; |
| 933 |
</programlisting> |
| 934 |
</para> |
| 935 |
|
| 936 |
<para> |
| 937 |
This example creates an enumerated type and uses it in |
| 938 |
a table definition: |
| 939 |
<programlisting> |
| 940 |
CREATE TYPE bug_status AS ENUM ('new', 'open', 'closed'); |
| 941 |
|
| 942 |
CREATE TABLE bug ( |
| 943 |
id serial, |
| 944 |
description text, |
| 945 |
status bug_status |
| 946 |
); |
| 947 |
</programlisting> |
| 948 |
</para> |
| 949 |
|
| 950 |
<para> |
| 951 |
This example creates a range type: |
| 952 |
<programlisting> |
| 953 |
CREATE TYPE float8_range AS RANGE (subtype = float8, subtype_diff = float8mi); |
| 954 |
</programlisting> |
| 955 |
</para> |
| 956 |
|
| 957 |
<para> |
| 958 |
This example creates the base data type <type>box</type> and then uses the |
| 959 |
type in a table definition: |
| 960 |
<programlisting> |
| 961 |
CREATE TYPE box; |
| 962 |
|
| 963 |
CREATE FUNCTION my_box_in_function(cstring) RETURNS box AS ... ; |
| 964 |
CREATE FUNCTION my_box_out_function(box) RETURNS cstring AS ... ; |
| 965 |
|
| 966 |
CREATE TYPE box ( |
| 967 |
INTERNALLENGTH = 16, |
| 968 |
INPUT = my_box_in_function, |
| 969 |
OUTPUT = my_box_out_function |
| 970 |
); |
| 971 |
|
| 972 |
CREATE TABLE myboxes ( |
| 973 |
id integer, |
| 974 |
description box |
| 975 |
); |
| 976 |
</programlisting> |
| 977 |
</para> |
| 978 |
|
| 979 |
<para> |
| 980 |
If the internal structure of <type>box</type> were an array of four |
| 981 |
<type>float4</type> elements, we might instead use: |
| 982 |
<programlisting> |
| 983 |
CREATE TYPE box ( |
| 984 |
INTERNALLENGTH = 16, |
| 985 |
INPUT = my_box_in_function, |
| 986 |
OUTPUT = my_box_out_function, |
| 987 |
ELEMENT = float4 |
| 988 |
); |
| 989 |
</programlisting> |
| 990 |
which would allow a box value's component numbers to be accessed |
| 991 |
by subscripting. Otherwise the type behaves the same as before. |
| 992 |
</para> |
| 993 |
|
| 994 |
<para> |
| 995 |
This example creates a large object type and uses it in |
| 996 |
a table definition: |
| 997 |
<programlisting> |
| 998 |
CREATE TYPE bigobj ( |
| 999 |
INPUT = lo_filein, OUTPUT = lo_fileout, |
| 1000 |
INTERNALLENGTH = VARIABLE |
| 1001 |
); |
| 1002 |
CREATE TABLE big_objs ( |
| 1003 |
id integer, |
| 1004 |
obj bigobj |
| 1005 |
); |
| 1006 |
</programlisting> |
| 1007 |
</para> |
| 1008 |
|
| 1009 |
<para> |
| 1010 |
More examples, including suitable input and output functions, are |
| 1011 |
in <xref linkend="xtypes"/>. |
| 1012 |
</para> |
| 1013 |
</refsect1> |
| 1014 |
|
| 1015 |
<refsect1 id="sql-createtype-compatibility"> |
| 1016 |
<title>Compatibility</title> |
| 1017 |
|
| 1018 |
<para> |
| 1019 |
The first form of the <command>CREATE TYPE</command> command, which |
| 1020 |
creates a composite type, conforms to the <acronym>SQL</acronym> standard. |
| 1021 |
The other forms are <productname>PostgreSQL</productname> |
| 1022 |
extensions. The <command>CREATE TYPE</command> statement in |
| 1023 |
the <acronym>SQL</acronym> standard also defines other forms that are not |
| 1024 |
implemented in <productname>PostgreSQL</productname>. |
| 1025 |
</para> |
| 1026 |
|
| 1027 |
<para> |
| 1028 |
The ability to create a composite type with zero attributes is |
| 1029 |
a <productname>PostgreSQL</productname>-specific deviation from the |
| 1030 |
standard (analogous to the same case in <command>CREATE TABLE</command>). |
| 1031 |
</para> |
| 1032 |
</refsect1> |
| 1033 |
|
| 1034 |
<refsect1 id="sql-createtype-see-also"> |
| 1035 |
<title>See Also</title> |
| 1036 |
|
| 1037 |
<simplelist type="inline"> |
| 1038 |
<member><xref linkend="sql-altertype"/></member> |
| 1039 |
<member><xref linkend="sql-createdomain"/></member> |
| 1040 |
<member><xref linkend="sql-createfunction"/></member> |
| 1041 |
<member><xref linkend="sql-droptype"/></member> |
| 1042 |
</simplelist> |
| 1043 |
</refsect1> |
| 1044 |
|
| 1045 |
</refentry> |