create_type

CREATE TYPE(SQL)                  Postgres95                  CREATE TYPE(SQL)



NAME
       create type — define a new base data type

SYNOPSIS
       create type typename (internallength = (number | variable),
            [ externallength = (number | variable), ]
            input = input_function,
            output = output_function
            [, element = typename]
            [, delimiter = <character>]
            [, default = "string" ]
            [, send = send_function ]
            [, receive = receive_function ]
            [, passedbyvalue])

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(l)) before defining the type.  The representation of a new
       base type is determined by input_function, which converts the type's
       external representation 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
       internallength to variable.  The external representation is similarly
       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

       To indicate the delimiter to be used on arrays of this type, delimiter
       can be set to a specific character.  The default delimiter is the comma
       (“,”) character.

       A default value is optionally available in case a user wants some
       specific bit pattern to mean “data not present.”

       The optional functions 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 different format for the data type than 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 passedbyvalue flag indicates that operators and functions
       which use this data type should be passed an argument by value rather
       than by reference.  Note that only types whose internal representation
       is at most four bytes may be passed by value.

       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 array types.  These functions operate
       on arrays of any existing Postgres type.

LARGE OBJECT TYPES
       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,
       meaning the internallength for large objects is always -1.

EXAMPLES
       --
       --This command creates the box data type and then uses the
       --type in a class 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 class definition.
       --
       create type bigobj
          (input = lo_filein, output = lo_fileout,
           internallength = variable)

       create table BIG_OBJS (id = int4, obj = bigobj)

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

SEE ALSO
       create function(l), create operator(l), drop type(l),
       introduction(large objects).



Postgres95                         11/05/95                   CREATE TYPE(SQL)