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