1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
|
<!-- manual page source format generated by PolyglotMan v3.0.4, -->
<!-- available via anonymous ftp from ftp.cs.berkeley.edu:/ucb/people/phelps/tcltk/rman.tar.Z -->
<HTML>
<HEAD>
<TITLE>"CREATE(TYPE") manual page</TITLE>
</HEAD>
<BODY>
<A HREF="sql.html">SQL Reference Contents</A>
<H2><A NAME="sect0" HREF="#toc0">NAME </A></H2>
create type - define a new base data type
<H2><A NAME="sect1" HREF="#toc1">SYNOPSIS </A></H2>
<B>create type
</B> typename <B>( </B><B>internallength </B> = (number | <B>variable </B>), <BR>
<tt> </tt><tt> </tt>[ <B>externallength </B>
= (number | <B>variable </B>)<B>, </B> ] <BR>
<tt> </tt><tt> </tt><B>input </B> = input_function, <BR>
<tt> </tt><tt> </tt><B>output </B> = output_function
<BR>
<tt> </tt><tt> </tt>[<B>, </B> <B>element </B> = typename] <BR>
<tt> </tt><tt> </tt>[<B>, </B> <B>delimiter </B> = <character>] <BR>
<tt> </tt><tt> </tt>[<B>, </B> <B>default </B> =
"string" ] <BR>
<tt> </tt><tt> </tt>[<B>, </B> <B>send </B> = send_function ] <BR>
<tt> </tt><tt> </tt>[<B>, </B> <B>receive </B> = receive_function
] <BR>
<tt> </tt><tt> </tt>[<B>, </B> <B>passedbyvalue </B>]<B>) </B> <BR>
<H2><A NAME="sect2" HREF="#toc2">DESCRIPTION </A></H2>
<B>Create type</B> allows the user to
register a new user data type with Postgres for use in the current data
base. The user who defines a type becomes its owner. <I>Typename</I> is the name
of the new type and must be unique within the types defined for this database.
<P>
<B>Create type</B> requires the registration of two functions (using <I>create <A HREF="function.l.html">function</I>(l)</A>
)
before defining the type. The representation of a new base type is determined
by <I>input_function</I>, which converts the type's external representation to
an internal representation usable by the operators and functions defined
for the type. Naturally, <I>output_function</I> performs the reverse transformation.
Both the input and output functions must be declared to take one or two
arguments of type `opaque'. <P>
New base data types can be fixed length, in which
case <B>internallength</B> 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 <B>internallength</B>
to <I>variable</I>. The external representation is similarly specified using the
<I>externallength</I> keyword. <P>
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 <B>element</B> keyword. For example, to define an array of 4 byte integers
(`int4'), specify element = int4 <BR>
<P>
To indicate the delimiter to be used on
arrays of this type, <B>delimiter</B> can be set to a specific character. The
default delimiter is the comma (`,') character. <P>
A <B>default</B> value is optionally
available in case a user wants some specific bit pattern to mean `data
not present.' <P>
The optional functions <I>send_function</I> and <I>receive_function</I>
are used when the application program requesting Postgres services resides
on a different machine. In this case, the machine on which Postgres runs
may use a different format for the data type than used on the remote machine.
In this case it is appropriate to convert data items to a standard form
when <B>send</B>ing from the server to the client and converting from the standard
format to the machine specific format when the server <B>receive</B>s the data
from the client. If these functions are not specified, 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 DECstation, but many other types do. <P>
The optional
<B>passedbyvalue</B> flag indicates that operators and functions which use this
data type should be passed an argument by value rather than by reference.
Note that only types whose internal representation is at most four bytes
may be passed by value. <P>
For new base types, a user can define operators,
functions and aggregates using the appropriate facilities described in
this section.
<H2><A NAME="sect3" HREF="#toc3">ARRAY TYPES </A></H2>
Two generalized built-in functions, <B>array_in</B>
and <B>array_out,</B> exist for quick creation of variable-length array types.
These functions operate on arrays of any existing Postgres type.
<H2><A NAME="sect4" HREF="#toc4">LARGE
OBJECT TYPES </A></H2>
A `regular' Postgres type can only be 8192 bytes in length.
If you need a larger type you must create a Large Object type. The interface
for these types is discussed at length in Section 7, the large object
interface. The length of all large object types is always <I>variable,</I> meaning
the <B>internallength</B> for large objects is always -1.
<H2><A NAME="sect5" HREF="#toc5">EXAMPLES </A></H2>
-- <BR>
--This command
creates the box data type and then uses the <BR>
--type in a class definition
<BR>
-- <BR>
create type box (internallength = 8, <BR>
<tt> </tt><tt> </tt>input = my_procedure_1, output
= my_procedure_2) <BR>
<P>
create table MYBOXES (id = int4, description = box)
<BR>
-- <BR>
--This command creates a variable length array type with <BR>
--integer elements.
<BR>
-- <BR>
create type int4array <BR>
(input = array_in, output = array_out, <BR>
internallength = variable, element = int4) <BR>
<P>
create table MYARRAYS
(id = int4, numbers = int4array) <BR>
-- <BR>
--This command creates a large object
type and uses it in <BR>
--a class definition. <BR>
-- <BR>
create type bigobj <BR>
(input
= lo_filein, output = lo_fileout, <BR>
internallength = variable) <BR>
<P>
create
table BIG_OBJS (id = int4, obj = bigobj) <BR>
<H2><A NAME="sect6" HREF="#toc6">RESTRICTIONS </A></H2>
Type names cannot
begin with the underscore character (`_') and can only be 15 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.
<H2><A NAME="sect7" HREF="#toc7">SEE ALSO </A></H2>
create <A HREF="function.l.html">function(l)</A>
, create <A HREF="operator.l.html">operator(l)</A>
, drop <A HREF="type.l.html">type(l)</A>
,
<A HREF="large_objects.3.html">large_objects(3)</A>
. <P>
<HR><P>
<A NAME="toc"><B>Table of Contents</B></A><P>
<UL>
<LI><A NAME="toc0" HREF="#sect0">NAME</A></LI>
<LI><A NAME="toc1" HREF="#sect1">SYNOPSIS</A></LI>
<LI><A NAME="toc2" HREF="#sect2">DESCRIPTION</A></LI>
<LI><A NAME="toc3" HREF="#sect3">ARRAY TYPES</A></LI>
<LI><A NAME="toc4" HREF="#sect4">LARGE OBJECT TYPES</A></LI>
<LI><A NAME="toc5" HREF="#sect5">EXAMPLES</A></LI>
<LI><A NAME="toc6" HREF="#sect6">RESTRICTIONS</A></LI>
<LI><A NAME="toc7" HREF="#sect7">SEE ALSO</A></LI>
</UL>
</BODY></HTML>
|
