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 ] )Parameters
- typename
The name of the new type being created, which may be up to 30 characters in length. All type names must be unique within a database, and may not begin with an underscore (which is reserved for implicit array types).
- internallength
The internal length of the new type, in bytes.
- externallength
The optional external (displayed) length of the new type.
- input_function
The name of the new type's input function. You must have already defined the function using CREATE FUNCTION, and it must act to convert data of the type's external form into the type's internal form.
- output_function
The name of the new type's output function. This function must convert data of the type's internal form into its displayable form.
- element
The data type of individual array elements which this type addresses, if you intend to create an array type manually. The element must be fixed-length data type.
- delimiter
The value delimiter for the implicitly created array associated with the new type (typename[]).
- default
The default value for the new data type. If you do not specify a default, the default value for an unspecified column will fall back to either a table-level DEFAULT constraint or NULL.
- send_function
The name of the new type's send function. This function would convert data of the type into a form that can be transferred to another machine, but is not used by PostgreSQL as of 7.1.x, and can be omitted.
- receive_function
The name of the new type's receive function. This function would accept data of the form returned by send_function, and convert that into the type's internal form, but it is also not used by PostgreSQL as of 7.1.x, and can be omitted.
- PASSEDBYVALUE
The optional PASSEDBYVALUE keyword indicates that operators and functions that use this data type should be passed the argument by value, rather than by reference (the default). You may not use this option on types whose internal representation is more than four bytes in length.
- alignment
The storage alignment that this type will require. This must be either char, int2, int4, or double. If unspecified, int4 will be chosen by default.
- storage
The storage technique that will be used for the type. Set this to one of plain, external, extended, or main. If left unspecified, the storage type will default to plain.
Results
- CREATE
The message returned when a type is successfully created.
Description
Use the CREATE TYPE command to register a new, user-defined data type within the
current database. The PostgreSQL user that issues the command becomes the owner of the data type.
For a type to be created, it must use two user-defined functions (written in C). These functions are the input and
output functions of the data type. The input function converts the type's external representation into an internal
representation that can be used by the system objects associated with the type. The output function converts the internal
representation back to an external representation.
Both the input and output functions must take a single argument of the opaque type.
The output function must return a value of type opaque, while the input function should
return a value of the type you intend to create. Notice that this is done before the type is actually created.
You can set the type as either fixed or variable length. If you intend to create a fixed-length type, set
internallength to set its numeric length, in bytes. If you intend to create a variable-length
type, use the VARIABLE keyword instead of the internallength
parameter, and the length will be handled in the same way as for the text data type.
Specify the external length in the same way, using either a numeric value for externallength, or
the VARIABLE keyword.
When a new type is created, PostgreSQL automatically adds an implicit array type for the new data type. Internally,
this implicit array type is named _typename (with a leading underscore). Any reference to a data
type called typename[] will automatically be translated to the
internal array type (_typename).
If you wish to provide a delimiter character for the array type, use delimiter to do so.
This is the character used to separate array elements within array constants passed to PostgreSQL (e.g.,
{1,2,3}). This is also the character used to separate elements in the external display of values for this
array type. By default the delimiter is set to a comma.
If you choose to manually create an array data type, you may provide the PostgreSQL array_in and
array_out functions as the input and output function, respectively. You may then use the
ELEMENT keyword to specify the data type of the array elements.
To define a system-wide default value for insertion on a column of the new data type (which would ordinarily default to
NULL, in instances where a value is neither provided by a user, or by a DEFAULT
constraint), use the DEFAULT keyword. Note that, as of PostgreSQL 7.1.x, this must be the
internal representation of the default value.
The alignment value dictates the internal storage alignment of the new data type. Data types
created with a variable internal length must be either int4 or double.
The storage value determines the internal storage method. Data types with fixed internal
length can only be set to plain. Data types with variable internal length can be set to
plain, extended, external, or main.
The plain method causes data to be stored in an uncompressed, literal representation. This
representation is subject to a maximum length of 8 kilobytes. The extended method allows
values that go over this limit to be compressed, as well as to be stored outside of the physical location of the table if the
size of the value goes over the physical limit through PostgreSQL's TOAST extension (The
Oversized Attribute Storage Technique, coined by Tom Lane).
The external method is similar to the extended method, but does not
attempt to compress the value before using TOAST to store values over the physical limit of the table. The
main method is also similar to the extended method, in that
it supports compression and TOAST, but it prefers to be maintained physically within the main table unless there is
no other storage alternative.
Example
The following example demonstrates the creation of a new data type called zero, which
is a numeric data type always set to 0. First, the input and output functions are created.
Then, the type itself is created, referencing those functions, as follows.
booktown=# CREATE FUNCTION zero_out(opaque) RETURNS opaque
booktown-# AS '/usr/local/pgsql/lib/zero.so' LANGUAGE 'C';
CREATE
booktown=# CREATE FUNCTION zero_in(opaque) RETURNS zero
booktown-# AS '/usr/local/pgsql/lib/zero.so' LANGUAGE 'C';
NOTICE: ProcedureCreate: type 'zero' is not yet defined
CREATE
booktown=# CREATE TYPE zero (internallength = 16,
booktown(# input = zero_in, output = zero_out);
CREATE
