Provided by: postgresql-client-8.0_8.0.7-2build1_i386 bug

NAME

       CREATE TYPE - define a new data type

SYNOPSIS

       CREATE TYPE name AS
           ( attribute_name data_type [, ... ] )

       CREATE TYPE name (
           INPUT = input_function,
           OUTPUT = output_function
           [ , RECEIVE = receive_function ]
           [ , SEND = send_function ]
           [ , ANALYZE = analyze_function ]
           [ , INTERNALLENGTH = { internallength | VARIABLE } ]
           [ , PASSEDBYVALUE ]
           [ , ALIGNMENT = alignment ]
           [ , STORAGE = storage ]
           [ , DEFAULT = default ]
           [ , ELEMENT = element ]
           [ , DELIMITER = delimiter ]
       )

DESCRIPTION

       CREATE  TYPE registers a new data type for use in the current database.
       The user who defines a type becomes its owner.

       If a schema name is given then the type is  created  in  the  specified
       schema.  Otherwise  it  is created in the 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 data types, the type name
       must also be distinct from the name of any existing table in  the  same
       schema.)

   COMPOSITE TYPES
       The  first form of CREATE TYPE creates a composite type.  The composite
       type is specified by a list of attribute names and data types.  This is
       essentially  the same as the row type of a table, but using CREATE TYPE
       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 argument
       or return type of a function.

   BASE TYPES
       The second form of CREATE TYPE creates a new base type  (scalar  type).
       The  parameters  may  appear  in  any  order, not only that illustrated
       above, and most are optional. You must register two or  more  functions
       (using CREATE FUNCTION) before defining the type. The support functions
       input_function and output_function are required,  while  the  functions
       receive_function,  send_function  and  analyze_function  are  optional.
       Generally these functions have to be coded in C  or  another  low-level
       language.

       The  input_function converts the type’s external textual representation
       to the internal representation used  by  the  operators  and  functions
       defined   for   the   type.    output_function   performs  the  reverse
       transformation. The input  function  may  be  declared  as  taking  one
       argument  of  type  cstring,  or  as  taking  three  arguments of types
       cstring, oid, integer.  The first argument is the input  text  as  a  C
       string,  the  second argument is the element type’s OID in case this is
       an array type (or the type’s own OID for a  composite  type),  and  the
       third  is  the  typmod  of the destination column, if known (-1 will be
       passed if not).  The input function should return a value of  the  data
       type  itself.   The  output  function  may  be  declared  as taking one
       argument of the new data type, or as taking two arguments of which  the
       second  is  type  oid.   The second argument is again the array element
       type OID for array types or the type  OID  for  composite  types.   The
       output function should return type cstring.

       The  optional  receive_function  converts  the  type’s  external binary
       representation to the internal representation. If this function is  not
       supplied,  the  type  cannot  participate  in  binary input. The binary
       representation should be chosen to be  cheap  to  convert  to  internal
       form,  while  being  reasonably  portable.  (For  example, the standard
       integer data types use  network  byte  order  as  the  external  binary
       representation,  while  the internal representation is in the machine’s
       native byte  order.)  The  receive  function  should  perform  adequate
       checking  to  ensure that the value is valid.  The receive function may
       be declared as taking one argument of type internal, or  two  arguments
       of  types  internal  and  oid.  It must return a value of the data type
       itself.  (The first argument  is  a  pointer  to  a  StringInfo  buffer
       holding  the  received byte string; the optional second argument is the
       element type OID in case this is an array type, or the type’s  own  OID
       for  a  composite type.) Similarly, the optional send_function converts
       from the internal representation to the external binary representation.
       If this function is not supplied, the type cannot participate in binary
       output. The send function may be declared as taking one argument of the
       new  data  type, or as taking two arguments of which the second is type
       oid.  The second argument is again the array element type OID for array
       types  or  the  type  OID  for composite types.  The send function must
       return type bytea.

       You should at  this  point  be  wondering  how  the  input  and  output
       functions can be declared to have results or arguments 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 (and the binary I/O functions if wanted),  and  finally
       the data type.  PostgreSQL will first see the name of the new data type
       as the return type of the input function. It will  create  a  ‘‘shell’’
       type,  which  is  simply a placeholder entry in the system catalog, and
       link the input function definition to the  shell  type.  Similarly  the
       other  functions  will  be  linked  to the (now already existing) shell
       type. Finally, CREATE TYPE replaces the shell  entry  with  a  complete
       type definition, and the new type can be used.

       The   optional   analyze_function   performs  type-specific  statistics
       collection for columns of the data  type.   By  default,  ANALYZE  will
       attempt  to  gather  statistics using the type’s ‘‘equals’’ and ‘‘less-
       than’’ operators, if there is a default b-tree operator class  for  the
       type. For non-scalar types this behavior is likely to be unsuitable, so
       it can be overridden by specifying  a  custom  analysis  function.  The
       analysis  function  must  be declared to take a single argument of type
       internal, and return a boolean result. The detailed  API  for  analysis
       functions appears in src/include/commands/vacuum.h.

       While  the  details  of the new type’s internal representation are only
       known to the I/O functions and other functions you create to work  with
       the  type,  there are several properties of the internal representation
       that  must  be  declared  to  PostgreSQL.    Foremost   of   these   is
       internallength.   Base  data  types  can be fixed-length, in which case
       internallength is a positive integer, or variable length, indicated  by
       setting internallength to VARIABLE. (Internally, this is represented by
       setting typlen to -1.) The internal  representation  of  all  variable-
       length  types  must start with a 4-byte integer giving the total length
       of this value of the type.

       The optional flag PASSEDBYVALUE indicates that values of this data type
       are  passed  by  value,  rather  than by reference. You may not pass by
       value types whose internal representation is larger than  the  size  of
       the Datum type (4 bytes on most machines, 8 bytes on a few).

       The  alignment  parameter  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  int4  as
       their first component.

       The  storage  parameter  allows  selection  of  storage  strategies for
       variable-length data types. (Only plain  is  allowed  for  fixed-length
       types.) plain specifies that data of the type will always be stored in-
       line and not compressed.  extended specifies that 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.  external allows the value to be
       moved  out  of  the main table, but the system will not try to compress
       it.  main allows compression, but discourages moving the value  out  of
       the  main  table.  (Data  items with this storage strategy 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 extended
       and external items.)

       A default value may be specified, in case a user wants columns  of  the
       data  type  to default to something other than the null value.  Specify
       the default with  the  DEFAULT  key  word.   (Such  a  default  may  be
       overridden  by  an  explicit  DEFAULT  clause  attached to a particular
       column.)

       To indicate that a type is an array, specify  the  type  of  the  array
       elements using the ELEMENT key word. For example, to define an array of
       4-byte integers (int4), specify ELEMENT  =  int4.  More  details  about
       array types appear below.

       To  indicate  the  delimiter  to be used between values in the external
       representation of arrays of this  type,  delimiter  can  be  set  to  a
       specific  character.  The default delimiter is the comma (,). Note that
       the delimiter is associated with the array element type, not the  array
       type itself.

   ARRAY TYPES
       Whenever   a   user-defined  base  data  type  is  created,  PostgreSQL
       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  foo[]  into  requests  for type _foo.  The implicitly-created
       array type is variable length and uses the built-in  input  and  output
       functions array_in and array_out.

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

PARAMETERS

       name   The name (optionally schema-qualified) of a type to be  created.

       attribute_name
              The name of an attribute (column) for the composite type.

       data_type
              The  name  of  an  existing  data type to become a column of the
              composite type.

       input_function
              The name of a  function  that  converts  data  from  the  type’s
              external textual form to its internal form.

       output_function
              The  name  of  a  function  that  converts  data from the type’s
              internal form to its external textual form.

       receive_function
              The name of a  function  that  converts  data  from  the  type’s
              external binary form to its internal form.

       send_function
              The  name  of  a  function  that  converts  data from the type’s
              internal form to its external binary form.

       analyze_function
              The name of a function that performs  statistical  analysis  for
              the data type.

       internallength
              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.

       alignment
              The   storage   alignment  requirement  of  the  data  type.  If
              specified, it must be char, int2, int4, or double;  the  default
              is int4.

       storage
              The  storage  strategy  for the data type. If specified, must be
              plain, external, extended, or main; the default is plain.

       default
              The default value for the data type. If  this  is  omitted,  the
              default is null.

       element
              The  type  being created is an array; this specifies the type of
              the array elements.

       delimiter
              The delimiter character to be used between values in arrays made
              of this type.

NOTES

       User-defined  type names cannot begin with the underscore character (_)
       and can only be 62 characters long (or  in  general  NAMEDATALEN  -  2,
       rather  than  the  NAMEDATALEN - 1 characters allowed for other names).
       Type names beginning  with  underscore  are  reserved  for  internally-
       created array type names.

       In PostgreSQL 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 opaque. The cstring arguments and
       results also had to be  declared  as  opaque  before  7.3.  To  support
       loading  of  old dump files, CREATE TYPE will accept functions declared
       using opaque, but it will issue a  notice  and  change  the  function’s
       declaration to use the correct types.

EXAMPLES

       This  example  creates  a  composite  type  and  uses  it in a function
       definition:

       CREATE TYPE compfoo AS (f1 int, f2 text);

       CREATE FUNCTION getfoo() RETURNS SETOF compfoo AS $$
           SELECT fooid, fooname FROM foo
       $$ LANGUAGE SQL;

       This example creates the base data type box and then uses the type in a
       table definition:

       CREATE TYPE box (
           INTERNALLENGTH = 16,
           INPUT = my_box_in_function,
           OUTPUT = my_box_out_function
       );

       CREATE TABLE myboxes (
           id integer,
           description box
       );

       If the internal structure of box were an array of four float4 elements,
       we might instead use

       CREATE TYPE box (
           INTERNALLENGTH = 16,
           INPUT = my_box_in_function,
           OUTPUT = my_box_out_function,
           ELEMENT = float4
       );

       which would allow a box value’s component numbers  to  be  accessed  by
       subscripting. Otherwise the type behaves the same as before.

       This  example  creates  a  large  object  type  and  uses it in a table
       definition:

       CREATE TYPE bigobj (
           INPUT = lo_filein, OUTPUT = lo_fileout,
           INTERNALLENGTH = VARIABLE
       );
       CREATE TABLE big_objs (
           id integer,
           obj bigobj
       );

       More examples, including suitable input and output  functions,  are  in
       the documentation.

COMPATIBILITY

       This  CREATE  TYPE command is a PostgreSQL extension. There is a CREATE
       TYPE statement in SQL:1999  and  later  that  is  rather  different  in
       detail.

SEE ALSO

       CREATE  FUNCTION  [create_function(7)], DROP TYPE [drop_type(l)], ALTER
       TYPE [alter_type(l)]