Creating the Operators and Support Routines

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;

Home
Online Resources
General
Beginner Tutorials
MySQL
PostgreSQL
Oracle
mSQL
Microsoft SQL
Contact Us
Random quote of the moment:
Don't get stuck in a closet -- wear yourself out.
 
http://www.sql.org/
 
Proudly designed and hosted by OmegaSphere, providers of all your Web Hosting, SSL Certificate, and Domain Name needs!
Paid advertisements: Free Electronics