create_type.7

来自「PostgreSQL 8.2中增加了很多企业用户所需要的功能和性能上的提高,其开」· 7 代码 · 共 415 行 · 第 1/2 页

.SS "ARRAY TYPES"
.PP
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.
.PP
You might reasonably ask why there is an \fBELEMENT\fR
option, if the system makes the correct array type automatically.
The only case where it's useful to use \fBELEMENT\fR is when you are
making a fixed-length type that happens to be internally an array of a number of
identical things, and you want to allow these 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 \fBname\fR
allows its constituent \fBchar\fR elements to be accessed this way.
A 2-D \fBpoint\fR type could allow its two component numbers 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 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.
.SH "PARAMETERS"
.TP
\fB\fIname\fB\fR
The name (optionally schema-qualified) of a type to be created.
.TP
\fB\fIattribute_name\fB\fR
The name of an attribute (column) for the composite type.
.TP
\fB\fIdata_type\fB\fR
The name of an existing data type to become a column of the
composite type.
.TP
\fB\fIinput_function\fB\fR
The name of a function that converts data from the type's
external textual form to its internal form.
.TP
\fB\fIoutput_function\fB\fR
The name of a function that converts data from the type's
internal form to its external textual form.
.TP
\fB\fIreceive_function\fB\fR
The name of a function that converts data from the type's
external binary form to its internal form.
.TP
\fB\fIsend_function\fB\fR
The name of a function that converts data from the type's
internal form to its external binary form.
.TP
\fB\fIanalyze_function\fB\fR
The name of a function that performs statistical analysis for the
data type.
.TP
\fB\fIinternallength\fB\fR
A numeric constant that specifies the length in bytes of the new
type's internal representation. The default assumption is that
it is variable-length.
.TP
\fB\fIalignment\fB\fR
The storage alignment requirement of the data type. If specified,
it must be char, int2,
int4, or double; the
default is int4.
.TP
\fB\fIstorage\fB\fR
The storage strategy for the data type. If specified, must be
plain, external,
extended, or main; the
default is plain.
.TP
\fB\fIdefault\fB\fR
The default value for the data type. If this is omitted, the
default is null.
.TP
\fB\fIelement\fB\fR
The type being created is an array; this specifies the type of
the array elements.
.TP
\fB\fIdelimiter\fB\fR
The delimiter character to be used between values in arrays made
of this type.
.SH "NOTES"
.PP
User-defined type names cannot begin with the underscore character
(_) and can only be 62 characters
long (or in general \fBNAMEDATALEN\fR - 2, rather than
the \fBNAMEDATALEN\fR - 1 characters allowed for other
names). Type names beginning with underscore are reserved for
internally-created array type names.
.PP
Because there are no restrictions on use of a data type once it's been
created, creating a base type is tantamount to granting public execute
permission on the functions mentioned in the type definition. (The creator
of the type is therefore required to own these functions.) This is usually
not an issue for the sorts of functions that are useful in a type
definition. But you might want to think twice before designing a type
in a way that would require ``secret'' information to be used
while converting it to or from external form.
.PP
Before PostgreSQL version 8.2, the syntax
CREATE TYPE \fIname\fR did not exist.
The way to create a new base type was to create its input function first.
In this approach, PostgreSQL will first see
the name of the new data type as the return type of the input function.
The shell type is implicitly created in this situation, and then it
can be referenced in the definitions of the remaining I/O functions.
This approach still works, but is deprecated and may be disallowed in
some future release. Also, to avoid accidentally cluttering
the catalogs with shell types as a result of simple typos in function
definitions, a shell type will only be made this way when the input
function is written in C.
.PP
In PostgreSQL versions before 7.3, it
was customary to avoid creating a shell type at all, by replacing the
functions' forward references to the type name with the placeholder
pseudotype \fBopaque\fR. The \fBcstring\fR arguments and
results also had to be declared as \fBopaque\fR before 7.3. To
support loading of old dump files, \fBCREATE TYPE\fR will
accept I/O functions declared using \fBopaque\fR, but it will issue
a notice and change the function declarations to use the correct
types.
.SH "EXAMPLES"
.PP
This example creates a composite type and uses it in
a function definition:
.sp
.nf
CREATE TYPE compfoo AS (f1 int, f2 text);

CREATE FUNCTION getfoo() RETURNS SETOF compfoo AS $$
    SELECT fooid, fooname FROM foo
$$ LANGUAGE SQL;
.sp
.fi
.PP
This example creates the base data type \fBbox\fR and then uses the
type in a table definition:
.sp
.nf
CREATE TYPE box;

CREATE FUNCTION my_box_in_function(cstring) RETURNS box AS ... ;
CREATE FUNCTION my_box_out_function(box) RETURNS cstring AS ... ;

CREATE TYPE box (
    INTERNALLENGTH = 16,
    INPUT = my_box_in_function,
    OUTPUT = my_box_out_function
);

CREATE TABLE myboxes (
    id integer,
    description box
);
.sp
.fi
.PP
If the internal structure of \fBbox\fR were an array of four
\fBfloat4\fR elements, we might instead use
.sp
.nf
CREATE TYPE box (
    INTERNALLENGTH = 16,
    INPUT = my_box_in_function,
    OUTPUT = my_box_out_function,
    ELEMENT = float4
);
.sp
.fi
which would allow a box value's component numbers to be accessed
by subscripting. Otherwise the type behaves the same as before.
.PP
This example creates a large object type and uses it in
a table definition:
.sp
.nf
CREATE TYPE bigobj (
    INPUT = lo_filein, OUTPUT = lo_fileout,
    INTERNALLENGTH = VARIABLE
);
CREATE TABLE big_objs (
    id integer,
    obj bigobj
);
.sp
.fi
.PP
More examples, including suitable input and output functions, are
in in the documentation.
.SH "COMPATIBILITY"
.PP
This \fBCREATE TYPE\fR command is a
PostgreSQL extension. There is a
\fBCREATE TYPE\fR statement in the SQL standard
that is rather different in detail.
.SH "SEE ALSO"
CREATE FUNCTION [\fBcreate_function\fR(7)], DROP TYPE [\fBdrop_type\fR(l)], ALTER TYPE [\fBalter_type\fR(l)], CREATE DOMAIN [\fBcreate_domain\fR(l)]