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.4 1998/03/01 04:52:59 scrappy 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 amopselect, amopnpages)
189 SELECT am.oid, opcl.oid, c.opoid, 1,
190 'btreesel'::regproc, 'btreenpage'::regproc
191 FROM pg_am am, pg_opclass opcl, complex_ops_tmp c
192 WHERE amname = 'btree' and opcname = 'complex_abs_ops'
195 INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy,
196 amopselect, amopnpages)
197 SELECT am.oid, opcl.oid, c.opoid, 2,
198 'btreesel'::regproc, 'btreenpage'::regproc
199 FROM pg_am am, pg_opclass opcl, complex_ops_tmp c
200 WHERE amname = 'btree' and opcname = 'complex_abs_ops'
201 and c.oprname = '<=';
203 INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy,
204 amopselect, amopnpages)
205 SELECT am.oid, opcl.oid, c.opoid, 3,
206 'btreesel'::regproc, 'btreenpage'::regproc
207 FROM pg_am am, pg_opclass opcl, complex_ops_tmp c
208 WHERE amname = 'btree' and opcname = 'complex_abs_ops'
211 INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy,
212 amopselect, amopnpages)
213 SELECT am.oid, opcl.oid, c.opoid, 4,
214 'btreesel'::regproc, 'btreenpage'::regproc
215 FROM pg_am am, pg_opclass opcl, complex_ops_tmp c
216 WHERE amname = 'btree' and opcname = 'complex_abs_ops'
217 and c.oprname = '>=';
219 INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy,
220 amopselect, amopnpages)
221 SELECT am.oid, opcl.oid, c.opoid, 5,
222 'btreesel'::regproc, 'btreenpage'::regproc
223 FROM pg_am am, pg_opclass opcl, complex_ops_tmp c
224 WHERE amname = 'btree' and opcname = 'complex_abs_ops'
228 CREATE FUNCTION complex_abs_cmp(complex, complex) RETURNS int4
229 AS '_OBJWD_/complex.so' LANGUAGE 'c';
231 SELECT oid, proname FROM pg_proc WHERE proname = 'complex_abs_cmp';
233 INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum)
234 SELECT am.oid, opcl.oid, pro.oid, 1
235 FROM pg_am am, pg_opclass opcl, pg_proc pro
236 WHERE amname = 'btree' and opcname = 'complex_abs_ops'
237 and proname = 'complex_abs_cmp';
239 -- now, we can define a btree index on complex types. First, let's populate
240 -- the table. Note that postgres needs many more tuples to start using the
241 -- btree index during selects.
242 INSERT INTO test_complex VALUES ('(56.0,-22.5)', '(-43.2,-0.07)');
243 INSERT INTO test_complex VALUES ('(-91.9,33.6)', '(8.6,3.0)');
245 CREATE INDEX test_cplx_ind ON test_complex
246 USING btree(a complex_abs_ops);
248 SELECT * from test_complex where a = '(56.0,-22.5)';
249 SELECT * from test_complex where a < '(56.0,-22.5)';
250 SELECT * from test_complex where a > '(56.0,-22.5)';
252 DELETE FROM pg_amop where (amopid, amopclaid, amopopr, amopstrategy)
254 SELECT am.oid, opcl.oid, c.opoid, 1
255 FROM pg_am am, pg_opclass opcl, complex_ops_tmp c
256 WHERE amname = 'btree' and opcname = 'complex_abs_ops'
257 and c.oprname = '<');
259 DELETE FROM pg_amop where (amopid, amopclaid, amopopr, amopstrategy)
261 SELECT am.oid, opcl.oid, c.opoid, 2
262 FROM pg_am am, pg_opclass opcl, complex_ops_tmp c
263 WHERE amname = 'btree' and opcname = 'complex_abs_ops'
264 and c.oprname = '<=');
266 DELETE FROM pg_amop where (amopid, amopclaid, amopopr, amopstrategy)
268 SELECT am.oid, opcl.oid, c.opoid, 3
269 FROM pg_am am, pg_opclass opcl, complex_ops_tmp c
270 WHERE amname = 'btree' and opcname = 'complex_abs_ops'
271 and c.oprname = '=');
273 DELETE FROM pg_amop where (amopid, amopclaid, amopopr, amopstrategy)
275 SELECT am.oid, opcl.oid, c.opoid, 4
276 FROM pg_am am, pg_opclass opcl, complex_ops_tmp c
277 WHERE amname = 'btree' and opcname = 'complex_abs_ops'
278 and c.oprname = '>=');
280 DELETE FROM pg_amop where (amopid, amopclaid, amopopr, amopstrategy)
282 SELECT am.oid, opcl.oid, c.opoid, 5
283 FROM pg_am am, pg_opclass opcl, complex_ops_tmp c
284 WHERE amname = 'btree' and opcname = 'complex_abs_ops'
285 and c.oprname = '>');
287 DELETE FROM pg_amproc where (amid, amopclaid, amproc, amprocnum)
289 SELECT am.oid, opcl.oid, pro.oid, 1
290 FROM pg_am am, pg_opclass opcl, pg_proc pro
291 WHERE amname = 'btree' and opcname = 'complex_abs_ops'
292 and proname = 'complex_abs_cmp');
294 DELETE FROM pg_opclass WHERE opcname = 'complex_abs_ops';
296 DROP FUNCTION complex_in(opaque);
297 DROP FUNCTION complex_out(opaque);
298 DROP FUNCTION complex_add(complex, complex);
299 DROP FUNCTION complex_abs_lt(complex, complex);
300 DROP FUNCTION complex_abs_le(complex, complex);
301 DROP FUNCTION complex_abs_eq(complex, complex);
302 DROP FUNCTION complex_abs_ge(complex, complex);
303 DROP FUNCTION complex_abs_gt(complex, complex);
304 DROP FUNCTION complex_abs_cmp(complex, complex);
305 DROP OPERATOR + (complex, complex);
306 DROP OPERATOR < (complex, complex);
307 DROP OPERATOR <= (complex, complex);
308 DROP OPERATOR = (complex, complex);
309 DROP OPERATOR >= (complex, complex);
310 DROP OPERATOR > (complex, complex);
311 DROP AGGREGATE complex_sum complex;
313 DROP TABLE test_complex, complex_ops_tmp;