Adding upstream version 14.5.upstream/14.5upstream

Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>

1 files changed, 228 insertions, 0 deletions

diff --git a/src/tutorial/complex.source b/src/tutorial/complex.source
new file mode 100644
index 0000000..d1f61fd
--- /dev/null
+++ b/src/tutorial/complex.source
@@ -0,0 +1,228 @@
+---------------------------------------------------------------------------
+--
+-- complex.sql-
+--    This file shows how to create a new user-defined type and how to
+--    use this new type.
+--
+--
+-- Portions Copyright (c) 1996-2021, PostgreSQL Global Development Group
+-- Portions Copyright (c) 1994, Regents of the University of California
+--
+-- src/tutorial/complex.source
+--
+---------------------------------------------------------------------------
+
+-----------------------------
+-- Creating a new type:
+--	We are going to create a new type called 'complex' which represents
+--	complex numbers.
+--	A user-defined type must have an input and an output function, and
+--	optionally can have binary input and output functions.  All of these
+--	are usually user-defined C functions.
+-----------------------------
+
+-- 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.  Note that we declare all of
+-- them as STRICT, so we do not need to cope with NULL inputs in the
+-- C code.  We also mark them IMMUTABLE, since they always return the
+-- same outputs given the same inputs.
+
+-- 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(cstring)
+   RETURNS complex
+   AS '_OBJWD_/complex'
+   LANGUAGE C IMMUTABLE STRICT;
+
+-- the output function 'complex_out' takes the internal representation and
+-- converts it into the textual representation.
+
+CREATE FUNCTION complex_out(complex)
+   RETURNS cstring
+   AS '_OBJWD_/complex'
+   LANGUAGE C IMMUTABLE STRICT;
+
+-- the binary input function 'complex_recv' takes a StringInfo buffer
+-- and turns its contents into the internal representation.
+
+CREATE FUNCTION complex_recv(internal)
+   RETURNS complex
+   AS '_OBJWD_/complex'
+   LANGUAGE C IMMUTABLE STRICT;
+
+-- the binary output function 'complex_send' takes the internal representation
+-- and converts it into a (hopefully) platform-independent bytea string.
+
+CREATE FUNCTION complex_send(complex)
+   RETURNS bytea
+   AS '_OBJWD_/complex'
+   LANGUAGE C IMMUTABLE STRICT;
+
+
+-- 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,
+   receive = complex_recv,
+   send = complex_send,
+   alignment = double
+);
+
+
+-----------------------------
+-- Using the new type:
+--	user-defined types can be used like ordinary built-in types.
+-----------------------------
+
+-- eg. we can use it in a table
+
+CREATE TABLE test_complex (
+	a	complex,
+	b	complex
+);
+
+-- 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 )');
+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.
+--	(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'
+   LANGUAGE C IMMUTABLE STRICT;
+
+-- we can now define the operator. We show a binary operator here but you
+-- can also define a prefix operator by omitting the leftarg.
+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;
+
+
+-----------------------------
+-- Interfacing New Types with Indexes:
+--	We cannot define a secondary index (eg. a B-tree) over the new type
+--	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' LANGUAGE C IMMUTABLE STRICT;
+CREATE FUNCTION complex_abs_le(complex, complex) RETURNS bool
+   AS '_OBJWD_/complex' LANGUAGE C IMMUTABLE STRICT;
+CREATE FUNCTION complex_abs_eq(complex, complex) RETURNS bool
+   AS '_OBJWD_/complex' LANGUAGE C IMMUTABLE STRICT;
+CREATE FUNCTION complex_abs_ge(complex, complex) RETURNS bool
+   AS '_OBJWD_/complex' LANGUAGE C IMMUTABLE STRICT;
+CREATE FUNCTION complex_abs_gt(complex, complex) RETURNS bool
+   AS '_OBJWD_/complex' LANGUAGE C IMMUTABLE STRICT;
+
+CREATE OPERATOR < (
+   leftarg = complex, rightarg = complex, procedure = complex_abs_lt,
+   commutator = > , negator = >= ,
+   restrict = scalarltsel, join = scalarltjoinsel
+);
+CREATE OPERATOR <= (
+   leftarg = complex, rightarg = complex, procedure = complex_abs_le,
+   commutator = >= , negator = > ,
+   restrict = scalarlesel, join = scalarlejoinsel
+);
+CREATE OPERATOR = (
+   leftarg = complex, rightarg = complex, procedure = complex_abs_eq,
+   commutator = = ,
+   -- leave out negator since we didn't create <> operator
+   -- negator = <> ,
+   restrict = eqsel, join = eqjoinsel
+);
+CREATE OPERATOR >= (
+   leftarg = complex, rightarg = complex, procedure = complex_abs_ge,
+   commutator = <= , negator = < ,
+   restrict = scalargesel, join = scalargejoinsel
+);
+CREATE OPERATOR > (
+   leftarg = complex, rightarg = complex, procedure = complex_abs_gt,
+   commutator = < , negator = <= ,
+   restrict = scalargtsel, join = scalargtjoinsel
+);
+
+-- create the support function too
+CREATE FUNCTION complex_abs_cmp(complex, complex) RETURNS int4
+   AS '_OBJWD_/complex' LANGUAGE C IMMUTABLE STRICT;
+
+-- 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);
+
+
+-- 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)';
+
+
+-- clean up the example
+DROP TABLE test_complex;
+DROP TYPE complex CASCADE;