create_type.sgml

postgresql8.3.4源码,开源数据库

  <variablelist>
   <varlistentry>
    <term><replaceable class="parameter">name</replaceable></term>
    <listitem>
     <para>
      The name (optionally schema-qualified) of a type to be created.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="parameter">attribute_name</replaceable></term>
    <listitem>
     <para>
      The name of an attribute (column) for the composite type.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="parameter">data_type</replaceable></term>
    <listitem>
     <para>
      The name of an existing data type to become a column of the
      composite type.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="parameter">label</replaceable></term>
    <listitem>
     <para>
      A string literal representing the textual label associated with
      one value of an enum type.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="parameter">input_function</replaceable></term>
    <listitem>
     <para>
      The name of a function that converts data from the type's
      external textual form to its internal form.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="parameter">output_function</replaceable></term>
    <listitem>
     <para>
      The name of a function that converts data from the type's
      internal form to its external textual form.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="parameter">receive_function</replaceable></term>
    <listitem>
     <para>
      The name of a function that converts data from the type's
      external binary form to its internal form.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="parameter">send_function</replaceable></term>
    <listitem>
     <para>
      The name of a function that converts data from the type's
      internal form to its external binary form.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="parameter">type_modifier_input_function</replaceable></term>
    <listitem>
     <para>
      The name of a function that converts an array of modifier(s) for the type
      into internal form.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="parameter">type_modifier_output_function</replaceable></term>
    <listitem>
     <para>
      The name of a function that converts the internal form of the type's
      modifier(s) to external textual form.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="parameter">analyze_function</replaceable></term>
    <listitem>
     <para>
      The name of a function that performs statistical analysis for the
      data type.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="parameter">internallength</replaceable></term>
    <listitem>
     <para>
      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.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="parameter">alignment</replaceable></term>
    <listitem>
     <para>
      The storage alignment requirement of the data type.  If specified,
      it must be <literal>char</literal>, <literal>int2</literal>,
      <literal>int4</literal>, or <literal>double</literal>; the
      default is <literal>int4</literal>.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="parameter">storage</replaceable></term>
    <listitem>
     <para>
      The storage strategy for the data type.  If specified, must be
      <literal>plain</literal>, <literal>external</literal>,
      <literal>extended</literal>, or <literal>main</literal>; the
      default is <literal>plain</literal>.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="parameter">default</replaceable></term>
    <listitem>
     <para>
      The default value for the data type.  If this is omitted, the
      default is null.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="parameter">element</replaceable></term>
    <listitem>
     <para>
      The type being created is an array; this specifies the type of
      the array elements.
     </para>
    </listitem>
   </varlistentry>

   <varlistentry>
    <term><replaceable class="parameter">delimiter</replaceable></term>
    <listitem>
     <para>
      The delimiter character to be used between values in arrays made
      of this type.
     </para>
    </listitem>
   </varlistentry>
  </variablelist>
 </refsect1>

 <refsect1 id="SQL-CREATETYPE-notes">
  <title>Notes</title>

  <para>
   Because there are no restrictions on use of a data type once it's been
   created, creating a base type is tantamount to granting public execute
   permission on the functions mentioned in the type definition.  (The creator
   of the type is therefore required to own these functions.)  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 <quote>secret</> information to be used
   while converting it to or from external form.
  </para>

  <para>
   Before <productname>PostgreSQL</productname> version 8.3, the name of
   a generated array type was always exactly the element type's name with one
   underscore character (<literal>_</literal>) prepended.  (Type names were
   therefore restricted in length to one less 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
   <structname>pg_type</>.<structfield>typarray</> to locate the array type
   associated with a given type.
  </para>

  <para>
   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.
  </para>

  <para>
   Before <productname>PostgreSQL</productname> version 8.2, the syntax
   <literal>CREATE TYPE <replaceable>name</></literal> did not exist.
   The way to create a new base type was to create its input function first.
   In this approach, <productname>PostgreSQL</productname> 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.
  </para>

  <para>
   In <productname>PostgreSQL</productname> versions before 7.3, it
   was customary to avoid creating a shell type at all, by replacing the
   functions' forward references to the type name with the placeholder
   pseudotype <type>opaque</>.  The <type>cstring</> arguments and
   results also had to be declared as <type>opaque</> before 7.3.  To
   support loading of old dump files, <command>CREATE TYPE</> will
   accept I/O functions declared using <type>opaque</>, but it will issue
   a notice and change the function declarations to use the correct
   types.
  </para>

 </refsect1>

 <refsect1>
  <title>Examples</title>

  <para>
   This example creates a composite type and uses it in
   a function definition:
<programlisting>
CREATE TYPE compfoo AS (f1 int, f2 text);

CREATE FUNCTION getfoo() RETURNS SETOF compfoo AS $$
    SELECT fooid, fooname FROM foo
$$ LANGUAGE SQL;
</programlisting>
  </para>

  <para>
   This example creates an enumerated type and uses it in
   a table definition:
<programlisting>
CREATE TYPE bug_status AS ENUM ('new', 'open', 'closed');

CREATE TABLE bug (
    id serial,
    description text,
    status bug_status
);
</programlisting>
  </para>

  <para>
   This example creates the base data type <type>box</type> and then uses the
   type in a table definition:
<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
);
</programlisting>
  </para>

  <para>
   If the internal structure of <type>box</type> were an array of four
   <type>float4</> elements, we might instead use:
<programlisting>
CREATE TYPE box (
    INTERNALLENGTH = 16,
    INPUT = my_box_in_function,
    OUTPUT = my_box_out_function,
    ELEMENT = float4
);
</programlisting>
   which would allow a box value's component numbers to be accessed
   by subscripting.  Otherwise the type behaves the same as before.
  </para>

  <para>
   This example creates a large object type and uses it in
   a table definition:
<programlisting>
CREATE TYPE bigobj (
    INPUT = lo_filein, OUTPUT = lo_fileout,
    INTERNALLENGTH = VARIABLE
);
CREATE TABLE big_objs (
    id integer,
    obj bigobj
);
</programlisting>
  </para>

  <para>
   More examples, including suitable input and output functions, are
   in <xref linkend="xtypes">.
  </para>
 </refsect1>

 <refsect1 id="SQL-CREATETYPE-compatibility">
  <title>Compatibility</title>

  <para>
   This <command>CREATE TYPE</command> command is a
   <productname>PostgreSQL</productname> extension.  There is a
   <command>CREATE TYPE</command> statement in the <acronym>SQL</> standard
   that is rather different in detail.
  </para>
 </refsect1>

 <refsect1 id="SQL-CREATETYPE-see-also">
  <title>See Also</title>

  <simplelist type="inline">
   <member><xref linkend="sql-createfunction" endterm="sql-createfunction-title"></member>
   <member><xref linkend="sql-droptype" endterm="sql-droptype-title"></member>
   <member><xref linkend="sql-altertype" endterm="sql-altertype-title"></member>
   <member><xref linkend="sql-createdomain" endterm="sql-createdomain-title"></member>
  </simplelist>
 </refsect1>

</refentry>