CREATE TYPE - Defines a new base data type


SYNOPSIS

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

   INPUTS
       typename
              The name of a type to be created.

       internallength
              A  literal  value,  which  specifies  the  internal
              length of the new type.

       externallength
              A literal value, which specifies the external (dis­
              played) length of the new type.

       input_function
              The name of a function, created by CREATE FUNCTION,
              which converts data from its external form  to  the
              type's internal form.

       output_function
              The name of a function, created by CREATE FUNCTION,
              which converts data from its  internal  form  to  a
              form suitable for display.

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

       delimiter
              The delimiter character for the array elements.

       default
              The default value for the data type.  Usually  this
              is omitted, so that the default is NULL.

       send_function
              The name of a function, created by CREATE FUNCTION,
              which converts data of this type into a form  suit­
              able for transmission to another machine.

              The name of a function, created by CREATE FUNCTION,
              which converts data of this type from a form  suit­
              able  for  transmission  from  another  machine  to
              internal form.

       alignment
              Storage alignment requirement of the data type.  If
              specified,  must be 'int4' or 'double'; the default
              is 'int4'.

       storage
              Storage technique for the data type. If  specified,
              must be 'plain', 'external', 'extended', or 'main';
              the default is 'plain'.

   OUTPUTS
       CREATE Message returned if the type is  successfully  cre­
              ated.


DESCRIPTION

       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.  typename is
       the name of the new type and must  be  unique  within  the
       types defined for this database.

       CREATE  TYPE  requires  the  registration of two functions
       (using create function) before defining the type. The rep­
       resentation   of   a   new  base  type  is  determined  by
       input_function, which converts the type's external  repre­
       sentation  to  an  internal  representation  usable by the
       operators and functions defined for the  type.  Naturally,
       output_function  performs the reverse transformation. Both
       the input and output functions must be  declared  to  take
       one or two arguments of type "opaque".

       New  base  data  types  can be fixed length, in which case
       internallength 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,  "text".
       To  indicate that a type is variable length, set internal­
       length to VARIABLE.  The external representation is  simi­
       larly specified using the externallength keyword.

       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

       ELEMENT = int4

       type,  delimiter  can  be set to a specific character. The
       default delimiter is the comma (",").

       A default value is optionally available  in  case  a  user
       wants  some  specific  bit  pattern to mean "data not pre­
       sent."  Specify the  default  with  the  DEFAULT  keyword.
       [Comment:  How  does the user specify that bit pattern and
       associate it with the fact that the data is not present>]

       The optional arguments send_function and  receive_function
       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.

       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.

       The storage keyword allows selection of storage strategies
       for variable-length data types (only plain is allowed  for
       fixed-length  types).   plain  disables TOAST for the data
       type: it will always be stored in-line and not compressed.
       extended  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.
       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 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  extended  and  external
       items.)

       For new base types, a user can define operators, functions
       and aggregates using the appropriate facilities  described
       in this section.

   ARRAY TYPES
       Two   generalized   built-in   functions,   array_in   and
       array_out, exist for  quick  creation  of  variable-length

       existing Postgres type.


EXAMPLES

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

       CREATE TYPE box (INTERNALLENGTH = 8,
           INPUT = my_procedure_1, OUTPUT = my_procedure_2);
       CREATE TABLE myboxes (id INT4, description box);

       This  command  creates  a  variable length array type with
       integer elements:

       CREATE TYPE int4array (INPUT = array_in, OUTPUT = array_out,
           INTERNALLENGTH = VARIABLE, ELEMENT = int4);
       CREATE TABLE myarrays (id int4, numbers int4array);

       This command 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 int4, obj bigobj);

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

       Refer to DROP TYPE to remove an existing type.

       See  also CREATE FUNCTION, CREATE OPERATOR and the chapter
       on Large Objects in the PostgreSQL Programmer's Guide.


COMPATIBILITY

   SQL3
       CREATE TYPE is an SQL3 statement.


Man(1) output converted with man2html