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:
- absolute-value less-than (strategy 1)
- absolute-value less-than-or-equal (strategy 2)
- absolute-value equal (strategy 3)
- absolute-value greater-than-or-equal (strategy 4)
- absolute-value greater-than (strategy 5)
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:
First, note that operators for less-than, less-than-or-equal, equal,
greater-than-or-equal, and greater-than for complex
are being defined. We can only have one operator named, say, = and
taking type complex for both operands. In this case
we don't have any other operator = for complex,
but if we were building a practical data type we'd probably want = to
be the ordinary equality operation for complex numbers. In that case,
we'd need to use some other operator name for complex_abs_eq.
Second, although PostgreSQL can cope with operators having
the same name as long as they have different input data types, C can only
cope with one global routine having a given name, period. So we shouldn't
name the C function something simple like abs_eq.
Usually it's a good practice to include the data type name in the C
function name, so as not to conflict with functions for other data types.
Third, we could have made the PostgreSQL name of the function
abs_eq, relying on PostgreSQL to distinguish it
by input data types from any other PostgreSQL function of the same name.
To keep the example simple, we make the function have the same names
at the C level and PostgreSQL level.
Finally, note that these operator functions return Boolean values.
In practice, all operators defined as index access method
strategies must return type boolean, since they must
appear at the top level of a WHERE clause to be used with an index.
(On the other hand, support functions return whatever the
particular access method expects -- in the case of the comparison
function for B-trees, a signed integer.)
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;
