<!--
$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_type.sgml,v 1.34 2002/08/29 00:17:01 tgl Exp $
PostgreSQL documentation
-->

<refentry id="SQL-CREATETYPE">
 <refmeta>
  <refentrytitle id="sql-createtype-title">CREATE TYPE</refentrytitle>
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>
 <refnamediv>
  <refname>
   CREATE TYPE
  </refname>
  <refpurpose>
   define a new data type
  </refpurpose>
 </refnamediv>
 <refsynopsisdiv>
  <refsynopsisdivinfo>
   <date>1999-07-20</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 }
    [ , DEFAULT = <replaceable class="parameter">default</replaceable> ]
    [ , ELEMENT = <replaceable class="parameter">element</replaceable> ] [ , DELIMITER = <replaceable class="parameter">delimiter</replaceable> ]
    [ , PASSEDBYVALUE ]
    [ , ALIGNMENT = <replaceable class="parameter">alignment</replaceable> ]
    [ , STORAGE = <replaceable class="parameter">storage</replaceable> ]
)

CREATE TYPE <replaceable class="parameter">typename</replaceable> AS
    ( <replaceable class="PARAMETER">column_name</replaceable> <replaceable class="PARAMETER">data_type</replaceable> [, ... ] )
  </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 (optionally schema-qualified) of a type to be created.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><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><replaceable class="parameter">input_function</replaceable></term>
      <listitem>
       <para>
	The name of a function, created by
	<command>CREATE FUNCTION</command>, which
	converts data from its external form to the type's
	internal form.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="parameter">output_function</replaceable></term>
      <listitem>
       <para>
	The name of a function, created by
	<command>CREATE FUNCTION</command>, 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 to be used between values in arrays made
	of this type.
       </para>
      </listitem>
     </varlistentry>

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

     <varlistentry>
      <term><replaceable class="parameter">alignment</replaceable></term>
      <listitem>
       <para>
        Storage alignment requirement of the data type.  If specified, 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>
        Storage technique 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">column_name</replaceable></term>
      <listitem>
       <para>
        The name of a column of the composite type.
       </para>
      </listitem>
     </varlistentry>

     <varlistentry>
      <term><replaceable class="PARAMETER">data_type</replaceable></term>
      <listitem>
       <para>
        The name of an existing data type.
       </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 TYPE
       </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 data
   type with <productname>PostgreSQL</> for use in the current data base.
   The user  who  defines  a type becomes its owner.
  </para>

  <para>
   If a schema name is given then the type is created in the
   specified schema.  Otherwise it is created in the current schema (the one
   at the front of the search path; see <literal>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 datatypes,
   type names also must not conflict with table names in the same schema.)
  </para>

  <refsect2>
   <title>Base Types</title>

  <para>
   The first form of <command>CREATE TYPE</command> creates a new base type
   (scalar type).  It  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.  The input function may be
   declared as taking one argument of type <type>cstring</type>,
   or as taking three arguments of types
   <type>cstring</type>, <type>OID</type>, <type>int4</type>.
   (The first argument is the input text as a C string, the second
   argument is the element type in case this is an array type,
   and the third is the typmod of the destination column, if known.)
   It should return a value of the datatype itself.
   The output function may be
   declared as taking one argument of the new datatype,  or as taking
   two arguments of which the second is type <type>OID</type>.
   (The second argument is again the array element type for array types.)
   The output function should return type <type>cstring</type>.
  </para>

  <para>
   You should at this point be wondering how the input and output functions
   can be declared to have results or inputs of the new type, when they have
   to be created before the new type can be created.  The answer is that the
   input function must be created first, then the output function, then the
   datatype.
   <productname>PostgreSQL</productname> will first see the name of the new
   datatype as the return type of the input function.  It will create a
   <quote>shell</> type, which is simply a placeholder entry in
   <literal>pg_type</>, and link the input function definition to the shell
   type.  Similarly the output function will be linked to the (now already
   existing) shell type.  Finally, <command>CREATE TYPE</> replaces the
   shell entry with a complete type definition, and the new type can be used.
  </para>

  <note>
  <para>
   In <productname>PostgreSQL</productname> versions before 7.3, it was
   customary to avoid creating a shell type by replacing the functions'
   forward references to the type name with the placeholder pseudo-type
   <type>OPAQUE</>.  The <type>cstring</> inputs and
   results also had to be declared as <type>OPAQUE</> before 7.3.
   Use of <type>OPAQUE</> for this purpose is still supported, but it is
   deprecated because it causes loss of type safety.
  </para>
  </note>

  <para>
   New  base  data  types  can be fixed length, in which case
   <replaceable class="parameter">internallength</replaceable> is a
   positive integer, or variable  length, indicated by setting
   <replaceable class="parameter">internallength</replaceable>
   to <option>VARIABLE</option>.  (Internally, this is represented
   by setting typlen to -1.)  The internal representation of all
   variable-length types must start with an integer giving the total
   length of this value of the type.
  </para>

  <para>
   To indicate that a type is an array,
   specify the type  of  the  array
   elements using the <option>ELEMENT</> keyword.  For example, to define
   an array of 4-byte integers ("int4"), specify
   <programlisting>ELEMENT = int4</programlisting>
   More details about array types appear below.
  </para>

  <para>
   To indicate the delimiter to be used between values in the external
   representation of arrays of this type, <replaceable
   class="parameter">delimiter</replaceable> can be
   set to a specific character.  The default delimiter is the comma
   ('<literal>,</literal>').  Note that the delimiter is associated
   with the array element type, not the array type itself.
  </para>

  <para>
   A default value may be specified, in case a user wants columns of the
   data type to default to something other than NULL.
   Specify the default with the <option>DEFAULT</option> keyword.
   (Such a default may be overridden by an explicit <option>DEFAULT</option>
   clause attached to a particular column.)
  </para>

  <para>
   The  optional flag,  <option>PASSEDBYVALUE</option>, indicates that
   values of this data type are passed
   by  value  rather  than by reference.  Note that you
   may not pass by value types whose internal representation is 
   longer than the width of the <type>Datum</> type (four bytes on
   most machines, eight bytes on a few).
  </para>

  <para>
   The <replaceable class="parameter">alignment</replaceable> keyword
   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 <type>int4</> as their first component.
  </para>

  <para>
   The <replaceable class="parameter">storage</replaceable> keyword
   allows selection of storage strategies for variable-length data types
   (only <literal>plain</literal> is allowed for fixed-length types).
   <literal>plain</literal> disables TOAST for the data type: it will always
   be stored in-line and not compressed.
   <literal>extended</literal> gives full TOAST capability: 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.
   <literal>external</literal> allows the value to be moved out of the main
   table, but the system will not try to compress it.
   <literal>main</literal> allows compression, but discourages moving the
   value out of the main table.  (Data items with this storage method may
   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
   <literal>extended</literal> and <literal>external</literal> items.)
  </para>
  </refsect2>

  <refsect2>
   <title>Composite Types</title>

  <para>
   The second form of <command>CREATE TYPE</command>
   creates a composite type.
   The composite type is specified by a list of column names and datatypes.
   This is essentially the same as the row type
   of a table, but using <command>CREATE TYPE</command> 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 as the return type of a function.
  </para>
  </refsect2>

  <refsect2>
   <title>Array Types</title>

   <para>
    Whenever a user-defined base data type is created, 
    <productname>PostgreSQL</productname> automatically creates an
    associated array type, whose name consists of the base type's
    name prepended with an underscore.  The parser understands this
    naming convention, and translates requests for columns of type
    <literal>foo[]</> into requests for type <literal>_foo</>.
    The implicitly-created array type is variable length and uses the
    built-in input and output functions <literal>array_in</> and
    <literal>array_out</>.
   </para>

   <para>
    You might reasonably ask <quote>why is there an <option>ELEMENT</>
    option, if the system makes the correct array type automatically?</quote>
    The only case where it's useful to use <option>ELEMENT</> is when you are
    making a fixed-length type that happens to be internally an array of N
    identical things, and you want to allow the N 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 <type>name</>
    allows its constituent <type>char</>s to be accessed this way.
    A 2-D <type>point</> type could allow its two component floats to be
    accessed like <literal>point[0]</> and <literal>point[1]</>.
    Note that
    this facility only works for fixed-length types whose internal form
    is exactly a sequence of N identical fixed-length fields.  A subscriptable
    variable-length type must have the generalized internal representation
    used by <literal>array_in</> and <literal>array_out</>.
    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.
   </para>
  </refsect2>
 </refsect1>

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

   <para>
    User-defined type names cannot begin with the underscore character
    (<quote><literal>_</literal></quote>) and can only be 62
    characters long (or in general <literal>NAMEDATALEN-2</>, rather than
    the <literal>NAMEDATALEN-1</> characters allowed for other names).
    Type names beginning with underscore are
    reserved for internally-created array type names.
   </para>
 </refsect1>
 
 <refsect1>
  <title>Examples</title>
  <para>
   This example creates the <type>box</type> data type and then uses the
   type in a table definition:
<programlisting>
CREATE TYPE box (INTERNALLENGTH = 16,
    INPUT = my_procedure_1, OUTPUT = my_procedure_2);
CREATE TABLE myboxes (id INT4, description box);
</programlisting>
  </para>

  <para>
   If <type>box</type>'s internal structure were an array of four
   <type>float4</>s, we might instead say
<programlisting>
CREATE TYPE box (INTERNALLENGTH = 16,
    INPUT = my_procedure_1, OUTPUT = my_procedure_2,
    ELEMENT = float4);
</programlisting>
   which would allow a box value's component floats 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 int4, obj bigobj);
</programlisting>
  </para>

  <para>
   This example creates a composite type and uses it in
   a table 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>
 </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 SQL99 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"></member>
   <member><xref linkend="sql-droptype"></member>
   <member><citetitle>PostgreSQL Programmer's Guide</citetitle></member>
  </simplelist>
 </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:
-->
