-- use this new type.
--
--
--- Copyright (c) 1994, Regents of the University of California
+-- Portions Copyright (c) 1996-2002, PostgreSQL Global Development Group
+-- Portions Copyright (c) 1994, Regents of the University of California
--
--- $Id: complex.source,v 1.7 2000/03/28 02:49:19 tgl Exp $
+-- $Header: /cvsroot/pgsql/src/tutorial/complex.source,v 1.14 2002/08/22 00:01:51 tgl Exp $
--
---------------------------------------------------------------------------
-- called 'complex' which represents complex numbers.
-----------------------------
--- Assume the user defined functions are in _OBJWD_/complex_DLSUFFIX_
+-- Assume the user defined functions are in _OBJWD_/complex$DLSUFFIX
+-- (we do not want to assume this is in the dynamic loader search path)
-- Look at $PWD/complex.c for the source.
-- the input function 'complex_in' takes a null-terminated string (the
-- (in memory) representation. You will get a message telling you 'complex'
-- does not exist yet but that's okay.
-CREATE FUNCTION complex_in(opaque)
+CREATE FUNCTION complex_in(cstring)
RETURNS complex
- AS '_OBJWD_/complex_DLSUFFIX_'
+ AS '_OBJWD_/complex'
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
- AS '_OBJWD_/complex_DLSUFFIX_'
+CREATE FUNCTION complex_out(complex)
+ RETURNS cstring
+ AS '_OBJWD_/complex'
LANGUAGE 'c';
-- now, we can create the type. The internallength specifies the size of the
CREATE TYPE complex (
internallength = 16,
input = complex_in,
- output = complex_out
+ output = complex_out,
+ alignment = double
);
-----------------------------
-- Using the new type:
--- user-defined types can be use like ordinary built-in types.
+-- user-defined types can be used like ordinary built-in types.
-----------------------------
-- eg. we can use it in a schema
b complex
);
--- data for user-defined type are just strings in the proper textual
+-- data for user-defined types are just strings in the proper textual
-- representation.
INSERT INTO test_complex VALUES ('(1.0, 2.5)', '(4.2, 3.55 )');
-- 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
+-- (Operator names can be reused with different numbers and types of
-- arguments.)
-----------------------------
-- first, define a function complex_add (also in complex.c)
CREATE FUNCTION complex_add(complex, complex)
RETURNS complex
- AS '_OBJWD_/complex_DLSUFFIX_'
+ AS '_OBJWD_/complex'
LANGUAGE 'c';
-- we can now define the operator. We show a binary operator here but you
-----------------------------
CREATE AGGREGATE complex_sum (
- sfunc1 = complex_add,
+ sfunc = complex_add,
basetype = complex,
- stype1 = complex,
- initcond1 = '(0,0)'
+ stype = complex,
+ initcond = '(0,0)'
);
SELECT complex_sum(a) FROM test_complex;
--------------------------------------------------------------------------------
--- ATTENTION! ATTENTION! ATTENTION! --
--- 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. --
--------------------------------------------------------------------------------
-
-SELECT 'READ ABOVE!' AS STOP;
-
-----------------------------
--- Interfacing New Types with Indices:
+-- Interfacing New Types with Indexes:
-- 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.
+-- yet. We need to create all the required operators and support
+-- functions, then we can make the operator class.
-----------------------------
-- first, define the required operators
CREATE FUNCTION complex_abs_lt(complex, complex) RETURNS bool
- AS '_OBJWD_/complex_DLSUFFIX_' LANGUAGE 'c';
+ AS '_OBJWD_/complex' LANGUAGE 'c';
CREATE FUNCTION complex_abs_le(complex, complex) RETURNS bool
- AS '_OBJWD_/complex_DLSUFFIX_' LANGUAGE 'c';
+ AS '_OBJWD_/complex' LANGUAGE 'c';
CREATE FUNCTION complex_abs_eq(complex, complex) RETURNS bool
- AS '_OBJWD_/complex_DLSUFFIX_' LANGUAGE 'c';
+ AS '_OBJWD_/complex' LANGUAGE 'c';
CREATE FUNCTION complex_abs_ge(complex, complex) RETURNS bool
- AS '_OBJWD_/complex_DLSUFFIX_' LANGUAGE 'c';
+ AS '_OBJWD_/complex' LANGUAGE 'c';
CREATE FUNCTION complex_abs_gt(complex, complex) RETURNS bool
- AS '_OBJWD_/complex_DLSUFFIX_' LANGUAGE 'c';
+ AS '_OBJWD_/complex' LANGUAGE 'c';
CREATE OPERATOR < (
leftarg = complex, rightarg = complex, procedure = complex_abs_lt,
restrict = scalargtsel, join = scalargtjoinsel
);
-INSERT INTO pg_opclass (opcname, opcdeftype)
- SELECT 'complex_abs_ops', oid FROM pg_type WHERE typname = 'complex';
-
-SELECT oid, opcname, opcdeftype
- FROM pg_opclass WHERE opcname = 'complex_abs_ops';
-
-SELECT o.oid AS opoid, o.oprname
-INTO TABLE complex_ops_tmp
-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;
-
-INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy)
- SELECT am.oid, opcl.oid, c.opoid, 1
- FROM pg_am am, pg_opclass opcl, complex_ops_tmp c
- WHERE amname = 'btree' and opcname = 'complex_abs_ops'
- and c.oprname = '<';
-
-INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy)
- SELECT am.oid, opcl.oid, c.opoid, 2
- FROM pg_am am, pg_opclass opcl, complex_ops_tmp c
- WHERE amname = 'btree' and opcname = 'complex_abs_ops'
- and c.oprname = '<=';
-
-INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy)
- SELECT am.oid, opcl.oid, c.opoid, 3
- FROM pg_am am, pg_opclass opcl, complex_ops_tmp c
- WHERE amname = 'btree' and opcname = 'complex_abs_ops'
- and c.oprname = '=';
-
-INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy)
- SELECT am.oid, opcl.oid, c.opoid, 4
- FROM pg_am am, pg_opclass opcl, complex_ops_tmp c
- WHERE amname = 'btree' and opcname = 'complex_abs_ops'
- and c.oprname = '>=';
-
-INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy)
- SELECT am.oid, opcl.oid, c.opoid, 5
- FROM pg_am am, pg_opclass opcl, complex_ops_tmp c
- WHERE amname = 'btree' and opcname = 'complex_abs_ops'
- and c.oprname = '>';
-
---
+-- create the support function too
CREATE FUNCTION complex_abs_cmp(complex, complex) RETURNS int4
- AS '_OBJWD_/complex_DLSUFFIX_' LANGUAGE 'c';
+ AS '_OBJWD_/complex' LANGUAGE 'c';
-SELECT oid, proname FROM pg_proc WHERE proname = 'complex_abs_cmp';
+-- now we can make the operator class
+CREATE OPERATOR CLASS complex_abs_ops
+ DEFAULT FOR TYPE complex USING btree AS
+ OPERATOR 1 < ,
+ OPERATOR 2 <= ,
+ OPERATOR 3 = ,
+ OPERATOR 4 >= ,
+ OPERATOR 5 > ,
+ FUNCTION 1 complex_abs_cmp(complex, complex);
-INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum)
- SELECT am.oid, opcl.oid, pro.oid, 1
- FROM pg_am am, pg_opclass opcl, pg_proc pro
- WHERE amname = 'btree' and opcname = 'complex_abs_ops'
- and proname = 'complex_abs_cmp';
-- now, we can define a btree index on complex types. First, let's populate
-- the table. Note that postgres needs many more tuples to start using the
SELECT * from test_complex where a < '(56.0,-22.5)';
SELECT * from test_complex where a > '(56.0,-22.5)';
-DELETE FROM pg_amop where (amopid, amopclaid, amopopr, amopstrategy)
- = (
- SELECT am.oid, opcl.oid, c.opoid, 1
- FROM pg_am am, pg_opclass opcl, complex_ops_tmp c
- WHERE amname = 'btree' and opcname = 'complex_abs_ops'
- and c.oprname = '<');
-
-DELETE FROM pg_amop where (amopid, amopclaid, amopopr, amopstrategy)
- = (
- SELECT am.oid, opcl.oid, c.opoid, 2
- FROM pg_am am, pg_opclass opcl, complex_ops_tmp c
- WHERE amname = 'btree' and opcname = 'complex_abs_ops'
- and c.oprname = '<=');
-
-DELETE FROM pg_amop where (amopid, amopclaid, amopopr, amopstrategy)
- = (
- SELECT am.oid, opcl.oid, c.opoid, 3
- FROM pg_am am, pg_opclass opcl, complex_ops_tmp c
- WHERE amname = 'btree' and opcname = 'complex_abs_ops'
- and c.oprname = '=');
-
-DELETE FROM pg_amop where (amopid, amopclaid, amopopr, amopstrategy)
- = (
- SELECT am.oid, opcl.oid, c.opoid, 4
- FROM pg_am am, pg_opclass opcl, complex_ops_tmp c
- WHERE amname = 'btree' and opcname = 'complex_abs_ops'
- and c.oprname = '>=');
-
-DELETE FROM pg_amop where (amopid, amopclaid, amopopr, amopstrategy)
- = (
- SELECT am.oid, opcl.oid, c.opoid, 5
- FROM pg_am am, pg_opclass opcl, complex_ops_tmp c
- WHERE amname = 'btree' and opcname = 'complex_abs_ops'
- and c.oprname = '>');
-
-DELETE FROM pg_amproc where (amid, amopclaid, amproc, amprocnum)
- = (
- SELECT am.oid, opcl.oid, pro.oid, 1
- FROM pg_am am, pg_opclass opcl, pg_proc pro
- WHERE amname = 'btree' and opcname = 'complex_abs_ops'
- and proname = 'complex_abs_cmp');
-
-DELETE FROM pg_opclass WHERE opcname = 'complex_abs_ops';
-
-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);
-DROP AGGREGATE complex_sum complex;
-DROP TYPE complex;
-DROP TABLE test_complex, complex_ops_tmp;
+
+-- clean up the example
+DROP TABLE test_complex;
+DROP TYPE complex CASCADE;
projects / postgresql / blobdiff