1 ---------------------------------------------------------------------------
4 -- This file shows how to create a new user-defined type and how to
8 -- Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
9 -- Portions Copyright (c) 1994, Regents of the University of California
11 -- $Header: /cvsroot/pgsql/src/tutorial/complex.source,v 1.14 2002/08/22 00:01:51 tgl Exp $
13 ---------------------------------------------------------------------------
15 -----------------------------
16 -- Creating a new type:
17 -- a user-defined type must have an input and an output function. They
18 -- are user-defined C functions. We are going to create a new type
19 -- called 'complex' which represents complex numbers.
20 -----------------------------
22 -- Assume the user defined functions are in _OBJWD_/complex$DLSUFFIX
23 -- (we do not want to assume this is in the dynamic loader search path)
24 -- Look at $PWD/complex.c for the source.
26 -- the input function 'complex_in' takes a null-terminated string (the
27 -- textual representation of the type) and turns it into the internal
28 -- (in memory) representation. You will get a message telling you 'complex'
29 -- does not exist yet but that's okay.
31 CREATE FUNCTION complex_in(cstring)
36 -- the output function 'complex_out' takes the internal representation and
37 -- converts it into the textual representation.
39 CREATE FUNCTION complex_out(complex)
44 -- now, we can create the type. The internallength specifies the size of the
45 -- memory block required to hold the type (we need two 8-byte doubles).
55 -----------------------------
56 -- Using the new type:
57 -- user-defined types can be used like ordinary built-in types.
58 -----------------------------
60 -- eg. we can use it in a schema
62 CREATE TABLE test_complex (
67 -- data for user-defined types are just strings in the proper textual
70 INSERT INTO test_complex VALUES ('(1.0, 2.5)', '(4.2, 3.55 )');
71 INSERT INTO test_complex VALUES ('(33.0, 51.4)', '(100.42, 93.55)');
73 SELECT * FROM test_complex;
75 -----------------------------
76 -- Creating an operator for the new type:
77 -- Let's define an add operator for complex types. Since POSTGRES
78 -- supports function overloading, we'll use + as the add operator.
79 -- (Operator names can be reused with different numbers and types of
81 -----------------------------
83 -- first, define a function complex_add (also in complex.c)
84 CREATE FUNCTION complex_add(complex, complex)
89 -- we can now define the operator. We show a binary operator here but you
90 -- can also define unary operators by omitting either of leftarg or rightarg.
94 procedure = complex_add,
99 SELECT (a + b) AS c FROM test_complex;
101 -- Occasionally, you may find it useful to cast the string to the desired
102 -- type explicitly. :: denotes a type cast.
104 SELECT a + '(1.0,1.0)'::complex AS aa,
105 b + '(1.0,1.0)'::complex AS bb
109 -----------------------------
110 -- Creating aggregate functions
111 -- you can also define aggregate functions. The syntax is somewhat
112 -- cryptic but the idea is to express the aggregate in terms of state
113 -- transition functions.
114 -----------------------------
116 CREATE AGGREGATE complex_sum (
123 SELECT complex_sum(a) FROM test_complex;
126 -----------------------------
127 -- Interfacing New Types with Indexes:
128 -- We cannot define a secondary index (eg. a B-tree) over the new type
129 -- yet. We need to create all the required operators and support
130 -- functions, then we can make the operator class.
131 -----------------------------
133 -- first, define the required operators
134 CREATE FUNCTION complex_abs_lt(complex, complex) RETURNS bool
135 AS '_OBJWD_/complex' LANGUAGE 'c';
136 CREATE FUNCTION complex_abs_le(complex, complex) RETURNS bool
137 AS '_OBJWD_/complex' LANGUAGE 'c';
138 CREATE FUNCTION complex_abs_eq(complex, complex) RETURNS bool
139 AS '_OBJWD_/complex' LANGUAGE 'c';
140 CREATE FUNCTION complex_abs_ge(complex, complex) RETURNS bool
141 AS '_OBJWD_/complex' LANGUAGE 'c';
142 CREATE FUNCTION complex_abs_gt(complex, complex) RETURNS bool
143 AS '_OBJWD_/complex' LANGUAGE 'c';
146 leftarg = complex, rightarg = complex, procedure = complex_abs_lt,
147 restrict = scalarltsel, join = scalarltjoinsel
150 leftarg = complex, rightarg = complex, procedure = complex_abs_le,
151 restrict = scalarltsel, join = scalarltjoinsel
154 leftarg = complex, rightarg = complex, procedure = complex_abs_eq,
155 restrict = eqsel, join = eqjoinsel
158 leftarg = complex, rightarg = complex, procedure = complex_abs_ge,
159 restrict = scalargtsel, join = scalargtjoinsel
162 leftarg = complex, rightarg = complex, procedure = complex_abs_gt,
163 restrict = scalargtsel, join = scalargtjoinsel
166 -- create the support function too
167 CREATE FUNCTION complex_abs_cmp(complex, complex) RETURNS int4
168 AS '_OBJWD_/complex' LANGUAGE 'c';
170 -- now we can make the operator class
171 CREATE OPERATOR CLASS complex_abs_ops
172 DEFAULT FOR TYPE complex USING btree AS
178 FUNCTION 1 complex_abs_cmp(complex, complex);
181 -- now, we can define a btree index on complex types. First, let's populate
182 -- the table. Note that postgres needs many more tuples to start using the
183 -- btree index during selects.
184 INSERT INTO test_complex VALUES ('(56.0,-22.5)', '(-43.2,-0.07)');
185 INSERT INTO test_complex VALUES ('(-91.9,33.6)', '(8.6,3.0)');
187 CREATE INDEX test_cplx_ind ON test_complex
188 USING btree(a complex_abs_ops);
190 SELECT * from test_complex where a = '(56.0,-22.5)';
191 SELECT * from test_complex where a < '(56.0,-22.5)';
192 SELECT * from test_complex where a > '(56.0,-22.5)';
195 -- clean up the example
196 DROP TABLE test_complex;
197 DROP TYPE complex CASCADE;