create_type.sgml

PostgreSQL7.4.6 for Linux

 <refsect1>
  <title>Parameters</title>

  <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">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">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>
   User-defined type names cannot begin with the underscore character
   (<literal>_</literal>) and can only be 62 characters
   long (or in general <symbol>NAMEDATALEN</symbol> - 2, rather than
   the <symbol>NAMEDATALEN</symbol> - 1 characters allowed for other
   names).  Type names beginning with underscore are reserved for
   internally-created array type names.
  </para>

  <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
   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 functions declared using <type>opaque</>, but it will issue
   a notice and change the function's declaration 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 the base data type <type>box</type> and then uses the
   type in a table definition:
<programlisting>
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="extend">.
  </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" endterm="sql-createfunction-title"></member>
   <member><xref linkend="sql-droptype" endterm="sql-droptype-title"></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:
-->