CREATE TYPE() SQL Commands CREATE TYPE()NAME
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 (displayed) length
of the new type.
input_function
The name of a function, created by CREATE FUNCTION, which con‐
verts data from its external form to the type's internal form.
output_function
The name of a function, created by CREATE FUNCTION, which con‐
verts data from its internal form to a form suitable for dis‐
play.
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 con‐
verts data of this type into a form suitable for transmission to
another machine.
receive_function
The name of a function, created by CREATE FUNCTION, which con‐
verts data of this type from a form suitable 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 created.
DESCRIPTION
CREATE TYPE allows the user to register a new user data type with Post‐
gres 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 representation of a new base
type is determined by input_function, which converts the type's exter‐
nal representation to an internal representation usable by the opera‐
tors and functions defined for the type. Naturally, output_function
performs the reverse transformation. Both the input and output func‐
tions 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 ele‐
ment 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
(",").
A default value is optionally available in case a user wants some spe‐
cific bit pattern to mean "data not present." Specify the default with
the DEFAULT keyword. [Comment: How does the user specify that bit pat‐
tern 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 dif‐
ferent 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 speci‐
fied, 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 DEC‐
station, but many other types do.
The optional flag, PASSEDBYVALUE, indicates that operators and func‐
tions 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 vari‐
able-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 mov‐
ing 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 prefer‐
entially over extended and external items.)
For new base types, a user can define operators, functions and aggre‐
gates 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.
EXAMPLES
This command creates the box data type and then uses the type in a ta‐
ble 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 ele‐
ments:
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 defini‐
tion:
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.
SQL - Language Statements 29 March 2001 CREATE TYPE()
