diff options
Diffstat (limited to 'src/tutorial/complex.source')
| -rw-r--r-- | src/tutorial/complex.source | 293 |
1 files changed, 0 insertions, 293 deletions
diff --git a/src/tutorial/complex.source b/src/tutorial/complex.source deleted file mode 100644 index 6fbaaf89dbd..00000000000 --- a/src/tutorial/complex.source +++ /dev/null @@ -1,293 +0,0 @@ ---------------------------------------------------------------------------- --- --- complex.sql- --- This file shows how to create a new user-defined type and how to --- use this new type. --- --- --- Copyright (c) 1994, Regents of the University of California --- --- $Id: complex.source,v 1.12 2002/04/17 20:57:57 tgl Exp $ --- ---------------------------------------------------------------------------- - ------------------------------ --- 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. ------------------------------ - --- 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 --- 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 - 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' - 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. - -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)'); - -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.) ------------------------------ - --- first, define a function complex_add (also in complex.c) -CREATE FUNCTION complex_add(complex, complex) - RETURNS complex - AS '_OBJWD_/complex' - 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 --- you can also define aggregate functions. The syntax is somewhat --- cryptic but the idea is to express the aggregate in terms of state --- transition functions. ------------------------------ - -CREATE AGGREGATE complex_sum ( - sfunc = complex_add, - basetype = complex, - 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: --- 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 - AS '_OBJWD_/complex' LANGUAGE 'c'; -CREATE FUNCTION complex_abs_le(complex, complex) RETURNS bool - AS '_OBJWD_/complex' LANGUAGE 'c'; -CREATE FUNCTION complex_abs_eq(complex, complex) RETURNS bool - AS '_OBJWD_/complex' LANGUAGE 'c'; -CREATE FUNCTION complex_abs_ge(complex, complex) RETURNS bool - AS '_OBJWD_/complex' LANGUAGE 'c'; -CREATE FUNCTION complex_abs_gt(complex, complex) RETURNS bool - AS '_OBJWD_/complex' LANGUAGE 'c'; - -CREATE OPERATOR < ( - leftarg = complex, rightarg = complex, procedure = complex_abs_lt, - restrict = scalarltsel, join = scalarltjoinsel -); -CREATE OPERATOR <= ( - leftarg = complex, rightarg = complex, procedure = complex_abs_le, - restrict = scalarltsel, join = scalarltjoinsel -); -CREATE OPERATOR = ( - leftarg = complex, rightarg = complex, procedure = complex_abs_eq, - restrict = eqsel, join = eqjoinsel -); -CREATE OPERATOR >= ( - leftarg = complex, rightarg = complex, procedure = complex_abs_ge, - restrict = scalargtsel, join = scalargtjoinsel -); -CREATE OPERATOR > ( - leftarg = complex, rightarg = complex, procedure = complex_abs_gt, - restrict = scalargtsel, join = scalargtjoinsel -); - -INSERT INTO pg_opclass (opcamid, opcname, opcnamespace, opcowner, opcintype, opcdefault, opckeytype) - VALUES ( - (SELECT oid FROM pg_am WHERE amname = 'btree'), - 'complex_abs_ops', - (SELECT oid FROM pg_namespace WHERE nspname = 'pg_catalog'), - 1, -- UID of superuser is hardwired to 1 as of PG 7.3 - (SELECT oid FROM pg_type WHERE typname = 'complex'), - true, - 0); - -SELECT oid, * - FROM pg_opclass WHERE opcname = 'complex_abs_ops'; - -SELECT o.oid AS opoid, o.oprname -INTO TEMP 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 (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' - and c.oprname = '<'; - -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' - and c.oprname = '<='; - -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' - and c.oprname = '='; - -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' - and c.oprname = '>='; - -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' - and c.oprname = '>'; - --- -CREATE FUNCTION complex_abs_cmp(complex, complex) RETURNS int4 - AS '_OBJWD_/complex' LANGUAGE 'c'; - -SELECT oid, proname FROM pg_proc WHERE proname = 'complex_abs_cmp'; - -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' - 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 --- btree index during selects. -INSERT INTO test_complex VALUES ('(56.0,-22.5)', '(-43.2,-0.07)'); -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)'; -SELECT * from test_complex where a > '(56.0,-22.5)'; - -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'; - -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; |