1
---------------------------------------------------------------------------
4
-- This file shows how to create a new user-defined type and how to
8
-- Portions Copyright (c) 1996-2005, PostgreSQL Global Development Group
9
-- Portions Copyright (c) 1994, Regents of the University of California
11
-- $PostgreSQL: pgsql/src/tutorial/complex.source,v 1.19 2004-12-31 22:04:05 pgsql Exp $
13
---------------------------------------------------------------------------
15
-----------------------------
16
-- Creating a new type:
17
-- We are going to create a new type called 'complex' which represents
19
-- A user-defined type must have an input and an output function, and
20
-- optionally can have binary input and output functions. All of these
21
-- are usually user-defined C functions.
22
-----------------------------
24
-- Assume the user defined functions are in _OBJWD_/complex$DLSUFFIX
25
-- (we do not want to assume this is in the dynamic loader search path).
26
-- Look at $PWD/complex.c for the source. Note that we declare all of
27
-- them as STRICT, so we do not need to cope with NULL inputs in the
28
-- C code. We also mark them IMMUTABLE, since they always return the
29
-- same outputs given the same inputs.
31
-- the input function 'complex_in' takes a null-terminated string (the
32
-- textual representation of the type) and turns it into the internal
33
-- (in memory) representation. You will get a message telling you 'complex'
34
-- does not exist yet but that's okay.
36
CREATE FUNCTION complex_in(cstring)
39
LANGUAGE C IMMUTABLE STRICT;
41
-- the output function 'complex_out' takes the internal representation and
42
-- converts it into the textual representation.
44
CREATE FUNCTION complex_out(complex)
47
LANGUAGE C IMMUTABLE STRICT;
49
-- the binary input function 'complex_recv' takes a StringInfo buffer
50
-- and turns its contents into the internal representation.
52
CREATE FUNCTION complex_recv(internal)
55
LANGUAGE C IMMUTABLE STRICT;
57
-- the binary output function 'complex_send' takes the internal representation
58
-- and converts it into a (hopefully) platform-independent bytea string.
60
CREATE FUNCTION complex_send(complex)
63
LANGUAGE C IMMUTABLE STRICT;
66
-- now, we can create the type. The internallength specifies the size of the
67
-- memory block required to hold the type (we need two 8-byte doubles).
73
receive = complex_recv,
79
-----------------------------
80
-- Using the new type:
81
-- user-defined types can be used like ordinary built-in types.
82
-----------------------------
84
-- eg. we can use it in a table
86
CREATE TABLE test_complex (
91
-- data for user-defined types are just strings in the proper textual
94
INSERT INTO test_complex VALUES ('(1.0, 2.5)', '(4.2, 3.55 )');
95
INSERT INTO test_complex VALUES ('(33.0, 51.4)', '(100.42, 93.55)');
97
SELECT * FROM test_complex;
99
-----------------------------
100
-- Creating an operator for the new type:
101
-- Let's define an add operator for complex types. Since POSTGRES
102
-- supports function overloading, we'll use + as the add operator.
103
-- (Operator names can be reused with different numbers and types of
105
-----------------------------
107
-- first, define a function complex_add (also in complex.c)
108
CREATE FUNCTION complex_add(complex, complex)
111
LANGUAGE C IMMUTABLE STRICT;
113
-- we can now define the operator. We show a binary operator here but you
114
-- can also define unary operators by omitting either of leftarg or rightarg.
118
procedure = complex_add,
123
SELECT (a + b) AS c FROM test_complex;
125
-- Occasionally, you may find it useful to cast the string to the desired
126
-- type explicitly. :: denotes a type cast.
128
SELECT a + '(1.0,1.0)'::complex AS aa,
129
b + '(1.0,1.0)'::complex AS bb
133
-----------------------------
134
-- Creating aggregate functions
135
-- you can also define aggregate functions. The syntax is somewhat
136
-- cryptic but the idea is to express the aggregate in terms of state
137
-- transition functions.
138
-----------------------------
140
CREATE AGGREGATE complex_sum (
147
SELECT complex_sum(a) FROM test_complex;
150
-----------------------------
151
-- Interfacing New Types with Indexes:
152
-- We cannot define a secondary index (eg. a B-tree) over the new type
153
-- yet. We need to create all the required operators and support
154
-- functions, then we can make the operator class.
155
-----------------------------
157
-- first, define the required operators
158
CREATE FUNCTION complex_abs_lt(complex, complex) RETURNS bool
159
AS '_OBJWD_/complex' LANGUAGE C IMMUTABLE STRICT;
160
CREATE FUNCTION complex_abs_le(complex, complex) RETURNS bool
161
AS '_OBJWD_/complex' LANGUAGE C IMMUTABLE STRICT;
162
CREATE FUNCTION complex_abs_eq(complex, complex) RETURNS bool
163
AS '_OBJWD_/complex' LANGUAGE C IMMUTABLE STRICT;
164
CREATE FUNCTION complex_abs_ge(complex, complex) RETURNS bool
165
AS '_OBJWD_/complex' LANGUAGE C IMMUTABLE STRICT;
166
CREATE FUNCTION complex_abs_gt(complex, complex) RETURNS bool
167
AS '_OBJWD_/complex' LANGUAGE C IMMUTABLE STRICT;
170
leftarg = complex, rightarg = complex, procedure = complex_abs_lt,
171
commutator = > , negator = >= ,
172
restrict = scalarltsel, join = scalarltjoinsel
175
leftarg = complex, rightarg = complex, procedure = complex_abs_le,
176
commutator = >= , negator = > ,
177
restrict = scalarltsel, join = scalarltjoinsel
180
leftarg = complex, rightarg = complex, procedure = complex_abs_eq,
182
-- leave out negator since we didn't create <> operator
184
restrict = eqsel, join = eqjoinsel
187
leftarg = complex, rightarg = complex, procedure = complex_abs_ge,
188
commutator = <= , negator = < ,
189
restrict = scalargtsel, join = scalargtjoinsel
192
leftarg = complex, rightarg = complex, procedure = complex_abs_gt,
193
commutator = < , negator = <= ,
194
restrict = scalargtsel, join = scalargtjoinsel
197
-- create the support function too
198
CREATE FUNCTION complex_abs_cmp(complex, complex) RETURNS int4
199
AS '_OBJWD_/complex' LANGUAGE C IMMUTABLE STRICT;
201
-- now we can make the operator class
202
CREATE OPERATOR CLASS complex_abs_ops
203
DEFAULT FOR TYPE complex USING btree AS
209
FUNCTION 1 complex_abs_cmp(complex, complex);
212
-- now, we can define a btree index on complex types. First, let's populate
213
-- the table. Note that postgres needs many more tuples to start using the
214
-- btree index during selects.
215
INSERT INTO test_complex VALUES ('(56.0,-22.5)', '(-43.2,-0.07)');
216
INSERT INTO test_complex VALUES ('(-91.9,33.6)', '(8.6,3.0)');
218
CREATE INDEX test_cplx_ind ON test_complex
219
USING btree(a complex_abs_ops);
221
SELECT * from test_complex where a = '(56.0,-22.5)';
222
SELECT * from test_complex where a < '(56.0,-22.5)';
223
SELECT * from test_complex where a > '(56.0,-22.5)';
226
-- clean up the example
227
DROP TABLE test_complex;
228
DROP TYPE complex CASCADE;