Red Hat Database: Administrator and User's Guide | ||
---|---|---|
Prev | Chapter 15. Interfacing Extensions To Indexes | Next |
So now we have an access method and an operator class. We still need a set of operators. The procedure for defining operators is discussed in the Red Hat Database SQL Guide and Reference. For the complex_abs_ops operator class on Btrees, the operators we require are:
Suppose the code that implements the functions defined is stored in the file $PGROOT/src/tutorial/complex.c
Part of the C code looks like this: (note that we will show only the equality operator for the rest of the examples. The other four operators are very similar. Refer to complex.c or complex.source for the details.)
#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); } |
We make the function known to PostgreSQL:
CREATE FUNCTION complex_abs_eq(complex, complex) RETURNS bool AS '$PGROOT/src/tutorial/complex.so' LANGUAGE 'c'; |
There are some important things to note:
Operators for less-than, less-than-or-equal, equal, greater-than-or-equal, and greater-than for complex are being defined. We can have only one operator named, say, = and taking type complex for both operands. In this case we do not have any other operator = for complex, but if we were building a practical datatype we would probably want = to be the ordinary equality operation for complex numbers. In that case, we would need to use some other operator name for complex_abs_eq.
Although PostgreSQL can cope with operators having the same name as long as they have different input datatypes, C can cope only with one global routine having a given name. Therefore, we should not name the C function something simple like abs_eq. Usually it is a good practice to include the datatype name in the C function name, so as not to conflict with functions for other datatypes.
We could have made the PostgreSQL name of the function abs_eq, relying on PostgreSQL to distinguish it by input datatypes from any other functions 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. The access methods rely on this fact. (On the other hand, the support function returns whatever the particular access method expects -- in this case, a signed integer.) The final routine in the file is the "support routine" mentioned when we discussed the amsupport column of the pg_am table. We will use this later on.
Now we are ready to define the operators:
CREATE OPERATOR = ( leftarg = complex, rightarg = complex, procedure = complex_abs_eq, restrict = eqsel, join = eqjoinsel ) |
The next step is to add entries for these operators to the pg_amop relation. To do this, we will need the oids of the operators we just defined. We will look up the names of all the operators that take two complexes, and pick the ones that we want:
SELECT o.oid AS opoid, o.oprname INTO TABLE complex_ops_tmp FROM pg_operator o, pg_type t WHERE o.oprleft = t.oid and o.oprright = t.oid and t.typname = 'complex'; opoid | oprname --------+--------- 277963 | + 277970 | < 277971 | <= 277972 | = 277973 | >= 277974 | > (6 rows) |
Now we are ready to update pg_amop with our new operator class. The most important thing in this entire discussion is that the operators are ordered, from less than through greater than, in pg_amop. We add the rows we need:
INSERT INTO pg_amop (amopid, amopclaid, amopopr, amopstrategy) SELECT am.oid, opcl.oid, c.opoid, 1 FROM pg_am am, pg_opclass opcl, complex_ops_tmp c WHERE amname = 'btree' AND opcname = 'complex_abs_ops' AND c.oprname = '<'; |
The field amopreqcheck is not discussed here; it should always be false for B-tree operators.
The next step is registration of the "support routine" previously described in our discussion of pg_am. The oid of this support routine is stored in the pg_amproc table, keyed by the access method oid and the operator class oid.
First, we need to register the function in PostgreSQL. (We put the C code that implements this routine in the bottom of the file in which we implemented the operator routines.):
CREATE FUNCTION complex_abs_cmp(complex, complex) RETURNS int4 AS '$PGROOT/src/tutorial/complex.so' LANGUAGE 'c'; SELECT oid, proname FROM pg_proc WHERE proname = 'complex_abs_cmp'; oid | proname --------+----------------- 277997 | complex_abs_cmp (1 row) |
INSERT INTO pg_amproc (amid, amopclaid, amproc, amprocnum) SELECT a.oid, b.oid, c.oid, 1 FROM pg_am a, pg_opclass b, pg_proc c WHERE a.amname = 'btree' AND b.opcname = 'complex_abs_ops' AND c.proname = 'complex_abs_cmp'; |
Now that we are done it should be possible to create and use btree indexes on complex columns.