diff options
Diffstat (limited to 'doc/src/sgml/html/sql-createtype.html')
| -rw-r--r-- | doc/src/sgml/html/sql-createtype.html | 655 |
1 files changed, 655 insertions, 0 deletions
diff --git a/doc/src/sgml/html/sql-createtype.html b/doc/src/sgml/html/sql-createtype.html new file mode 100644 index 0000000..207459e --- /dev/null +++ b/doc/src/sgml/html/sql-createtype.html @@ -0,0 +1,655 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>CREATE TYPE</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="sql-createtrigger.html" title="CREATE TRIGGER" /><link rel="next" href="sql-createuser.html" title="CREATE USER" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">CREATE TYPE</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-createtrigger.html" title="CREATE TRIGGER">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-createuser.html" title="CREATE USER">Next</a></td></tr></table><hr /></div><div class="refentry" id="SQL-CREATETYPE"><div class="titlepage"></div><a id="id-1.9.3.94.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">CREATE TYPE</span></h2><p>CREATE TYPE — define a new data type</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis"> +CREATE TYPE <em class="replaceable"><code>name</code></em> AS + ( [ <em class="replaceable"><code>attribute_name</code></em> <em class="replaceable"><code>data_type</code></em> [ COLLATE <em class="replaceable"><code>collation</code></em> ] [, ... ] ] ) + +CREATE TYPE <em class="replaceable"><code>name</code></em> AS ENUM + ( [ '<em class="replaceable"><code>label</code></em>' [, ... ] ] ) + +CREATE TYPE <em class="replaceable"><code>name</code></em> AS RANGE ( + SUBTYPE = <em class="replaceable"><code>subtype</code></em> + [ , SUBTYPE_OPCLASS = <em class="replaceable"><code>subtype_operator_class</code></em> ] + [ , COLLATION = <em class="replaceable"><code>collation</code></em> ] + [ , CANONICAL = <em class="replaceable"><code>canonical_function</code></em> ] + [ , SUBTYPE_DIFF = <em class="replaceable"><code>subtype_diff_function</code></em> ] + [ , MULTIRANGE_TYPE_NAME = <em class="replaceable"><code>multirange_type_name</code></em> ] +) + +CREATE TYPE <em class="replaceable"><code>name</code></em> ( + INPUT = <em class="replaceable"><code>input_function</code></em>, + OUTPUT = <em class="replaceable"><code>output_function</code></em> + [ , RECEIVE = <em class="replaceable"><code>receive_function</code></em> ] + [ , SEND = <em class="replaceable"><code>send_function</code></em> ] + [ , TYPMOD_IN = <em class="replaceable"><code>type_modifier_input_function</code></em> ] + [ , TYPMOD_OUT = <em class="replaceable"><code>type_modifier_output_function</code></em> ] + [ , ANALYZE = <em class="replaceable"><code>analyze_function</code></em> ] + [ , SUBSCRIPT = <em class="replaceable"><code>subscript_function</code></em> ] + [ , INTERNALLENGTH = { <em class="replaceable"><code>internallength</code></em> | VARIABLE } ] + [ , PASSEDBYVALUE ] + [ , ALIGNMENT = <em class="replaceable"><code>alignment</code></em> ] + [ , STORAGE = <em class="replaceable"><code>storage</code></em> ] + [ , LIKE = <em class="replaceable"><code>like_type</code></em> ] + [ , CATEGORY = <em class="replaceable"><code>category</code></em> ] + [ , PREFERRED = <em class="replaceable"><code>preferred</code></em> ] + [ , DEFAULT = <em class="replaceable"><code>default</code></em> ] + [ , ELEMENT = <em class="replaceable"><code>element</code></em> ] + [ , DELIMITER = <em class="replaceable"><code>delimiter</code></em> ] + [ , COLLATABLE = <em class="replaceable"><code>collatable</code></em> ] +) + +CREATE TYPE <em class="replaceable"><code>name</code></em> +</pre></div><div class="refsect1" id="id-1.9.3.94.5"><h2>Description</h2><p> + <code class="command">CREATE TYPE</code> registers a new data type for use in + the current database. The user who defines a type becomes its + owner. + </p><p> + If a schema name is given then the type is created in the specified + schema. Otherwise it is created in the current schema. The type + name must be distinct from the name of any existing type or domain + in the same schema. (Because tables have associated data types, + the type name must also be distinct from the name of any existing + table in the same schema.) + </p><p> + There are five forms of <code class="command">CREATE TYPE</code>, as shown in the + syntax synopsis above. They respectively create a <em class="firstterm">composite + type</em>, an <em class="firstterm">enum type</em>, a <em class="firstterm">range type</em>, a + <em class="firstterm">base type</em>, or a <em class="firstterm">shell type</em>. The first four + of these are discussed in turn below. A shell type is simply a placeholder + for a type to be defined later; it is created by issuing <code class="command">CREATE + TYPE</code> with no parameters except for the type name. Shell types + are needed as forward references when creating range types and base types, + as discussed in those sections. + </p><div class="refsect2" id="id-1.9.3.94.5.5"><h3>Composite Types</h3><p> + The first form of <code class="command">CREATE TYPE</code> + creates a composite type. + The composite type is specified by a list of attribute names and data types. + An attribute's collation can be specified too, if its data type is + collatable. A composite type is essentially the same as the row type + of a table, but using <code class="command">CREATE TYPE</code> avoids the need to + create an actual table when all that is wanted is to define a type. + A stand-alone composite type is useful, for example, as the argument or + return type of a function. + </p><p> + To be able to create a composite type, you must + have <code class="literal">USAGE</code> privilege on all attribute types. + </p></div><div class="refsect2" id="SQL-CREATETYPE-ENUM"><h3>Enumerated Types</h3><p> + The second form of <code class="command">CREATE TYPE</code> creates an enumerated + (enum) type, as described in <a class="xref" href="datatype-enum.html" title="8.7. Enumerated Types">Section 8.7</a>. + Enum types take a list of quoted labels, each of which + must be less than <code class="symbol">NAMEDATALEN</code> bytes long (64 bytes in a + standard <span class="productname">PostgreSQL</span> build). (It is possible to + create an enumerated type with zero labels, but such a type cannot be used + to hold values before at least one label is added using <a class="link" href="sql-altertype.html" title="ALTER TYPE"><code class="command">ALTER TYPE</code></a>.) + </p></div><div class="refsect2" id="SQL-CREATETYPE-RANGE"><h3>Range Types</h3><p> + The third form of <code class="command">CREATE TYPE</code> creates a new + range type, as described in <a class="xref" href="rangetypes.html" title="8.17. Range Types">Section 8.17</a>. + </p><p> + The range type's <em class="replaceable"><code>subtype</code></em> can + be any type with an associated b-tree operator class (to determine the + ordering of values for the range type). Normally the subtype's default + b-tree operator class is used to determine ordering; to use a non-default + operator class, specify its name with <em class="replaceable"><code>subtype_opclass</code></em>. If the subtype is + collatable, and you want to use a non-default collation in the range's + ordering, specify the desired collation with the <em class="replaceable"><code>collation</code></em> option. + </p><p> + The optional <em class="replaceable"><code>canonical</code></em> + function must take one argument of the range type being defined, and + return a value of the same type. This is used to convert range values + to a canonical form, when applicable. See <a class="xref" href="rangetypes.html#RANGETYPES-DEFINING" title="8.17.8. Defining New Range Types">Section 8.17.8</a> for more information. Creating a + <em class="replaceable"><code>canonical</code></em> function + is a bit tricky, since it must be defined before the range type can be + declared. To do this, you must first create a shell type, which is a + placeholder type that has no properties except a name and an + owner. This is done by issuing the command <code class="literal">CREATE TYPE + <em class="replaceable"><code>name</code></em></code>, with no additional parameters. Then + the function can be declared using the shell type as argument and result, + and finally the range type can be declared using the same name. This + automatically replaces the shell type entry with a valid range type. + </p><p> + The optional <em class="replaceable"><code>subtype_diff</code></em> + function must take two values of the + <em class="replaceable"><code>subtype</code></em> type as argument, + and return a <code class="type">double precision</code> value representing the + difference between the two given values. While this is optional, + providing it allows much greater efficiency of GiST indexes on columns of + the range type. See <a class="xref" href="rangetypes.html#RANGETYPES-DEFINING" title="8.17.8. Defining New Range Types">Section 8.17.8</a> for more + information. + </p><p> + The optional <em class="replaceable"><code>multirange_type_name</code></em> + parameter specifies the name of the corresponding multirange type. If not + specified, this name is chosen automatically as follows. + If the range type name contains the substring <code class="literal">range</code>, then + the multirange type name is formed by replacement of the <code class="literal">range</code> + substring with <code class="literal">multirange</code> in the range + type name. Otherwise, the multirange type name is formed by appending a + <code class="literal">_multirange</code> suffix to the range type name. + </p></div><div class="refsect2" id="id-1.9.3.94.5.8"><h3>Base Types</h3><p> + The fourth form of <code class="command">CREATE TYPE</code> creates a new base type + (scalar type). To create a new base type, you must be a superuser. + (This restriction is made because an erroneous type definition could + confuse or even crash the server.) + </p><p> + The parameters can appear in any order, not only that + illustrated above, and most are optional. You must register + two or more functions (using <code class="command">CREATE FUNCTION</code>) before + defining the type. The support functions + <em class="replaceable"><code>input_function</code></em> and + <em class="replaceable"><code>output_function</code></em> + are required, while the functions + <em class="replaceable"><code>receive_function</code></em>, + <em class="replaceable"><code>send_function</code></em>, + <em class="replaceable"><code>type_modifier_input_function</code></em>, + <em class="replaceable"><code>type_modifier_output_function</code></em>, + <em class="replaceable"><code>analyze_function</code></em>, and + <em class="replaceable"><code>subscript_function</code></em> + are optional. Generally these functions have to be coded in C + or another low-level language. + </p><p> + The <em class="replaceable"><code>input_function</code></em> + converts the type's external textual representation to the internal + representation used by the operators and functions defined for the type. + <em class="replaceable"><code>output_function</code></em> + performs the reverse transformation. The input function can be + declared as taking one argument of type <code class="type">cstring</code>, + or as taking three arguments of types + <code class="type">cstring</code>, <code class="type">oid</code>, <code class="type">integer</code>. + The first argument is the input text as a C string, the second + argument is the type's own OID (except for array types, which instead + receive their element type's OID), + and the third is the <code class="literal">typmod</code> of the destination column, if known + (-1 will be passed if not). + The input function must return a value of the data type itself. + Usually, an input function should be declared STRICT; if it is not, + it will be called with a NULL first parameter when reading a NULL + input value. The function must still return NULL in this case, unless + it raises an error. + (This case is mainly meant to support domain input functions, which + might need to reject NULL inputs.) + The output function must be + declared as taking one argument of the new data type. + The output function must return type <code class="type">cstring</code>. + Output functions are not invoked for NULL values. + </p><p> + The optional <em class="replaceable"><code>receive_function</code></em> + converts the type's external binary representation to the internal + representation. If this function is not supplied, the type cannot + participate in binary input. The binary representation should be + chosen to be cheap to convert to internal form, while being reasonably + portable. (For example, the standard integer data types use network + byte order as the external binary representation, while the internal + representation is in the machine's native byte order.) The receive + function should perform adequate checking to ensure that the value is + valid. + The receive function can be declared as taking one argument of type + <code class="type">internal</code>, or as taking three arguments of types + <code class="type">internal</code>, <code class="type">oid</code>, <code class="type">integer</code>. + The first argument is a pointer to a <code class="type">StringInfo</code> buffer + holding the received byte string; the optional arguments are the + same as for the text input function. + The receive function must return a value of the data type itself. + Usually, a receive function should be declared STRICT; if it is not, + it will be called with a NULL first parameter when reading a NULL + input value. The function must still return NULL in this case, unless + it raises an error. + (This case is mainly meant to support domain receive functions, which + might need to reject NULL inputs.) + Similarly, the optional + <em class="replaceable"><code>send_function</code></em> converts + from the internal representation to the external binary representation. + If this function is not supplied, the type cannot participate in binary + output. The send function must be + declared as taking one argument of the new data type. + The send function must return type <code class="type">bytea</code>. + Send functions are not invoked for NULL values. + </p><p> + You should at this point be wondering how the input and output functions + can be declared to have results or arguments of the new type, when they + have to be created before the new type can be created. The answer is that + the type should first be defined as a <em class="firstterm">shell type</em>, which is a + placeholder type that has no properties except a name and an owner. This + is done by issuing the command <code class="literal">CREATE TYPE + <em class="replaceable"><code>name</code></em></code>, with no additional parameters. Then the + C I/O functions can be defined referencing the shell type. Finally, + <code class="command">CREATE TYPE</code> with a full definition replaces the shell entry + with a complete, valid type definition, after which the new type can be + used normally. + </p><p> + The optional + <em class="replaceable"><code>type_modifier_input_function</code></em> + and <em class="replaceable"><code>type_modifier_output_function</code></em> + are needed if the type supports modifiers, that is optional constraints + attached to a type declaration, such as <code class="literal">char(5)</code> or + <code class="literal">numeric(30,2)</code>. <span class="productname">PostgreSQL</span> allows + user-defined types to take one or more simple constants or identifiers as + modifiers. However, this information must be capable of being packed into a + single non-negative integer value for storage in the system catalogs. The + <em class="replaceable"><code>type_modifier_input_function</code></em> + is passed the declared modifier(s) in the form of a <code class="type">cstring</code> + array. It must check the values for validity (throwing an error if they + are wrong), and if they are correct, return a single non-negative + <code class="type">integer</code> value that will be stored as the column <span class="quote">“<span class="quote">typmod</span>”</span>. + Type modifiers will be rejected if the type does not have a + <em class="replaceable"><code>type_modifier_input_function</code></em>. + The <em class="replaceable"><code>type_modifier_output_function</code></em> + converts the internal integer typmod value back to the correct form for + user display. It must return a <code class="type">cstring</code> value that is the exact + string to append to the type name; for example <code class="type">numeric</code>'s + function might return <code class="literal">(30,2)</code>. + It is allowed to omit the + <em class="replaceable"><code>type_modifier_output_function</code></em>, + in which case the default display format is just the stored typmod integer + value enclosed in parentheses. + </p><p> + The optional <em class="replaceable"><code>analyze_function</code></em> + performs type-specific statistics collection for columns of the data type. + By default, <code class="command">ANALYZE</code> will attempt to gather statistics using + the type's <span class="quote">“<span class="quote">equals</span>”</span> and <span class="quote">“<span class="quote">less-than</span>”</span> operators, if there + is a default b-tree operator class for the type. For non-scalar types + this behavior is likely to be unsuitable, so it can be overridden by + specifying a custom analysis function. The analysis function must be + declared to take a single argument of type <code class="type">internal</code>, and return + a <code class="type">boolean</code> result. The detailed API for analysis functions appears + in <code class="filename">src/include/commands/vacuum.h</code>. + </p><p> + The optional <em class="replaceable"><code>subscript_function</code></em> + allows the data type to be subscripted in SQL commands. Specifying this + function does not cause the type to be considered a <span class="quote">“<span class="quote">true</span>”</span> + array type; for example, it will not be a candidate for the result type + of <code class="literal">ARRAY[]</code> constructs. But if subscripting a value + of the type is a natural notation for extracting data from it, then + a <em class="replaceable"><code>subscript_function</code></em> can + be written to define what that means. The subscript function must be + declared to take a single argument of type <code class="type">internal</code>, and + return an <code class="type">internal</code> result, which is a pointer to a struct + of methods (functions) that implement subscripting. + The detailed API for subscript functions appears + in <code class="filename">src/include/nodes/subscripting.h</code>. + It may also be useful to read the array implementation + in <code class="filename">src/backend/utils/adt/arraysubs.c</code>, + or the simpler code + in <code class="filename">contrib/hstore/hstore_subs.c</code>. + Additional information appears in + <a class="xref" href="sql-createtype.html#SQL-CREATETYPE-ARRAY" title="Array Types">Array Types</a> below. + </p><p> + While the details of the new type's internal representation are only + known to the I/O functions and other functions you create to work with + the type, there are several properties of the internal representation + that must be declared to <span class="productname">PostgreSQL</span>. + Foremost of these is + <em class="replaceable"><code>internallength</code></em>. + Base data types can be fixed-length, in which case + <em class="replaceable"><code>internallength</code></em> is a + positive integer, or variable-length, indicated by setting + <em class="replaceable"><code>internallength</code></em> + to <code class="literal">VARIABLE</code>. (Internally, this is represented + by setting <code class="literal">typlen</code> to -1.) The internal representation of all + variable-length types must start with a 4-byte integer giving the total + length of this value of the type. (Note that the length field is often + encoded, as described in <a class="xref" href="storage-toast.html" title="73.2. TOAST">Section 73.2</a>; it's unwise + to access it directly.) + </p><p> + The optional flag <code class="literal">PASSEDBYVALUE</code> indicates that + values of this data type are passed by value, rather than by + reference. Types passed by value must be fixed-length, and their internal + representation cannot be larger than the size of the <code class="type">Datum</code> type + (4 bytes on some machines, 8 bytes on others). + </p><p> + The <em class="replaceable"><code>alignment</code></em> parameter + specifies the storage alignment required for the data type. The + allowed values equate to alignment on 1, 2, 4, or 8 byte boundaries. + Note that variable-length types must have an alignment of at least + 4, since they necessarily contain an <code class="type">int4</code> as their first component. + </p><p> + The <em class="replaceable"><code>storage</code></em> parameter + allows selection of storage strategies for variable-length data + types. (Only <code class="literal">plain</code> is allowed for fixed-length + types.) <code class="literal">plain</code> specifies that data of the type + will always be stored in-line and not compressed. + <code class="literal">extended</code> specifies that the system will first + try to compress a long data value, and will move the value out of + the main table row if it's still too long. + <code class="literal">external</code> allows the value to be moved out of the + main table, but the system will not try to compress it. + <code class="literal">main</code> allows compression, but discourages moving + the value out of the main table. (Data items with this storage + strategy might still be moved out of the main table if there is no + other way to make a row fit, but they will be kept in the main + table preferentially over <code class="literal">extended</code> and + <code class="literal">external</code> items.) + </p><p> + All <em class="replaceable"><code>storage</code></em> values other + than <code class="literal">plain</code> imply that the functions of the data type + can handle values that have been <em class="firstterm">toasted</em>, as described + in <a class="xref" href="storage-toast.html" title="73.2. TOAST">Section 73.2</a> and <a class="xref" href="xtypes.html#XTYPES-TOAST" title="38.13.1. TOAST Considerations">Section 38.13.1</a>. + The specific other value given merely determines the default TOAST + storage strategy for columns of a toastable data type; users can pick + other strategies for individual columns using <code class="literal">ALTER TABLE + SET STORAGE</code>. + </p><p> + The <em class="replaceable"><code>like_type</code></em> parameter + provides an alternative method for specifying the basic representation + properties of a data type: copy them from some existing type. The values of + <em class="replaceable"><code>internallength</code></em>, + <em class="replaceable"><code>passedbyvalue</code></em>, + <em class="replaceable"><code>alignment</code></em>, and + <em class="replaceable"><code>storage</code></em> are copied from the + named type. (It is possible, though usually undesirable, to override + some of these values by specifying them along with the <code class="literal">LIKE</code> + clause.) Specifying representation this way is especially useful when + the low-level implementation of the new type <span class="quote">“<span class="quote">piggybacks</span>”</span> on an + existing type in some fashion. + </p><p> + The <em class="replaceable"><code>category</code></em> and + <em class="replaceable"><code>preferred</code></em> parameters can be + used to help control which implicit cast will be applied in ambiguous + situations. Each data type belongs to a category named by a single ASCII + character, and each type is either <span class="quote">“<span class="quote">preferred</span>”</span> or not within its + category. The parser will prefer casting to preferred types (but only from + other types within the same category) when this rule is helpful in + resolving overloaded functions or operators. For more details see <a class="xref" href="typeconv.html" title="Chapter 10. Type Conversion">Chapter 10</a>. For types that have no implicit casts to or from any + other types, it is sufficient to leave these settings at the defaults. + However, for a group of related types that have implicit casts, it is often + helpful to mark them all as belonging to a category and select one or two + of the <span class="quote">“<span class="quote">most general</span>”</span> types as being preferred within the category. + The <em class="replaceable"><code>category</code></em> parameter is + especially useful when adding a user-defined type to an existing built-in + category, such as the numeric or string types. However, it is also + possible to create new entirely-user-defined type categories. Select any + ASCII character other than an upper-case letter to name such a category. + </p><p> + A default value can be specified, in case a user wants columns of the + data type to default to something other than the null value. + Specify the default with the <code class="literal">DEFAULT</code> key word. + (Such a default can be overridden by an explicit <code class="literal">DEFAULT</code> + clause attached to a particular column.) + </p><p> + To indicate that a type is a fixed-length array type, + specify the type of the array + elements using the <code class="literal">ELEMENT</code> key word. For example, to + define an array of 4-byte integers (<code class="type">int4</code>), specify + <code class="literal">ELEMENT = int4</code>. For more details, + see <a class="xref" href="sql-createtype.html#SQL-CREATETYPE-ARRAY" title="Array Types">Array Types</a> below. + </p><p> + To indicate the delimiter to be used between values in the external + representation of arrays of this type, <em class="replaceable"><code>delimiter</code></em> can be + set to a specific character. The default delimiter is the comma + (<code class="literal">,</code>). Note that the delimiter is associated + with the array element type, not the array type itself. + </p><p> + If the optional Boolean + parameter <em class="replaceable"><code>collatable</code></em> + is true, column definitions and expressions of the type may carry + collation information through use of + the <code class="literal">COLLATE</code> clause. It is up to the + implementations of the functions operating on the type to actually + make use of the collation information; this does not happen + automatically merely by marking the type collatable. + </p></div><div class="refsect2" id="SQL-CREATETYPE-ARRAY"><h3>Array Types</h3><p> + Whenever a user-defined type is created, + <span class="productname">PostgreSQL</span> automatically creates an + associated array type, whose name consists of the element type's + name prepended with an underscore, and truncated if necessary to keep + it less than <code class="symbol">NAMEDATALEN</code> bytes long. (If the name + so generated collides with an existing type name, the process is + repeated until a non-colliding name is found.) + This implicitly-created array type is variable length and uses the + built-in input and output functions <code class="literal">array_in</code> and + <code class="literal">array_out</code>. Furthermore, this type is what the system + uses for constructs such as <code class="literal">ARRAY[]</code> over the + user-defined type. The array type tracks any changes in its + element type's owner or schema, and is dropped if the element type is. + </p><p> + You might reasonably ask why there is an <code class="option">ELEMENT</code> + option, if the system makes the correct array type automatically. + The main case where it's useful to use <code class="option">ELEMENT</code> is when you are + making a fixed-length type that happens to be internally an array of a number of + identical things, and you want to allow these things to be accessed + directly by subscripting, in addition to whatever operations you plan + to provide for the type as a whole. For example, type <code class="type">point</code> + is represented as just two floating-point numbers, which can be accessed + using <code class="literal">point[0]</code> and <code class="literal">point[1]</code>. + Note that + this facility only works for fixed-length types whose internal form + is exactly a sequence of identical fixed-length fields. + For historical reasons (i.e., this is clearly wrong but it's far too + late to change it), subscripting of fixed-length array types starts from + zero, rather than from one as for variable-length arrays. + </p><p> + Specifying the <code class="option">SUBSCRIPT</code> option allows a data type to + be subscripted, even though the system does not otherwise regard it as + an array type. The behavior just described for fixed-length arrays is + actually implemented by the <code class="option">SUBSCRIPT</code> handler + function <code class="function">raw_array_subscript_handler</code>, which is + used automatically if you specify <code class="option">ELEMENT</code> for a + fixed-length type without also writing <code class="option">SUBSCRIPT</code>. + </p><p> + When specifying a custom <code class="option">SUBSCRIPT</code> function, it is + not necessary to specify <code class="option">ELEMENT</code> unless + the <code class="option">SUBSCRIPT</code> handler function needs to + consult <code class="structfield">typelem</code> to find out what to return. + Be aware that specifying <code class="option">ELEMENT</code> causes the system to + assume that the new type contains, or is somehow physically dependent on, + the element type; thus for example changing properties of the element + type won't be allowed if there are any columns of the dependent type. + </p></div></div><div class="refsect1" id="id-1.9.3.94.6"><h2>Parameters</h2><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>name</code></em></span></dt><dd><p> + The name (optionally schema-qualified) of a type to be created. + </p></dd><dt><span class="term"><em class="replaceable"><code>attribute_name</code></em></span></dt><dd><p> + The name of an attribute (column) for the composite type. + </p></dd><dt><span class="term"><em class="replaceable"><code>data_type</code></em></span></dt><dd><p> + The name of an existing data type to become a column of the + composite type. + </p></dd><dt><span class="term"><em class="replaceable"><code>collation</code></em></span></dt><dd><p> + The name of an existing collation to be associated with a column of + a composite type, or with a range type. + </p></dd><dt><span class="term"><em class="replaceable"><code>label</code></em></span></dt><dd><p> + A string literal representing the textual label associated with + one value of an enum type. + </p></dd><dt><span class="term"><em class="replaceable"><code>subtype</code></em></span></dt><dd><p> + The name of the element type that the range type will represent ranges + of. + </p></dd><dt><span class="term"><em class="replaceable"><code>subtype_operator_class</code></em></span></dt><dd><p> + The name of a b-tree operator class for the subtype. + </p></dd><dt><span class="term"><em class="replaceable"><code>canonical_function</code></em></span></dt><dd><p> + The name of the canonicalization function for the range type. + </p></dd><dt><span class="term"><em class="replaceable"><code>subtype_diff_function</code></em></span></dt><dd><p> + The name of a difference function for the subtype. + </p></dd><dt><span class="term"><em class="replaceable"><code>multirange_type_name</code></em></span></dt><dd><p> + The name of the corresponding multirange type. + </p></dd><dt><span class="term"><em class="replaceable"><code>input_function</code></em></span></dt><dd><p> + The name of a function that converts data from the type's + external textual form to its internal form. + </p></dd><dt><span class="term"><em class="replaceable"><code>output_function</code></em></span></dt><dd><p> + The name of a function that converts data from the type's + internal form to its external textual form. + </p></dd><dt><span class="term"><em class="replaceable"><code>receive_function</code></em></span></dt><dd><p> + The name of a function that converts data from the type's + external binary form to its internal form. + </p></dd><dt><span class="term"><em class="replaceable"><code>send_function</code></em></span></dt><dd><p> + The name of a function that converts data from the type's + internal form to its external binary form. + </p></dd><dt><span class="term"><em class="replaceable"><code>type_modifier_input_function</code></em></span></dt><dd><p> + The name of a function that converts an array of modifier(s) for the type + into internal form. + </p></dd><dt><span class="term"><em class="replaceable"><code>type_modifier_output_function</code></em></span></dt><dd><p> + The name of a function that converts the internal form of the type's + modifier(s) to external textual form. + </p></dd><dt><span class="term"><em class="replaceable"><code>analyze_function</code></em></span></dt><dd><p> + The name of a function that performs statistical analysis for the + data type. + </p></dd><dt><span class="term"><em class="replaceable"><code>subscript_function</code></em></span></dt><dd><p> + The name of a function that defines what subscripting a value of the + data type does. + </p></dd><dt><span class="term"><em class="replaceable"><code>internallength</code></em></span></dt><dd><p> + A numeric constant that specifies the length in bytes of the new + type's internal representation. The default assumption is that + it is variable-length. + </p></dd><dt><span class="term"><em class="replaceable"><code>alignment</code></em></span></dt><dd><p> + The storage alignment requirement of the data type. If specified, + it must be <code class="literal">char</code>, <code class="literal">int2</code>, + <code class="literal">int4</code>, or <code class="literal">double</code>; the + default is <code class="literal">int4</code>. + </p></dd><dt><span class="term"><em class="replaceable"><code>storage</code></em></span></dt><dd><p> + The storage strategy for the data type. If specified, must be + <code class="literal">plain</code>, <code class="literal">external</code>, + <code class="literal">extended</code>, or <code class="literal">main</code>; the + default is <code class="literal">plain</code>. + </p></dd><dt><span class="term"><em class="replaceable"><code>like_type</code></em></span></dt><dd><p> + The name of an existing data type that the new type will have the + same representation as. The values of + <em class="replaceable"><code>internallength</code></em>, + <em class="replaceable"><code>passedbyvalue</code></em>, + <em class="replaceable"><code>alignment</code></em>, and + <em class="replaceable"><code>storage</code></em> + are copied from that type, unless overridden by explicit + specification elsewhere in this <code class="command">CREATE TYPE</code> command. + </p></dd><dt><span class="term"><em class="replaceable"><code>category</code></em></span></dt><dd><p> + The category code (a single ASCII character) for this type. + The default is <code class="literal">'U'</code> for <span class="quote">“<span class="quote">user-defined type</span>”</span>. + Other standard category codes can be found in + <a class="xref" href="catalog-pg-type.html#CATALOG-TYPCATEGORY-TABLE" title="Table 53.65. typcategory Codes">Table 53.65</a>. You may also choose + other ASCII characters in order to create custom categories. + </p></dd><dt><span class="term"><em class="replaceable"><code>preferred</code></em></span></dt><dd><p> + True if this type is a preferred type within its type category, + else false. The default is false. Be very careful about creating + a new preferred type within an existing type category, as this + could cause surprising changes in behavior. + </p></dd><dt><span class="term"><em class="replaceable"><code>default</code></em></span></dt><dd><p> + The default value for the data type. If this is omitted, the + default is null. + </p></dd><dt><span class="term"><em class="replaceable"><code>element</code></em></span></dt><dd><p> + The type being created is an array; this specifies the type of + the array elements. + </p></dd><dt><span class="term"><em class="replaceable"><code>delimiter</code></em></span></dt><dd><p> + The delimiter character to be used between values in arrays made + of this type. + </p></dd><dt><span class="term"><em class="replaceable"><code>collatable</code></em></span></dt><dd><p> + True if this type's operations can use collation information. + The default is false. + </p></dd></dl></div></div><div class="refsect1" id="SQL-CREATETYPE-NOTES"><h2>Notes</h2><p> + Because there are no restrictions on use of a data type once it's been + created, creating a base type or range type is tantamount to granting + public execute permission on the functions mentioned in the type definition. + This is usually + not an issue for the sorts of functions that are useful in a type + definition. But you might want to think twice before designing a type + in a way that would require <span class="quote">“<span class="quote">secret</span>”</span> information to be used + while converting it to or from external form. + </p><p> + Before <span class="productname">PostgreSQL</span> version 8.3, the name of + a generated array type was always exactly the element type's name with one + underscore character (<code class="literal">_</code>) prepended. (Type names were + therefore restricted in length to one fewer character than other names.) + While this is still usually the case, the array type name may vary from + this in case of maximum-length names or collisions with user type names + that begin with underscore. Writing code that depends on this convention + is therefore deprecated. Instead, use + <code class="structname">pg_type</code>.<code class="structfield">typarray</code> to locate the array type + associated with a given type. + </p><p> + It may be advisable to avoid using type and table names that begin with + underscore. While the server will change generated array type names to + avoid collisions with user-given names, there is still risk of confusion, + particularly with old client software that may assume that type names + beginning with underscores always represent arrays. + </p><p> + Before <span class="productname">PostgreSQL</span> version 8.2, the shell-type + creation syntax + <code class="literal">CREATE TYPE <em class="replaceable"><code>name</code></em></code> did not exist. + The way to create a new base type was to create its input function first. + In this approach, <span class="productname">PostgreSQL</span> will first see + the name of the new data type as the return type of the input function. + The shell type is implicitly created in this situation, and then it + can be referenced in the definitions of the remaining I/O functions. + This approach still works, but is deprecated and might be disallowed in + some future release. Also, to avoid accidentally cluttering + the catalogs with shell types as a result of simple typos in function + definitions, a shell type will only be made this way when the input + function is written in C. + </p></div><div class="refsect1" id="id-1.9.3.94.8"><h2>Examples</h2><p> + This example creates a composite type and uses it in + a function definition: +</p><pre class="programlisting"> +CREATE TYPE compfoo AS (f1 int, f2 text); + +CREATE FUNCTION getfoo() RETURNS SETOF compfoo AS $$ + SELECT fooid, fooname FROM foo +$$ LANGUAGE SQL; +</pre><p> + </p><p> + This example creates an enumerated type and uses it in + a table definition: +</p><pre class="programlisting"> +CREATE TYPE bug_status AS ENUM ('new', 'open', 'closed'); + +CREATE TABLE bug ( + id serial, + description text, + status bug_status +); +</pre><p> + </p><p> + This example creates a range type: +</p><pre class="programlisting"> +CREATE TYPE float8_range AS RANGE (subtype = float8, subtype_diff = float8mi); +</pre><p> + </p><p> + This example creates the base data type <code class="type">box</code> and then uses the + type in a table definition: +</p><pre class="programlisting"> +CREATE TYPE box; + +CREATE FUNCTION my_box_in_function(cstring) RETURNS box AS ... ; +CREATE FUNCTION my_box_out_function(box) RETURNS cstring AS ... ; + +CREATE TYPE box ( + INTERNALLENGTH = 16, + INPUT = my_box_in_function, + OUTPUT = my_box_out_function +); + +CREATE TABLE myboxes ( + id integer, + description box +); +</pre><p> + </p><p> + If the internal structure of <code class="type">box</code> were an array of four + <code class="type">float4</code> elements, we might instead use: +</p><pre class="programlisting"> +CREATE TYPE box ( + INTERNALLENGTH = 16, + INPUT = my_box_in_function, + OUTPUT = my_box_out_function, + ELEMENT = float4 +); +</pre><p> + which would allow a box value's component numbers to be accessed + by subscripting. Otherwise the type behaves the same as before. + </p><p> + This example creates a large object type and uses it in + a table definition: +</p><pre class="programlisting"> +CREATE TYPE bigobj ( + INPUT = lo_filein, OUTPUT = lo_fileout, + INTERNALLENGTH = VARIABLE +); +CREATE TABLE big_objs ( + id integer, + obj bigobj +); +</pre><p> + </p><p> + More examples, including suitable input and output functions, are + in <a class="xref" href="xtypes.html" title="38.13. User-Defined Types">Section 38.13</a>. + </p></div><div class="refsect1" id="SQL-CREATETYPE-COMPATIBILITY"><h2>Compatibility</h2><p> + The first form of the <code class="command">CREATE TYPE</code> command, which + creates a composite type, conforms to the <acronym class="acronym">SQL</acronym> standard. + The other forms are <span class="productname">PostgreSQL</span> + extensions. The <code class="command">CREATE TYPE</code> statement in + the <acronym class="acronym">SQL</acronym> standard also defines other forms that are not + implemented in <span class="productname">PostgreSQL</span>. + </p><p> + The ability to create a composite type with zero attributes is + a <span class="productname">PostgreSQL</span>-specific deviation from the + standard (analogous to the same case in <code class="command">CREATE TABLE</code>). + </p></div><div class="refsect1" id="SQL-CREATETYPE-SEE-ALSO"><h2>See Also</h2><span class="simplelist"><a class="xref" href="sql-altertype.html" title="ALTER TYPE"><span class="refentrytitle">ALTER TYPE</span></a>, <a class="xref" href="sql-createdomain.html" title="CREATE DOMAIN"><span class="refentrytitle">CREATE DOMAIN</span></a>, <a class="xref" href="sql-createfunction.html" title="CREATE FUNCTION"><span class="refentrytitle">CREATE FUNCTION</span></a>, <a class="xref" href="sql-droptype.html" title="DROP TYPE"><span class="refentrytitle">DROP TYPE</span></a></span></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-createtrigger.html" title="CREATE TRIGGER">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-createuser.html" title="CREATE USER">Next</a></td></tr><tr><td width="40%" align="left" valign="top">CREATE TRIGGER </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 15.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> CREATE USER</td></tr></table></div></body></html>
\ No newline at end of file |