create_type.sgml

关系型数据库 Postgresql 6.5.2

<refentry id="SQL-CREATETYPE">
 <refmeta>
  <refentrytitle>
   CREATE TYPE
  </refentrytitle>
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>
 <refnamediv>
  <refname>
   CREATE TYPE
  </refname>
  <refpurpose>
   Defines a new base data type
  </refpurpose>
 </refnamediv>
 <refsynopsisdiv>
  <refsynopsisdivinfo>
   <date>1998-09-21</date>
  </refsynopsisdivinfo>
  <synopsis>
CREATE TYPE <replaceable class="parameter">typename</replaceable> (
        INPUT          = <replaceable class="parameter">input_function</replaceable>
      , OUTPUT         = <replaceable class="parameter">output_function</replaceable>
      , INTERNALLENGTH = (<replaceable class="parameter">internallength</replaceable> | VARIABLE)
    [ , EXTERNALLENGTH = (<replaceable class="parameter">externallength</replaceable> | VARIABLE) ]
    [ , ELEMENT        = <replaceable class="parameter">element</replaceable> ]
    [ , DELIMITER      = <replaceable class="parameter">delimiter</replaceable> ]
    [ , DEFAULT        = "<replaceable class="parameter">default</replaceable>" ]
    [ , SEND           = <replaceable class="parameter">send_function</replaceable> ]
    [ , RECEIVE        = <replaceable class="parameter">receive_function</replaceable> ]
    [ , PASSEDBYVALUE ]
)
  </synopsis>
  
  <refsect2 id="R2-SQL-CREATETYPE-1">
   <refsect2info>
    <date>1998-09-21</date>
   </refsect2info>
   <title>
    Inputs
   </title>
   <para>

    <variablelist>
     <varlistentry>
      <term><replaceable class="parameter">typename</replaceable></term>
      <listitem>
       <para>
	The name of a type to be created.
       </para>
      </listitem>
     </varlistentry>
     <varlistentry>
      <term>INTERNALLENGTH <replaceable class="parameter">internallength</replaceable></term>
      <listitem>
       <para>
	A literal value, which specifies the internal length of
	the new type.
       </para>
      </listitem>
     </varlistentry>
     <varlistentry>
      <term>EXTERNALLENGTH <replaceable class="parameter">externallength</replaceable></term>
      <listitem>
       <para>
	A literal value, which specifies the external length of
	the new type.
       </para>
      </listitem>
     </varlistentry>
     <varlistentry>
      <term>INPUT <replaceable class="parameter">input_function</replaceable></term>
      <listitem>
       <para>
	The name of a function, created by CREATE FUNCTION, which
	converts data from its external form to the type's
	internal form.
       </para>
      </listitem>
     </varlistentry>
     <varlistentry>
      <term>OUTPUT <replaceable class="parameter">output_function</replaceable></term>
      <listitem>
       <para>
	The name of a function, created by CREATE FUNCTION, which
	converts data from its internal form to a form suitable
	for display.
       </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 for the array.
       </para>
      </listitem>
     </varlistentry>
     <varlistentry>
      <term><replaceable class="parameter">default</replaceable></term>
      <listitem>
       <para>
	The default text to be displayed to indicate "data
	not present"
       </para>
      </listitem>
     </varlistentry>
     <varlistentry>
      <term><replaceable class="parameter">send_function</replaceable></term>
      <listitem>
       <para>
	The name of a function, created by CREATE FUNCTION, which
	converts data of this type into a form suitable for
	transmission to another machine.
	<comment>Is this right?</comment>
       </para>
      </listitem>
     </varlistentry>
     <varlistentry>
      <term><replaceable class="parameter">receive_function</replaceable></term>
      <listitem>
       <para>
	The name of a function, created by CREATE FUNCTION, which
	converts data of this type from a form suitable for
	transmission from another machine to internal form.
	<comment>Is this right?</comment>
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </para>
  </refsect2>

  <refsect2 id="R2-SQL-CREATETYPE-2">
   <refsect2info>
    <date>1998-09-21</date>
   </refsect2info>
   <title>
    Outputs
   </title>
   <para>

    <variablelist>
     <varlistentry>
      <term><computeroutput>
CREATE
       </computeroutput></term>
      <listitem>
       <para>
	Message returned if the type is successfully created.
       </para>
      </listitem>
     </varlistentry>
    </variablelist>
   </para>
  </refsect2>
 </refsynopsisdiv>

 <refsect1 id="R1-SQL-CREATETYPE-1">
  <refsect1info>
   <date>1998-09-21</date>
  </refsect1info>
  <title>
   Description
  </title>
  <para>
   <command>CREATE TYPE</command>  allows  the user to register a new user data
   type with Postgres for use in the current data base.   The
   user  who  defines  a type becomes its owner.
   <replaceable class="parameter">typename</replaceable> is
   the name of the new type and must  be  unique  within  the
   types defined for this database.
  </para>
  <para>
   <command>CREATE TYPE</command>  requires  the  registration of two functions
   (using create function) before defining the type.   The
   representation  of  a  new  base  type  is  determined  by
   <replaceable class="parameter">input_function</replaceable>, which
   converts the type's external  representation  to  an  internal
   representation  usable by the
   operators and functions defined for the type.   Naturally,
   <replaceable class="parameter">output_function</replaceable>
   performs the reverse transformation.  Both
   the input and output functions must be  declared  to  take
   one or two arguments of type "<literal>opaque</literal>".
  </para>
  <para>
   New  base  data  types  can be fixed length, in which case
   <replaceable class="parameter">internallength</replaceable> is a
   positive integer, or variable  length,
   in  which  case Postgres assumes that the new type has the
   same format
   as the Postgres-supplied  data  type, "<literal>text</literal>".
   To  indicate that a type is variable-length, set
   <replaceable class="parameter">internallength</replaceable>
   to <option>VARIABLE</option>.
   The external representation is  similarly specified using the
   <replaceable class="parameter">externallength</replaceable>
   keyword.
  </para>
  <para>
   To indicate that a type is an array and to indicate that a
   type has array elements, indicate the type  of  the  array
   element using the element keyword.  For example, to define
   an array of 4 byte integers ("int4"), specify
   <programlisting>ELEMENT = int4</programlisting>
  </para>
  <para>
   To indicate the delimiter to be used  on  arrays  of  this
   type,  <replaceable class="parameter">delimiter</replaceable>
   can be
   set to a specific character.  The default delimiter is the comma
   ("<literal>,</literal>").
  </para>
  <para>
   A default value is optionally available  in  case  a  user
   wants  some  specific  bit  pattern to mean "data not present."
   Specify the default with the <literal>DEFAULT</literal> keyword.
   <comment>How does the user specify that bit pattern and associate
    it with the fact that the data is not present></comment>
  </para>
  <para>
   The optional functions
   <replaceable class="parameter">send_function</replaceable> and
   <replaceable class="parameter">receive_function</replaceable>
   are  used when the application program requesting Postgres
   services resides on a different machine.   In  this  case,
   the  machine  on  which  Postgres runs may use a format for the data
   type different from that used on the remote  machine.
   In  this case it is appropriate to convert data items to a
   standard form when sending from the server to  the  client
   and  converting  from  the  standard format to the machine
   specific format when the server receives the data from the
   client.   If these functions are not specified, then it is
   assumed that the internal format of the type is acceptable
   on  all relevant machine architectures.  For example, single
   characters do not have to be converted if passed  from
   a Sun-4 to a DECstation, but many other types do.
  </para>
  <para>
   The  optional flag,  <option>PASSEDBYVALUE</option>, indicates that operators
   and functions which use this data type should be passed an
   argument  by  value  rather  than by reference.  Note that you
   may not pass by value types whose internal representation is 
   more than four bytes.
  </para>
  <para>
   For new base types, a user can define operators, functions
   and aggregates using the appropriate facilities  described
   in this section.
  </para>

  <refsect2>
   <title>Array Types</title>
   <para>
    Two   generalized   built-in   functions,   array_in   and
    array_out, exist for  quick  creation  of  variable-length
    array  types.   These  functions  operate on arrays of any
    existing Postgres type.
   </para>
  </refsect2>

  <refsect2>
   <title>Large Object Types</title>
   <para>
    A "regular" Postgres  type  can  only  be  8192  bytes  in
    length.  If you need a larger type you must create a Large
    Object type.  The interface for these types  is  discussed
    at  length  in
    <citetitle>The PostgreSQL Programmer's Guide</citetitle>.
    The length of all large object types is always VARIABLE.
   </para>
  </refsect2>
 </refsect1>

 <refsect1>
  <title>Examples</title>
  <para>
   This command creates the box data type and then uses the
   type in a class definition:
  </para>
  <programlisting>
CREATE TYPE box (INTERNALLENGTH = 8,
    INPUT = my_procedure_1, OUTPUT = my_procedure_2);
CREATE TABLE myboxes (id INT4, description box);
  </programlisting>
  <para>
   This command creates a variable length array type with
   integer elements.

   <programlisting>
CREATE TYPE int4array (INPUT = array_in, OUTPUT = array_out,
    INTERNALLENGTH = VARIABLE, ELEMENT = int4);
CREATE TABLE myarrays (id int4, numbers int4array);
   </programlisting>
  </para>
  <para>
   This command creates a large object type and uses it in
   a class definition:

   <programlisting>
CREATE TYPE bigobj (INPUT = lo_filein, OUTPUT = lo_fileout,
    INTERNALLENGTH = VARIABLE);
CREATE TABLE big_objs (id int4, obj bigobj);
   </programlisting>
  </para>

  <refsect2>
   <title>Restrictions</title>
   <para>
    Type  names  cannot  begin  with  the underscore character
    ("_") and can only be 31 characters long.  This is because
    Postgres silently creates an array type for each base type
    with a name consisting of the base type's  name  prepended
    with an underscore.
   </para>
  </refsect2>

  <refsect2 id="R2-SQL-CREATETYPE-3">
   <refsect2info>
    <date>1998-09-21</date>
   </refsect2info>
   <title>
    Notes
   </title>
   <para>
    Refer to <command>DROP TYPE</command> to remove an existing type.
   </para>
   <para>
    See also <command>CREATE  FUNCTION</command>,
    <command>CREATE  OPERATOR</command> and the chapter on Large Objects
    in the <citetitle>PostgreSQL Programmer's Guide</citetitle>.
   </para>
  </refsect2>
 </refsect1>
 
 
 <refsect1 id="R1-SQL-CREATETYPE-3">
  <title>
   Compatibility
  </title>

  <refsect2 id="R2-SQL-CREATETYPE-4">
   <refsect2info>
    <date>1998-09-21</date>
   </refsect2info>
   <title>
    SQL3
   </title>
   <para>
   <command>CREATE TYPE</command> is an <acronym>SQL3</acronym> statement.
   </para>
  </refsect2>
 </refsect1>
</refentry>


<!-- Keep this comment at the end of the file
Local variables:
mode: sgml
sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"../reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:"/usr/lib/sgml/catalog"
sgml-local-ecat-files:nil
End:
-->