Re: operators and indexes

Brook Milligan <brook@biology.nmsu.edu> writes:
> I have defined various operators for some user defined types.  How do
> I use them for setting up an index on the types?

> The naive approach yields the following

>     ERROR:  DefineIndex: type geocentric_point has no default operator class

> so presumably one must define a default operator.  How is this done
> and what exactly are the semantics of a default operator?

You parsed it wrong, what you need is an index "operator class" for the
type, which you then mark as being the default opclass for the type.
The operator class is just an abstract concept that binds together a
specific set of index-related operators and functions for a particular
datatype.

To support btree indexes on a datatype, you need a 3-way comparison
function for the type, plus all six standard relational operators for
the type (< = > <= <> >=).  Then define an index opclass for the type
(in pg_opclass) and make entries associating it to the comparison
function (in pg_amproc) and to the relational operators (in pg_amop).
There is a tutorial about this in the SGML docs, or see the existing
entries for any standard datatype.

The motivation for opclasses is that you might have more than one
reasonable ordering for a datatype; if so, you can associate multiple
opclasses with the type so that different indexes can follow different
orderings.  For example, a complex-number datatype might possibly want
opclasses associated with absolute-value ordering and with real-part
ordering.  I can imagine multiple opclasses for a point datatype too...
        regards, tom lane