- publishing free software manuals
PostgreSQL Reference Manual - Volume 2 - Programming Guide
by The PostgreSQL Global Development Group
Paperback (6"x9"), 408 pages
ISBN 0954612035
RRP £19.95 ($34.95)

Sales of this book support the PostgreSQL project! Get a printed copy>>>

5.14.4 An Example

Now that we have seen the ideas, here is the promised example of creating a new operator class. (You can find a working copy of this example in ‘src/tutorial/complex.c’ and ‘src/tutorial/complex.sql’ in the source distribution.) The operator class encapsulates operators that sort complex numbers in absolute value order, so we choose the name complex_abs_ops. First, we need a set of operators. The procedure for defining operators was discussed in section 5.12 User-Defined Operators. For an operator class on B-trees, the operators we require are:

The least error-prone way to define a related set of comparison operators is to write the B-tree comparison support function first, and then write the other functions as one-line wrappers around the support function. This reduces the odds of getting inconsistent results for corner cases. Following this approach, we first write

#define Mag(c)  ((c)->x*(c)->x + (c)->y*(c)->y)

static int
complex_abs_cmp_internal(Complex *a, Complex *b)
    double      amag = Mag(a),
                bmag = Mag(b);

    if (amag < bmag)
        return -1;
    if (amag > bmag)
        return 1;
    return 0;

Now the less-than function looks like


    Complex    *a = (Complex *) PG_GETARG_POINTER(0);
    Complex    *b = (Complex *) PG_GETARG_POINTER(1);

    PG_RETURN_BOOL(complex_abs_cmp_internal(a, b) < 0);

The other four functions differ only in how they compare the internal function's result to zero.

Next we declare the functions and the operators based on the functions to SQL:

CREATE FUNCTION complex_abs_lt(complex, complex) RETURNS bool
    AS 'filename', 'complex_abs_lt'

   leftarg = complex, rightarg = complex, procedure =
   commutator = > , negator = >= ,
   restrict = scalarltsel, join = scalarltjoinsel

It is important to specify the correct commutator and negator operators, as well as suitable restriction and join selectivity functions, otherwise the optimizer will be unable to make effective use of the index. Note that the less-than, equal, and greater-than cases should use different selectivity functions.

Other things worth noting are happening here:

The next step is the registration of the support routine required by B-trees. The example C code that implements this is in the same file that contains the operator functions. This is how we declare the function:

CREATE FUNCTION complex_abs_cmp(complex, complex)
    RETURNS integer
    AS 'filename'

Now that we have the required operators and support routine, we can finally create the operator class:

    DEFAULT FOR TYPE complex USING btree AS
        OPERATOR        1       < ,
        OPERATOR        2       <= ,
        OPERATOR        3       = ,
        OPERATOR        4       >= ,
        OPERATOR        5       > ,
        FUNCTION        1       complex_abs_cmp(complex,

And we're done! It should now be possible to create and use B-tree indexes on complex columns.

We could have written the operator entries more verbosely, as in

OPERATOR        1       < (complex, complex) ,

but there is no need to do so when the operators take the same data type we are defining the operator class for.

The above example assumes that you want to make this new operator class the default B-tree operator class for the complex data type. If you don't, just leave out the word DEFAULT.

ISBN 0954612035PostgreSQL Reference Manual - Volume 2 - Programming GuideSee the print edition