Indexing Custom Data Types

At this point, you have a reasonably complete custom data type. You can create and display FCUR values, store them in a table, compare two FCUR values, and convert them to (and from) other data types. But you're missing one important feature: You can't create an index that includes an FCUR value. Once you have all of the comparison operators (<, <=, =, >=, and so on) in place you are two short steps away.

To index values of a given data type, you must create an operator class that tells PostgreSQL which operators it should use for that type. You may recall from Chapter 3, “PostgreSQL Syntax and Use,” that PostgreSQL supports a number of index types (B-tree, hash, R-tree, and GiST). Each index type requires a different set of operators. For example, to build a B-tree index, PostgreSQL can make use of five different operators: <, <=, =, >=, and >. Before you can create an operator class that PostgreSQL can use to build B-tree indexes over FCUR values, you'll need one more function.

Listing 6.18 shows the fcur_cmp() function that compares two FCUR values:

Listing 6.18. fcur.c (Part 10)

305 PG_FUNCTION_INFO_V1(fcur_cmp);
306
307 Datum fcur_cmp(PG_FUNCTION_ARGS)
308 {
309     fcur * left      = (fcur *)PG_GETARG_POINTER(0);
310     fcur * right     = (fcur *)PG_GETARG_POINTER(1);
311     double left_dbl  = normalize( left );
312     double right_dbl = normalize( right );
313
314     if( left_dbl > right_dbl )
315       PG_RETURN_INT32( 1 );
316     else if( left_dbl < right_dbl )
317       PG_RETURN_INT32 ( -1 );
318     else
319       PG_RETURN_INT32 ( 0 );
320 }

PostgreSQL will call fcur_cmp() repeatedly as it builds a B-tree index. fcur_cmp() expects two arguments, both of type fcur. After normalizing the values, fcur_cmp() returns +1 if the first argument is greater than the second, -1 if the second argument is greater than the first, or 0 if the arguments are equal.

Don't forget to tell PostgreSQL how to find this function:

CREATE OR REPLACE FUNCTION FCUR_CMP( FCUR, FCUR )
   RETURNS INT4
   AS 'fcur.so'
   LANGUAGE 'C'
   IMMUTABLE STRICT;

Now you have all of the pieces in place; you can create an operator class with the following command:

CREATE OPERATOR CLASS fcur_ops
  DEFAULT FOR TYPE fcur USING BTREE as
    OPERATOR 1 <,
    OPERATOR 2 <=,
    OPERATOR 3 =,
    OPERATOR 4 >=,
    OPERATOR 5 >,
    FUNCTION 1 fcur_cmp( fcur, fcur );

That's it; once you've created an operator class for type FCUR, you can create an index that includes values of that type. To create an operator class for the other index types (hash, R-tree, and GiST), you'll have to create a few more support functions. See section 31 (“Extending SQL”) of the PostgreSQL reference documentation for more details.