File: sql-createtype.html

package info (click to toggle)
mpsql 2.1-2
links: PTS
area: non-free
in suites: potato
size: 3,528 kB
ctags: 4,886
sloc: ansic: 35,184; makefile: 3,761; sh: 44
file content (646 lines) | stat: -rw-r--r-- 11,239 bytes
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML
><HEAD
><TITLE
>   CREATE TYPE
  </TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet version 1.19"><LINK
REL="HOME"
TITLE="PostgreSQL User's Guide"
HREF="user.html"><LINK
REL="UP"
TITLE="SQL Commands"
HREF="sql-commands.html"><LINK
REL="PREVIOUS"
TITLE="   CREATE TRIGGER
  "
HREF="sql-createtrigger.html"><LINK
REL="NEXT"
TITLE="   CREATE USER
  "
HREF="sql-createuser.html"></HEAD
><BODY
BGCOLOR="#FFFFFF"
TEXT="#000000"
><DIV
CLASS="NAVHEADER"
><TABLE
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="3"
ALIGN="center"
>PostgreSQL User's Guide</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="bottom"
><A
HREF="sql-createtrigger.html"
>Prev</A
></TD
><TD
WIDTH="80%"
ALIGN="center"
VALIGN="bottom"
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="bottom"
><A
HREF="sql-createuser.html"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><H1
>CREATE TYPE</H1
><DIV
CLASS="REFNAMEDIV"
><H2
>Name</H2
>   CREATE TYPE
   &#8212;    Defines a new base data type
  </DIV
><DIV
CLASS="REFSYNOPSISDIV"
><PRE
CLASS="SYNOPSIS"
>CREATE TYPE <TT
CLASS="REPLACEABLE"
><I
>typename</I
></TT
> (
        INPUT          = <TT
CLASS="REPLACEABLE"
><I
>input_function</I
></TT
>
      , OUTPUT         = <TT
CLASS="REPLACEABLE"
><I
>output_function</I
></TT
>
      , INTERNALLENGTH = (<TT
CLASS="REPLACEABLE"
><I
>internallength</I
></TT
> | VARIABLE)
    [ , EXTERNALLENGTH = (<TT
CLASS="REPLACEABLE"
><I
>externallength</I
></TT
> | VARIABLE) ]
    [ , ELEMENT        = <TT
CLASS="REPLACEABLE"
><I
>element</I
></TT
> ]
    [ , DELIMITER      = <TT
CLASS="REPLACEABLE"
><I
>delimiter</I
></TT
> ]
    [ , DEFAULT        = "<TT
CLASS="REPLACEABLE"
><I
>default</I
></TT
>" ]
    [ , SEND           = <TT
CLASS="REPLACEABLE"
><I
>send_function</I
></TT
> ]
    [ , RECEIVE        = <TT
CLASS="REPLACEABLE"
><I
>receive_function</I
></TT
> ]
    [ , PASSEDBYVALUE ]
)
  </PRE
><DIV
CLASS="REFSECT2"
><H3
>    Inputs
   </H3
><P
>   </P
><P
></P
><DL
><DT
><TT
CLASS="REPLACEABLE"
><I
>typename</I
></TT
></DT
><DD
><P
>          The name of a type to be created.
	  </P
></DD
><DT
>INTERNALLENGTH <TT
CLASS="REPLACEABLE"
><I
>internallength</I
></TT
></DT
><DD
><P
>	   A literal value, which specifies the internal length of
	   the new type.
	  </P
></DD
><DT
>EXTERNALLENGTH <TT
CLASS="REPLACEABLE"
><I
>externallength</I
></TT
></DT
><DD
><P
>	   A literal value, which specifies the external length of
	   the new type.
	  </P
></DD
><DT
>INPUT <TT
CLASS="REPLACEABLE"
><I
>input_function</I
></TT
></DT
><DD
><P
>	   The name of a function, created by CREATE FUNCTION, which
	   converts data from its external form to the type's
	   internal form.
	  </P
></DD
><DT
>OUTPUT <TT
CLASS="REPLACEABLE"
><I
>output_function</I
></TT
></DT
><DD
><P
>	   The name of a function, created by CREATE FUNCTION, which
	   converts data from its internal form to a form suitable
	   for display.
	  </P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
>element</I
></TT
></DT
><DD
><P
>	   The type being created is an array; this specifies
	   the type of the array elements.
	  </P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
>delimiter</I
></TT
></DT
><DD
><P
>	   The delimiter character for the array.
	  </P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
>default</I
></TT
></DT
><DD
><P
>	   The default text to be displayed to indicate "data
	   not present"
	  </P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
>send_function</I
></TT
></DT
><DD
><P
>	   The name of a function, created by CREATE FUNCTION, which
	   converts data of this type into a form suitable for
	   transmission to another machine.

	  </P
></DD
><DT
><TT
CLASS="REPLACEABLE"
><I
>receive_function</I
></TT
></DT
><DD
><P
>	   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.

	  </P
></DD
></DL
></DIV
><DIV
CLASS="REFSECT2"
><H3
>    Outputs
   </H3
><P
>&#13;       <P
></P
></P><DL
><DT
><SPAN
CLASS="RETURNVALUE"
>CREATE</SPAN
></DT
><DD
><P
>	   Message returned if the type is successfully created.

   </P
></DD
></DL
><P>
   
  </P
></DIV
></DIV
><DIV
CLASS="REFSECT1"
><H2
>   Description
  </H2
><P
>   CREATE  TYPE  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.
   <TT
CLASS="REPLACEABLE"
><I
>Typename</I
></TT
> is
   the name of the new type and must  be  unique  within  the
   types defined for this database.
  </P
><P
>   CREATE  TYPE  requires  the  registration of two functions
   (using create function) before defining the type.   The
   representation  of  a  new  base  type  is  determined  by
   <TT
CLASS="REPLACEABLE"
><I
>input_function</I
></TT
>, which
   converts the type's external  representation  to  an  internal
   representation  usable by the
   operators and functions defined for the type.   Naturally,
   <TT
CLASS="REPLACEABLE"
><I
>output_function</I
></TT
>
   performs the reverse transformation.  Both
   the input and output functions must be  declared  to  take
   one or two arguments of type "<TT
CLASS="LITERAL"
>opaque</TT
>".
  </P
><P
>   New  base  data  types  can be fixed length, in which case
   <TT
CLASS="REPLACEABLE"
><I
>internallength</I
></TT
> 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, "<TT
CLASS="LITERAL"
>text</TT
>".
   To  indicate that a type is variable-length, set
   <TT
CLASS="REPLACEABLE"
><I
>internallength</I
></TT
>
   to VARIABLE.
   The external representation is  similarly specified using the
   <TT
CLASS="REPLACEABLE"
><I
>externallength</I
></TT
>
   keyword.
  </P
><P
>   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
   <PRE
CLASS="PROGRAMLISTING"
>ELEMENT = int4</PRE
>
  </P
><P
>   To indicate the delimiter to be used  on  arrays  of  this
   type,  <TT
CLASS="REPLACEABLE"
><I
>delimiter</I
></TT
>
   can be
   set to a specific character.  The default delimiter is the comma
   ("<TT
CLASS="LITERAL"
>,</TT
>").
  </P
><P
>   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 DEFAULT keyword.

  </P
><P
>   The optional functions
   <TT
CLASS="REPLACEABLE"
><I
>send_function</I
></TT
> and
   <TT
CLASS="REPLACEABLE"
><I
>receive_function</I
></TT
>
   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.
  </P
><P
>   The  optional flag,  PASSEDBYVALUE, 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.
  </P
><P
>   For new base types, a user can define operators, functions
   and aggregates using the appropriate facilities  described
   in this section.
  </P
><DIV
CLASS="REFSECT2"
><H3
>Array Types</H3
><P
>       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.</P
></DIV
><DIV
CLASS="REFSECT2"
><H3
>Large Object Types</H3
><P
>    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

    Section 7, the large object interface.  The
    length of all large object types is always VARIABLE.
  </P
></DIV
></DIV
><DIV
CLASS="REFSECT1"
><H2
>Examples</H2
><P
>   This command creates the box data type and then uses the
   type in a class definition:
  </P
><PRE
CLASS="PROGRAMLISTING"
>       CREATE TYPE box (INTERNALLENGTH = 8,
            INPUT = my_procedure_1, OUTPUT = my_procedure_2)

       CREATE TABLE myboxes (id INT4, description box)
  </PRE
><P
>   This command creates a variable length array type with
   integer elements.
  </P
><PRE
CLASS="PROGRAMLISTING"
>       CREATE TYPE int4array
          (INPUT = array_in, OUTPUT = array_out,
           INTERNALLENGTH = VARIABLE, ELEMENT = int4)

       CREATE TABLE myarrays (id int4, numbers int4array)
  </PRE
><P
>   This command creates a large object type and uses it in
   a class definition.
  </P
><PRE
CLASS="PROGRAMLISTING"
>       CREATE TYPE bigobj
          (INPUT = lo_filein, OUTPUT = lo_fileout,
           INTERNALLENGTH = VARIABLE)

       CREATE TABLE big_objs (id int4, obj bigobj)
  </PRE
><DIV
CLASS="REFSECT2"
><H3
>Restrictions</H3
><P
>    Type  names  cannot  begin  with  the underscore character
    ("_") and can only be 15 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.
   </P
></DIV
><DIV
CLASS="REFSECT2"
><H3
>    Notes
   </H3
><P
>    Refer to <B
CLASS="COMMAND"
>DROP TYPE</B
> to remove an existing type.
   </P
><P
>    See also <B
CLASS="COMMAND"
>CREATE  FUNCTION</B
>,
  <B
CLASS="COMMAND"
>CREATE  OPERATOR</B
> and the chapter on Large Objects
in the <I
CLASS="CITETITLE"
>PostgreSQL Programmer's Guide</I
>.</P
></DIV
></DIV
><DIV
CLASS="REFSECT1"
><H2
>   Compatibility
  </H2
><P
>&#13;  </P
><DIV
CLASS="REFSECT2"
><H3
>    SQL3
   </H3
><P
>   <B
CLASS="COMMAND"
>CREATE TYPE</B
> is an <SPAN
CLASS="ACRONYM"
>SQL3</SPAN
> statement.
   </P
></DIV
></DIV
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="sql-createtrigger.html"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="user.html"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="sql-createuser.html"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>CREATE TRIGGER</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="sql-commands.html"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>CREATE USER</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>