1---------------------------------------------------------------------------
2--
3-- complex.sql-
4--    This file shows how to create a new user-defined type and how to
5--    use this new type.
6--
7--
8-- Portions Copyright (c) 1996-2017, PostgreSQL Global Development Group
9-- Portions Copyright (c) 1994, Regents of the University of California
10--
11-- src/tutorial/complex.source
12--
13---------------------------------------------------------------------------
14
15-----------------------------
16-- Creating a new type:
17--	We are going to create a new type called 'complex' which represents
18--	complex numbers.
19--	A user-defined type must have an input and an output function, and
20--	optionally can have binary input and output functions.  All of these
21--	are usually user-defined C functions.
22-----------------------------
23
24-- Assume the user defined functions are in _OBJWD_/complex$DLSUFFIX
25-- (we do not want to assume this is in the dynamic loader search path).
26-- Look at $PWD/complex.c for the source.  Note that we declare all of
27-- them as STRICT, so we do not need to cope with NULL inputs in the
28-- C code.  We also mark them IMMUTABLE, since they always return the
29-- same outputs given the same inputs.
30
31-- the input function 'complex_in' takes a null-terminated string (the
32-- textual representation of the type) and turns it into the internal
33-- (in memory) representation. You will get a message telling you 'complex'
34-- does not exist yet but that's okay.
35
36CREATE FUNCTION complex_in(cstring)
37   RETURNS complex
38   AS '_OBJWD_/complex'
39   LANGUAGE C IMMUTABLE STRICT;
40
41-- the output function 'complex_out' takes the internal representation and
42-- converts it into the textual representation.
43
44CREATE FUNCTION complex_out(complex)
45   RETURNS cstring
46   AS '_OBJWD_/complex'
47   LANGUAGE C IMMUTABLE STRICT;
48
49-- the binary input function 'complex_recv' takes a StringInfo buffer
50-- and turns its contents into the internal representation.
51
52CREATE FUNCTION complex_recv(internal)
53   RETURNS complex
54   AS '_OBJWD_/complex'
55   LANGUAGE C IMMUTABLE STRICT;
56
57-- the binary output function 'complex_send' takes the internal representation
58-- and converts it into a (hopefully) platform-independent bytea string.
59
60CREATE FUNCTION complex_send(complex)
61   RETURNS bytea
62   AS '_OBJWD_/complex'
63   LANGUAGE C IMMUTABLE STRICT;
64
65
66-- now, we can create the type. The internallength specifies the size of the
67-- memory block required to hold the type (we need two 8-byte doubles).
68
69CREATE TYPE complex (
70   internallength = 16,
71   input = complex_in,
72   output = complex_out,
73   receive = complex_recv,
74   send = complex_send,
75   alignment = double
76);
77
78
79-----------------------------
80-- Using the new type:
81--	user-defined types can be used like ordinary built-in types.
82-----------------------------
83
84-- eg. we can use it in a table
85
86CREATE TABLE test_complex (
87	a	complex,
88	b	complex
89);
90
91-- data for user-defined types are just strings in the proper textual
92-- representation.
93
94INSERT INTO test_complex VALUES ('(1.0, 2.5)', '(4.2, 3.55 )');
95INSERT INTO test_complex VALUES ('(33.0, 51.4)', '(100.42, 93.55)');
96
97SELECT * FROM test_complex;
98
99-----------------------------
100-- Creating an operator for the new type:
101--	Let's define an add operator for complex types. Since POSTGRES
102--	supports function overloading, we'll use + as the add operator.
103--	(Operator names can be reused with different numbers and types of
104--	arguments.)
105-----------------------------
106
107-- first, define a function complex_add (also in complex.c)
108CREATE FUNCTION complex_add(complex, complex)
109   RETURNS complex
110   AS '_OBJWD_/complex'
111   LANGUAGE C IMMUTABLE STRICT;
112
113-- we can now define the operator. We show a binary operator here but you
114-- can also define unary operators by omitting either of leftarg or rightarg.
115CREATE OPERATOR + (
116   leftarg = complex,
117   rightarg = complex,
118   procedure = complex_add,
119   commutator = +
120);
121
122
123SELECT (a + b) AS c FROM test_complex;
124
125-- Occasionally, you may find it useful to cast the string to the desired
126-- type explicitly. :: denotes a type cast.
127
128SELECT  a + '(1.0,1.0)'::complex AS aa,
129        b + '(1.0,1.0)'::complex AS bb
130   FROM test_complex;
131
132
133-----------------------------
134-- Creating aggregate functions
135--	you can also define aggregate functions. The syntax is somewhat
136--	cryptic but the idea is to express the aggregate in terms of state
137--	transition functions.
138-----------------------------
139
140CREATE AGGREGATE complex_sum (
141   sfunc = complex_add,
142   basetype = complex,
143   stype = complex,
144   initcond = '(0,0)'
145);
146
147SELECT complex_sum(a) FROM test_complex;
148
149
150-----------------------------
151-- Interfacing New Types with Indexes:
152--	We cannot define a secondary index (eg. a B-tree) over the new type
153--	yet. We need to create all the required operators and support
154--      functions, then we can make the operator class.
155-----------------------------
156
157-- first, define the required operators
158CREATE FUNCTION complex_abs_lt(complex, complex) RETURNS bool
159   AS '_OBJWD_/complex' LANGUAGE C IMMUTABLE STRICT;
160CREATE FUNCTION complex_abs_le(complex, complex) RETURNS bool
161   AS '_OBJWD_/complex' LANGUAGE C IMMUTABLE STRICT;
162CREATE FUNCTION complex_abs_eq(complex, complex) RETURNS bool
163   AS '_OBJWD_/complex' LANGUAGE C IMMUTABLE STRICT;
164CREATE FUNCTION complex_abs_ge(complex, complex) RETURNS bool
165   AS '_OBJWD_/complex' LANGUAGE C IMMUTABLE STRICT;
166CREATE FUNCTION complex_abs_gt(complex, complex) RETURNS bool
167   AS '_OBJWD_/complex' LANGUAGE C IMMUTABLE STRICT;
168
169CREATE OPERATOR < (
170   leftarg = complex, rightarg = complex, procedure = complex_abs_lt,
171   commutator = > , negator = >= ,
172   restrict = scalarltsel, join = scalarltjoinsel
173);
174CREATE OPERATOR <= (
175   leftarg = complex, rightarg = complex, procedure = complex_abs_le,
176   commutator = >= , negator = > ,
177   restrict = scalarltsel, join = scalarltjoinsel
178);
179CREATE OPERATOR = (
180   leftarg = complex, rightarg = complex, procedure = complex_abs_eq,
181   commutator = = ,
182   -- leave out negator since we didn't create <> operator
183   -- negator = <> ,
184   restrict = eqsel, join = eqjoinsel
185);
186CREATE OPERATOR >= (
187   leftarg = complex, rightarg = complex, procedure = complex_abs_ge,
188   commutator = <= , negator = < ,
189   restrict = scalargtsel, join = scalargtjoinsel
190);
191CREATE OPERATOR > (
192   leftarg = complex, rightarg = complex, procedure = complex_abs_gt,
193   commutator = < , negator = <= ,
194   restrict = scalargtsel, join = scalargtjoinsel
195);
196
197-- create the support function too
198CREATE FUNCTION complex_abs_cmp(complex, complex) RETURNS int4
199   AS '_OBJWD_/complex' LANGUAGE C IMMUTABLE STRICT;
200
201-- now we can make the operator class
202CREATE OPERATOR CLASS complex_abs_ops
203    DEFAULT FOR TYPE complex USING btree AS
204        OPERATOR        1       < ,
205        OPERATOR        2       <= ,
206        OPERATOR        3       = ,
207        OPERATOR        4       >= ,
208        OPERATOR        5       > ,
209        FUNCTION        1       complex_abs_cmp(complex, complex);
210
211
212-- now, we can define a btree index on complex types. First, let's populate
213-- the table. Note that postgres needs many more tuples to start using the
214-- btree index during selects.
215INSERT INTO test_complex VALUES ('(56.0,-22.5)', '(-43.2,-0.07)');
216INSERT INTO test_complex VALUES ('(-91.9,33.6)', '(8.6,3.0)');
217
218CREATE INDEX test_cplx_ind ON test_complex
219   USING btree(a complex_abs_ops);
220
221SELECT * from test_complex where a = '(56.0,-22.5)';
222SELECT * from test_complex where a < '(56.0,-22.5)';
223SELECT * from test_complex where a > '(56.0,-22.5)';
224
225
226-- clean up the example
227DROP TABLE test_complex;
228DROP TYPE complex CASCADE;
229