complex.source 9.64 KB
Newer Older
1 2 3 4
---------------------------------------------------------------------------
--
-- complex.sql-
--    This file shows how to create a new user-defined type and how to
5
--    use this new type.
6 7 8 9
-- 
--
-- Copyright (c) 1994, Regents of the University of California
--
10
-- $Id: complex.source,v 1.11 2001/10/26 20:45:33 tgl Exp $
11 12 13 14 15 16 17 18 19 20
--
---------------------------------------------------------------------------

-----------------------------
-- Creating a new type:
--	a user-defined type must have an input and an output function. They
--	are user-defined C functions. We are going to create a new type 
--	called 'complex' which represents complex numbers.
-----------------------------

21 22
-- Assume the user defined functions are in _OBJWD_/complex$DLSUFFIX
-- (we do not want to assume this is in the dynamic loader search path)
23
-- Look at $PWD/complex.c for the source.
24 25 26 27 28 29 30 31

-- the input function 'complex_in' takes a null-terminated string (the 
-- textual representation of the type) and turns it into the internal
-- (in memory) representation. You will get a message telling you 'complex'
-- does not exist yet but that's okay.

CREATE FUNCTION complex_in(opaque)
   RETURNS complex
32
   AS '_OBJWD_/complex'
33 34 35 36 37 38 39
   LANGUAGE 'c';

-- the output function 'complex_out' takes the internal representation and
-- converts it into the textual representation.

CREATE FUNCTION complex_out(opaque)
   RETURNS opaque
40
   AS '_OBJWD_/complex'
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
   LANGUAGE 'c';

-- now, we can create the type. The internallength specifies the size of the
-- memory block required to hold the type (we need two 8-byte doubles).

CREATE TYPE complex (
   internallength = 16, 
   input = complex_in,
   output = complex_out
);


-----------------------------
-- Using the new type:
--	user-defined types can be use like ordinary built-in types.
-----------------------------

-- eg. we can use it in a schema

CREATE TABLE test_complex (
	a	complex,
	b	complex
);

-- data for user-defined type are just strings in the proper textual
-- representation. 

68 69
INSERT INTO test_complex VALUES ('(1.0, 2.5)', '(4.2, 3.55 )');
INSERT INTO test_complex VALUES ('(33.0, 51.4)', '(100.42, 93.55)');
70 71 72 73 74 75 76 77 78 79 80

SELECT * FROM test_complex;

-----------------------------
-- Creating an operator for the new type:
--	Let's define an add operator for complex types. Since POSTGRES
--	supports function overloading, we'll use + as the add operator.
--	(Operators can be reused with different number and types of 
--	arguments.)
-----------------------------

81
-- first, define a function complex_add (also in complex.c)
82 83
CREATE FUNCTION complex_add(complex, complex)
   RETURNS complex
84
   AS '_OBJWD_/complex'
85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108
   LANGUAGE 'c';

-- we can now define the operator. We show a binary operator here but you
-- can also define unary operators by omitting either of leftarg or rightarg.
CREATE OPERATOR + ( 
   leftarg = complex,
   rightarg = complex,
   procedure = complex_add,
   commutator = +
);


SELECT (a + b) AS c FROM test_complex;

-- Occasionally, you may find it useful to cast the string to the desired
-- type explicitly. :: denotes a type cast.

SELECT  a + '(1.0,1.0)'::complex AS aa,
        b + '(1.0,1.0)'::complex AS bb
   FROM test_complex;


-----------------------------
-- Creating aggregate functions
109
--	you can also define aggregate functions. The syntax is somewhat
110 111 112 113 114
--	cryptic but the idea is to express the aggregate in terms of state
--	transition functions.
-----------------------------

CREATE AGGREGATE complex_sum (
115
   sfunc = complex_add,
116
   basetype = complex,
117 118
   stype = complex,
   initcond = '(0,0)'
119 120 121 122 123 124 125
);

SELECT complex_sum(a) FROM test_complex;


-------------------------------------------------------------------------------
--             ATTENTION!      ATTENTION!      ATTENTION!                    --
126 127
--  YOU MAY SKIP THE SECTION BELOW ON INTERFACING WITH INDICES. YOU DON'T    --
--  NEED THE FOLLOWING IF YOU DON'T USE INDICES WITH NEW DATA TYPES.         --
128 129 130 131 132 133 134 135 136 137 138 139 140 141
-------------------------------------------------------------------------------

SELECT 'READ ABOVE!' AS STOP;

-----------------------------
-- Interfacing New Types with Indices:
--	We cannot define a secondary index (eg. a B-tree) over the new type
--	yet. We need to modify a few system catalogs to show POSTGRES how
--	to use the new type. Unfortunately, there is no simple command to
--	do this. Please bear with me.
-----------------------------

-- first, define the required operators
CREATE FUNCTION complex_abs_lt(complex, complex) RETURNS bool
142
   AS '_OBJWD_/complex' LANGUAGE 'c';
143
CREATE FUNCTION complex_abs_le(complex, complex) RETURNS bool
144
   AS '_OBJWD_/complex' LANGUAGE 'c';
145
CREATE FUNCTION complex_abs_eq(complex, complex) RETURNS bool
146
   AS '_OBJWD_/complex' LANGUAGE 'c';
147
CREATE FUNCTION complex_abs_ge(complex, complex) RETURNS bool
148
   AS '_OBJWD_/complex' LANGUAGE 'c';
149
CREATE FUNCTION complex_abs_gt(complex, complex) RETURNS bool
150
   AS '_OBJWD_/complex' LANGUAGE 'c';
151 152 153

CREATE OPERATOR < (
   leftarg = complex, rightarg = complex, procedure = complex_abs_lt,
154
   restrict = scalarltsel, join = scalarltjoinsel
155
);
156 157
CREATE OPERATOR <= (
   leftarg = complex, rightarg = complex, procedure = complex_abs_le,
158
   restrict = scalarltsel, join = scalarltjoinsel
159
);
160 161 162
CREATE OPERATOR = (
   leftarg = complex, rightarg = complex, procedure = complex_abs_eq,
   restrict = eqsel, join = eqjoinsel
163
);
164 165
CREATE OPERATOR >= (
   leftarg = complex, rightarg = complex, procedure = complex_abs_ge,
166
   restrict = scalargtsel, join = scalargtjoinsel
167
);
168 169
CREATE OPERATOR > (
   leftarg = complex, rightarg = complex, procedure = complex_abs_gt,
170
   restrict = scalargtsel, join = scalargtjoinsel
171 172
);

173 174 175 176 177 178 179
INSERT INTO pg_opclass (opcamid, opcname, opcintype, opcdefault, opckeytype)
    VALUES (
        (SELECT oid FROM pg_am WHERE amname = 'btree'),
        'complex_abs_ops',
        (SELECT oid FROM pg_type WHERE typname = 'complex'),
        true,
        0);
180

181
SELECT oid, *
182
    FROM pg_opclass WHERE opcname = 'complex_abs_ops';
183 184

SELECT o.oid AS opoid, o.oprname
185
INTO TEMP TABLE complex_ops_tmp
186 187 188 189 190 191 192
FROM pg_operator o, pg_type t
WHERE o.oprleft = t.oid and o.oprright = t.oid
   and t.typname = 'complex';

-- make sure we have the right operators
SELECT * from complex_ops_tmp;

193 194 195 196 197 198
INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr)
   SELECT opcl.oid, 1, false, c.opoid
   FROM pg_opclass opcl, complex_ops_tmp c
   WHERE
      opcamid = (SELECT oid FROM pg_am WHERE amname = 'btree')
      and opcname = 'complex_abs_ops' 
199 200
      and c.oprname = '<';

201 202 203 204 205 206
INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr)
   SELECT opcl.oid, 2, false, c.opoid
   FROM pg_opclass opcl, complex_ops_tmp c
   WHERE
      opcamid = (SELECT oid FROM pg_am WHERE amname = 'btree')
      and opcname = 'complex_abs_ops' 
207 208
      and c.oprname = '<=';

209 210 211 212 213 214
INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr)
   SELECT opcl.oid, 3, false, c.opoid
   FROM pg_opclass opcl, complex_ops_tmp c
   WHERE
      opcamid = (SELECT oid FROM pg_am WHERE amname = 'btree')
      and opcname = 'complex_abs_ops' 
215 216
      and c.oprname = '=';

217 218 219 220 221 222
INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr)
   SELECT opcl.oid, 4, false, c.opoid
   FROM pg_opclass opcl, complex_ops_tmp c
   WHERE
      opcamid = (SELECT oid FROM pg_am WHERE amname = 'btree')
      and opcname = 'complex_abs_ops' 
223 224
      and c.oprname = '>=';

225 226 227 228 229 230
INSERT INTO pg_amop (amopclaid, amopstrategy, amopreqcheck, amopopr)
   SELECT opcl.oid, 5, false, c.opoid
   FROM pg_opclass opcl, complex_ops_tmp c
   WHERE
      opcamid = (SELECT oid FROM pg_am WHERE amname = 'btree')
      and opcname = 'complex_abs_ops' 
231 232 233 234
      and c.oprname = '>';

--
CREATE FUNCTION complex_abs_cmp(complex, complex) RETURNS int4
235
   AS '_OBJWD_/complex' LANGUAGE 'c';
236 237 238

SELECT oid, proname FROM pg_proc WHERE proname = 'complex_abs_cmp';

239 240 241 242 243 244
INSERT INTO pg_amproc (amopclaid, amprocnum, amproc)
   SELECT opcl.oid, 1, pro.oid
   FROM pg_opclass opcl, pg_proc pro
   WHERE
      opcamid = (SELECT oid FROM pg_am WHERE amname = 'btree')
      and opcname = 'complex_abs_ops'
245 246 247
      and proname = 'complex_abs_cmp';

-- now, we can define a btree index on complex types. First, let's populate
248 249
-- the table. Note that postgres needs many more tuples to start using the
-- btree index during selects.
250
INSERT INTO test_complex VALUES ('(56.0,-22.5)', '(-43.2,-0.07)');
251 252 253 254 255 256 257
INSERT INTO test_complex VALUES ('(-91.9,33.6)', '(8.6,3.0)');

CREATE INDEX test_cplx_ind ON test_complex
   USING btree(a complex_abs_ops);

SELECT * from test_complex where a = '(56.0,-22.5)';
SELECT * from test_complex where a < '(56.0,-22.5)';
258
SELECT * from test_complex where a > '(56.0,-22.5)';
259

260 261 262 263 264 265 266 267 268 269 270 271 272
DELETE FROM pg_amop WHERE
   amopclaid = (SELECT oid FROM pg_opclass WHERE
      opcamid = (SELECT oid FROM pg_am WHERE amname = 'btree')
      and opcname = 'complex_abs_ops');

DELETE FROM pg_amproc WHERE
   amopclaid = (SELECT oid FROM pg_opclass WHERE
      opcamid = (SELECT oid FROM pg_am WHERE amname = 'btree')
      and opcname = 'complex_abs_ops');

DELETE FROM pg_opclass WHERE
      opcamid = (SELECT oid FROM pg_am WHERE amname = 'btree')
      and opcname = 'complex_abs_ops';
273

274 275 276 277 278 279 280 281 282 283 284 285 286 287 288
DROP FUNCTION complex_in(opaque);
DROP FUNCTION complex_out(opaque);
DROP FUNCTION complex_add(complex, complex);
DROP FUNCTION complex_abs_lt(complex, complex);
DROP FUNCTION complex_abs_le(complex, complex);
DROP FUNCTION complex_abs_eq(complex, complex);
DROP FUNCTION complex_abs_ge(complex, complex);
DROP FUNCTION complex_abs_gt(complex, complex);
DROP FUNCTION complex_abs_cmp(complex, complex);
DROP OPERATOR + (complex, complex);
DROP OPERATOR < (complex, complex);
DROP OPERATOR <= (complex, complex);
DROP OPERATOR = (complex, complex);
DROP OPERATOR >= (complex, complex);
DROP OPERATOR > (complex, complex);
289
DROP AGGREGATE complex_sum (complex);
290
DROP TYPE complex;
291
DROP TABLE test_complex, complex_ops_tmp;