The procedures described thus far let you define new types, new functions, and new operators. However, we cannot yet define a secondary index (such as a B-tree, R-tree, or hash access method) over a new type, nor associate operators of a new type with secondary indexes. To do these things, we must define an operator class for the new datatype. We will describe operator classes in the context of a running example: a new operator class for the B-tree access method that stores and sorts complex numbers in ascending absolute value order. Prior to PostgreSQL release 7.3, it was necesssary to make manual additions to pg_amop, pg_amproc, and pg_opclass in order to create a user-defined operator class. That approach is now deprecated in favor of using CREATE OPERATOR CLASS, which is a much simpler and less error-prone way of creating the necessary catalog entries. The pg_am table contains one row for every index access method. Support for access to regular tables is built into PostgreSQL, but all index access methods are described in pg_am. It is possible to add a new index access method by defining the required interface routines and then creating a row in pg_am --- but that is far beyond the scope of this chapter. The routines for an index access method do not directly know anything about the data types the access method will operate on. Instead, an operator class identifies the set of operations that the access method needs to be able to use to work with a particular data type. Operator classes are so called because one thing they specify is the set of WHERE-clause operators that can be used with an index (ie, can be converted into an indexscan qualification). An operator class may also specify some support procedures that are needed by the internal operations of the index access method, but do not directly correspond to any WHERE-clause operator that can be used with the index. It is possible to define multiple operator classes for the same input datatype and index access method. By doing this, multiple sets of indexing semantics can be defined for a single datatype. For example, a B-tree index requires a sort ordering to be defined for each datatype it works on. It might be useful for a complex-number datatype to have one B-tree operator class that sorts the data by complex absolute value, another that sorts by real part, and so on. Typically one of the operator classes will be deemed most commonly useful and will be marked as the default operator class for that datatype and index access method. The same operator class name can be used for several different access methods (for example, both B-tree and hash access methods have operator classes named oid_ops), but each such class is an independent entity and must be defined separately. The operators associated with an operator class are identified by strategy numbers, which serve to identify the semantics of each operator within the context of its operator class. For example, B-trees impose a strict ordering on keys, lesser to greater, and so operators like less than and greater than or equal to are interesting with respect to a B-tree. Because PostgreSQL allows the user to define operators, PostgreSQL cannot look at the name of an operator (e.g., > or <) and tell what kind of comparison it is. Instead, the index access method defines a set of strategies, which can be thought of as generalized operators. Each operator class shows which actual operator corresponds to each strategy for a particular datatype and interpretation of the index semantics. B-tree indexes define 5 strategies, as shown in . B-tree Operation Strategy Number less than 1 less than or equal 2 equal 3 greater than or equal 4 greater than 5 Hash indexes express only bitwise similarity, and so they define only 1 strategy, as shown in . Hash Operation Strategy Number equal 1 R-tree indexes express rectangle-containment relationships. They define 8 strategies, as shown in . R-tree Operation Strategy Number left of 1 left of or overlapping 2 overlapping 3 right of or overlapping 4 right of 5 same 6 contains 7 contained by 8 GiST indexes are even more flexible: they do not have a fixed set of strategies at all. Instead, the consistency support routine of a particular GiST operator class interprets the strategy numbers however it likes. By the way, the amorderstrategy column in pg_am tells whether the access method supports ordered scan. Zero means it doesn't; if it does, amorderstrategy is the strategy number that corresponds to the ordering operator. For example, B-tree has amorderstrategy = 1, which is its less than strategy number. In short, an operator class must specify a set of operators that express each of these semantic ideas for the operator class's datatype. Strategies aren't usually enough information for the system to figure out how to use an index. In practice, the access methods require additional support routines in order to work. For example, the B-tree access method must be able to compare two keys and determine whether one is greater than, equal to, or less than the other. Similarly, the R-tree access method must be able to compute intersections, unions, and sizes of rectangles. These operations do not correspond to operators used in qualifications in SQL queries; they are administrative routines used by the access methods, internally. Just as with operators, the operator class identifies which specific functions should play each of these roles for a given datatype and semantic interpretation. The index access method specifies the set of functions it needs, and the operator class identifies the correct functions to use by assigning support function numbers to them. B-trees require a single support function, as shown in . B-tree Operation Support Number Compare two keys and return -1, 0, or +1, depending on whether the first key is less than, equal to, or greater than the second. (Actually, this routine can return any negative int32 value (< 0), zero, or any non-zero positive int32 value (> 0).) 1 Hash indexes likewise require one support function, as shown in . Hash Operation Support Number compute the hash value for a key 1 R-tree indexes require three support functions, as shown in . R-tree Operation Support Number union 1 intersection 2 size 3 GiST indexes require seven support functions, as shown in . GiST Operation Support Number consistent 1 union 2 compress 3 decompress 4 penalty 5 picksplit 6 equal 7 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 . 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 for the rest of the examples. 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; Now that we have the required operators and support routine, we can finally create 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); And we're done! (Whew.) 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 datatype 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. There are two special features of operator classes that we have not discussed yet, mainly because they are not very useful with the default B-tree index access method. Normally, declaring an operator as a member of an operator class means that the index access method can retrieve exactly the set of rows that satisfy a WHERE condition using the operator. For example, SELECT * FROM table WHERE integer_column < 4; can be satisfied exactly by a B-tree index on the integer column. But there are cases where an index is useful as an inexact guide to the matching rows. For example, if an R-tree index stores only bounding boxes for objects, then it cannot exactly satisfy a WHERE condition that tests overlap between nonrectangular objects such as polygons. Yet we could use the index to find objects whose bounding box overlaps the bounding box of the target object, and then do the exact overlap test only on the objects found by the index. If this scenario applies, the index is said to be lossy for the operator, and we mark the OPERATOR clause in the CREATE OPERATOR CLASS command with RECHECK. RECHECK is valid if the index is guaranteed to return all the required tuples, plus perhaps some additional tuples, which can be eliminated by performing the original operator comparison. Consider again the situation where we are storing in the index only the bounding box of a complex object such as a polygon. In this case there's not much value in storing the whole polygon in the index entry --- we may as well store just a simpler object of type box. This situation is expressed by the STORAGE option in CREATE OPERATOR CLASS: we'd write something like CREATE OPERATOR CLASS polygon_ops DEFAULT FOR TYPE polygon USING gist AS ... STORAGE box; At present, only the GiST access method supports a STORAGE type that's different from the column datatype. The GiST compress and decompress support routines must deal with datatype conversion when STORAGE is used.