1 ---------------------------------------------------------------------------
4 -- This file shows how to create a new user-defined type and how to
8 -- Copyright (c) 1994, Regents of the University of California
10 -- $Id: complex.source,v 1.5 2000/01/22 23:50:30 tgl Exp $
12 ---------------------------------------------------------------------------
14 -----------------------------
15 -- Creating a new type:
16 -- a user-defined type must have an input and an output function. They
17 -- are user-defined C functions. We are going to create a new type
18 -- called 'complex' which represents complex numbers.
19 -----------------------------
21 -- Assume the user defined functions are in _OBJWD_/complex.so
22 -- Look at $PWD/complex.c for the source.
24 -- the input function 'complex_in' takes a null-terminated string (the
25 -- textual representation of the type) and turns it into the internal
26 -- (in memory) representation. You will get a message telling you 'complex'
27 -- does not exist yet but that's okay.
29 CREATE FUNCTION complex_in(opaque)
31 AS '_OBJWD_/complex.so'
34 -- the output function 'complex_out' takes the internal representation and
35 -- converts it into the textual representation.
37 CREATE FUNCTION complex_out(opaque)
39 AS '_OBJWD_/complex.so'
42 -- now, we can create the type. The internallength specifies the size of the
43 -- memory block required to hold the type (we need two 8-byte doubles).
52 -----------------------------
53 -- Using the new type:
54 -- user-defined types can be use like ordinary built-in types.
55 -----------------------------
57 -- eg. we can use it in a schema
59 CREATE TABLE test_complex (
64 -- data for user-defined type are just strings in the proper textual
67 INSERT INTO test_complex VALUES ('(1.0, 2.5)', '(4.2, 3.55 )');
68 INSERT INTO test_complex VALUES ('(33.0, 51.4)', '(100.42, 93.55)');
70 SELECT * FROM test_complex;
72 -----------------------------
73 -- Creating an operator for the new type:
74 -- Let's define an add operator for complex types. Since POSTGRES
75 -- supports function overloading, we'll use + as the add operator.
76 -- (Operators can be reused with different number and types of
78 -----------------------------
80 -- first, define a function complex_add (also in complex.c)
81 CREATE FUNCTION complex_add(complex, complex)
83 AS '_OBJWD_/complex.so'
86 -- we can now define the operator. We show a binary operator here but you
87 -- can also define unary operators by omitting either of leftarg or rightarg.
91 procedure = complex_add,
96 SELECT (a + b) AS c FROM test_complex;
98 -- Occasionally, you may find it useful to cast the string to the desired
99 -- type explicitly. :: denotes a type cast.
101 SELECT a + '(1.0,1.0)'::complex AS aa,
102 b + '(1.0,1.0)'::complex AS bb
106 -----------------------------
107 -- Creating aggregate functions
108 -- you can also define aggregate functions. The syntax is somewhat
109 -- cryptic but the idea is to express the aggregate in terms of state
110 -- transition functions.
111 -----------------------------
113 CREATE AGGREGATE complex_sum (
114 sfunc1 = complex_add,
120 SELECT complex_sum(a) FROM test_complex;
123 -------------------------------------------------------------------------------
124 -- ATTENTION! ATTENTION! ATTENTION! --
125 -- YOU MAY SKIP THE SECTION BELOW ON INTERFACING WITH INDICES. YOU DON'T --
126 -- NEED THE FOLLOWING IF YOU DON'T USE INDICES WITH NEW DATA TYPES. --
127 -------------------------------------------------------------------------------
129 SELECT 'READ ABOVE!' AS STOP;
131 -----------------------------
132 -- Interfacing New Types with Indices:
133 -- We cannot define a secondary index (eg. a B-tree) over the new type
134 -- yet. We need to modify a few system catalogs to show POSTGRES how
135 -- to use the new type. Unfortunately, there is no simple command to
136 -- do this. Please bear with me.
137 -----------------------------
139 -- first, define the required operators
140 CREATE FUNCTION complex_abs_lt(complex, complex) RETURNS bool
141 AS '_OBJWD_/complex.so' LANGUAGE 'c';
142 CREATE FUNCTION complex_abs_le(complex, complex) RETURNS bool
143 AS '_OBJWD_/complex.so' LANGUAGE 'c';
144 CREATE FUNCTION complex_abs_eq(complex, complex) RETURNS bool
145 AS '_OBJWD_/complex.so' LANGUAGE 'c';
146 CREATE FUNCTION complex_abs_ge(complex, complex) RETURNS bool
147 AS '_OBJWD_/complex.so' LANGUAGE 'c';
148 CREATE FUNCTION complex_abs_gt(complex, complex) RETURNS bool
149 AS '_OBJWD_/complex.so' LANGUAGE 'c';
151 -- the restrict and join selectivity functions are bogus (notice we only
152 -- have intltsel, eqsel and intgtsel)
154 leftarg = complex, rightarg = complex, procedure = complex_abs_lt,
155 restrict = intltsel, join = intltjoinsel
158 leftarg = complex, rightarg = complex, procedure = complex_abs_le,
159 restrict = intltsel, join = intltjoinsel
162 leftarg = complex, rightarg = complex, procedure = complex_abs_eq,
163 restrict = eqsel, join = eqjoinsel
166 leftarg = complex, rightarg = complex, procedure = complex_abs_ge,
167 restrict = intgtsel, join = intgtjoinsel
170 leftarg = complex, rightarg = complex, procedure = complex_abs_gt,
171 restrict = intgtsel, join = intgtjoinsel
174 INSERT INTO pg_opclass VALUES ('complex_abs_ops');
176 SELECT oid, opcname FROM pg_opclass WHERE opcname = 'complex_abs_ops';
178 SELECT o.oid AS opoid, o.oprname
179 INTO TABLE complex_ops_tmp
180 FROM pg_operator o, pg_type t
181 WHERE o.oprleft = t.oid and o.oprright = t.oid
182 and t.typname = 'complex';
184 -- make sure we have the right operators
185 SELECT * from complex_ops_tmp;
187 INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy)
188 SELECT am.oid, opcl.oid, c.opoid, 1
189 FROM pg_am am, pg_opclass opcl, complex_ops_tmp c
190 WHERE amname = 'btree' and opcname = 'complex_abs_ops'
193 INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy)
194 SELECT am.oid, opcl.oid, c.opoid, 2
195 FROM pg_am am, pg_opclass opcl, complex_ops_tmp c
196 WHERE amname = 'btree' and opcname = 'complex_abs_ops'
197 and c.oprname = '<=';
199 INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy)
200 SELECT am.oid, opcl.oid, c.opoid, 3
201 FROM pg_am am, pg_opclass opcl, complex_ops_tmp c
202 WHERE amname = 'btree' and opcname = 'complex_abs_ops'
205 INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy)
206 SELECT am.oid, opcl.oid, c.opoid, 4
207 FROM pg_am am, pg_opclass opcl, complex_ops_tmp c
208 WHERE amname = 'btree' and opcname = 'complex_abs_ops'
209 and c.oprname = '>=';
211 INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy)
212 SELECT am.oid, opcl.oid, c.opoid, 5
213 FROM pg_am am, pg_opclass opcl, complex_ops_tmp c
214 WHERE amname = 'btree' and opcname = 'complex_abs_ops'
218 CREATE FUNCTION complex_abs_cmp(complex, complex) RETURNS int4
219 AS '_OBJWD_/complex.so' LANGUAGE 'c';
221 SELECT oid, proname FROM pg_proc WHERE proname = 'complex_abs_cmp';
223 INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum)
224 SELECT am.oid, opcl.oid, pro.oid, 1
225 FROM pg_am am, pg_opclass opcl, pg_proc pro
226 WHERE amname = 'btree' and opcname = 'complex_abs_ops'
227 and proname = 'complex_abs_cmp';
229 -- now, we can define a btree index on complex types. First, let's populate
230 -- the table. Note that postgres needs many more tuples to start using the
231 -- btree index during selects.
232 INSERT INTO test_complex VALUES ('(56.0,-22.5)', '(-43.2,-0.07)');
233 INSERT INTO test_complex VALUES ('(-91.9,33.6)', '(8.6,3.0)');
235 CREATE INDEX test_cplx_ind ON test_complex
236 USING btree(a complex_abs_ops);
238 SELECT * from test_complex where a = '(56.0,-22.5)';
239 SELECT * from test_complex where a < '(56.0,-22.5)';
240 SELECT * from test_complex where a > '(56.0,-22.5)';
242 DELETE FROM pg_amop where (amopid, amopclaid, amopopr, amopstrategy)
244 SELECT am.oid, opcl.oid, c.opoid, 1
245 FROM pg_am am, pg_opclass opcl, complex_ops_tmp c
246 WHERE amname = 'btree' and opcname = 'complex_abs_ops'
247 and c.oprname = '<');
249 DELETE FROM pg_amop where (amopid, amopclaid, amopopr, amopstrategy)
251 SELECT am.oid, opcl.oid, c.opoid, 2
252 FROM pg_am am, pg_opclass opcl, complex_ops_tmp c
253 WHERE amname = 'btree' and opcname = 'complex_abs_ops'
254 and c.oprname = '<=');
256 DELETE FROM pg_amop where (amopid, amopclaid, amopopr, amopstrategy)
258 SELECT am.oid, opcl.oid, c.opoid, 3
259 FROM pg_am am, pg_opclass opcl, complex_ops_tmp c
260 WHERE amname = 'btree' and opcname = 'complex_abs_ops'
261 and c.oprname = '=');
263 DELETE FROM pg_amop where (amopid, amopclaid, amopopr, amopstrategy)
265 SELECT am.oid, opcl.oid, c.opoid, 4
266 FROM pg_am am, pg_opclass opcl, complex_ops_tmp c
267 WHERE amname = 'btree' and opcname = 'complex_abs_ops'
268 and c.oprname = '>=');
270 DELETE FROM pg_amop where (amopid, amopclaid, amopopr, amopstrategy)
272 SELECT am.oid, opcl.oid, c.opoid, 5
273 FROM pg_am am, pg_opclass opcl, complex_ops_tmp c
274 WHERE amname = 'btree' and opcname = 'complex_abs_ops'
275 and c.oprname = '>');
277 DELETE FROM pg_amproc where (amid, amopclaid, amproc, amprocnum)
279 SELECT am.oid, opcl.oid, pro.oid, 1
280 FROM pg_am am, pg_opclass opcl, pg_proc pro
281 WHERE amname = 'btree' and opcname = 'complex_abs_ops'
282 and proname = 'complex_abs_cmp');
284 DELETE FROM pg_opclass WHERE opcname = 'complex_abs_ops';
286 DROP FUNCTION complex_in(opaque);
287 DROP FUNCTION complex_out(opaque);
288 DROP FUNCTION complex_add(complex, complex);
289 DROP FUNCTION complex_abs_lt(complex, complex);
290 DROP FUNCTION complex_abs_le(complex, complex);
291 DROP FUNCTION complex_abs_eq(complex, complex);
292 DROP FUNCTION complex_abs_ge(complex, complex);
293 DROP FUNCTION complex_abs_gt(complex, complex);
294 DROP FUNCTION complex_abs_cmp(complex, complex);
295 DROP OPERATOR + (complex, complex);
296 DROP OPERATOR < (complex, complex);
297 DROP OPERATOR <= (complex, complex);
298 DROP OPERATOR = (complex, complex);
299 DROP OPERATOR >= (complex, complex);
300 DROP OPERATOR > (complex, complex);
301 DROP AGGREGATE complex_sum complex;
303 DROP TABLE test_complex, complex_ops_tmp;