CREATE 
TYPE(7)							   SQL Commands 						    CREATE TYPE(7)

NAME

       CREATE TYPE - define a new data type

SYNOPSIS

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

       CREATE TYPE typename AS
	   ( column_name data_type [, ... ] )

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

       internallength
	      A literal value, which specifies the internal 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 to be used between values in arrays made of this type.

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

       alignment
	      Storage alignment requirement of the data type. If specified, must be char, int2, 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.

       column_name
	      The name of a column of the composite type.

       data_type
	      The name of an existing data type.

   OUTPUTS
       CREATE TYPE
	      Message returned if the type is successfully created.

DESCRIPTION

       CREATE  TYPE  allows  the  user	to register a new data type with PostgreSQL for use in the current data base.  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 one  at  the
       front  of  the search path; see 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, type names also must not conflict with table names in the same schema.)

   BASE TYPES
       The first form of CREATE TYPE creates a new base type (scalar type). It requires the registration of two functions (using CREATE  FUNCTION)
       before  defining the type. The representation 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.  The  input function may be declared as taking one argument of type cstring, or as taking three arguments of types
       cstring, OID, int4.  (The first argument is the input text as a C string, the second argument is the element type in case this is an  array
       type,  and  the	third  is  the typmod of the destination column, if known.)  It 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 for array types.)  The output function should return type cstring.

       You  should  at	this point be wondering how the input and output functions can be declared to have results or inputs 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,  then	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 pg_type, and link the input function definition to the  shell  type.  Simi-
       larly  the  output  function  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.

	      Note: In PostgreSQL versions before 7.3, it was customary to avoid creating a shell type by replacing the functions' forward  refer-
	      ences  to  the  type  name with the placeholder pseudo-type OPAQUE. The cstring inputs 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.

       New  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 an integer giving the total length of this value of the type.

       To  indicate  that a type is an array, specify the type of the array elements using the ELEMENT keyword. 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  spe-
       cific character. The default delimiter is the comma (','). Note that the delimiter is associated with the array element type, not the array
       type itself.

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

       The  optional  flag, PASSEDBYVALUE, indicates that values of this data type are passed by value rather than by reference. Note that you may
       not pass by value types whose internal representation is longer than the width of the Datum type (four bytes on most machines, eight  bytes
       on a few).

       The  alignment  keyword 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  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.)

   COMPOSITE TYPES
       The second form of CREATE TYPE creates a composite type.  The composite type is specified by a list of column 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 return type of a function.

   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 is there 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 N identical things, and you
       want  to allow the N 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 chars to be accessed this way.  A 2-D point  type  could	allow  its  two  component
       floats 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 N 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.

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-cre-
       ated array type names.

EXAMPLES

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

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

       If box's internal structure were an array of four float4s, we might instead say

       CREATE TYPE box (INTERNALLENGTH = 16,
	   INPUT = my_procedure_1, OUTPUT = my_procedure_2,
	   ELEMENT = float4);

       which would allow a box value's component floats 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 int4, obj bigobj);

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

       CREATE TYPE compfoo AS (f1 int, f2 text);
       CREATE FUNCTION getfoo() RETURNS SETOF compfoo AS 'SELECT fooid, fooname FROM foo' LANGUAGE SQL;

COMPATIBILITY

       This CREATE TYPE command is a PostgreSQL extension. There is a CREATE TYPE statement in SQL99 that is rather different in detail.

SEE ALSO

       CREATE FUNCTION [create_function(7)], DROP TYPE [drop_type(l)], PostgreSQL Programmer's Guide

SQL - Language Statements					    2002-11-22							    CREATE TYPE(7)