CREATE TYPE

Practical PostgreSQL
Prev		Next

CREATE TYPE

Name

CREATE TYPE -- Defines a new data type for use in the database.

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

Prev	Home	Next
CREATE TRIGGER	Up	CREATE USER