14.5. Creating the Operators and Support Routines

Now that we have seen the ideas, here is the promised example of creating a new operator class. First, we need a set of operators. The procedure for defining operators was discussed in Chapter 11. For the complex_abs_ops operator class on B-trees, the operators we require are:

Suppose the code that implements these functions is stored in the file PGROOT/src/tutorial/complex.c, which we have compiled into PGROOT/src/tutorial/complex.so. Part of the C code looks like this:

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

         bool
         complex_abs_eq(Complex *a, Complex *b)
         {
             double amag = Mag(a), bmag = Mag(b);
             return (amag==bmag);
         }

(Note that we will only show the equality operator in this text. The other four operators are very similar. Refer to complex.c or complex.source for the details.)

We make the function known to PostgreSQL like this:

CREATE FUNCTION complex_abs_eq(complex, complex) RETURNS boolean
    AS 'PGROOT/src/tutorial/complex'
    LANGUAGE C;

There are some important things that are happening here:

Now we are ready to define the operators:

CREATE OPERATOR = (
     leftarg = complex, rightarg = complex,
     procedure = complex_abs_eq,
     restrict = eqsel, join = eqjoinsel
         );

The important things here are the procedure names (which are the C functions defined above) and the restriction and join selectivity functions. You should just use the selectivity functions used in the example (see complex.source). Note that there are different such functions for the less-than, equal, and greater-than cases. These must be supplied or the optimizer will be unable to make effective use of the index.

The next step is the registration of the comparison "support routine" required by B-trees. The C code that implements this is in the same file that contains the operator procedures:

CREATE FUNCTION complex_abs_cmp(complex, complex)
    RETURNS integer
    AS 'PGROOT/src/tutorial/complex'
    LANGUAGE C;