Create an absolute-value operator class

As an example, suppose you want to define a new ordering for integers. The lexicographical sequence of the default B-tree operator class orders integers numerically: -4 < -3 < -2 < -1 < 0 < 1 < 2 < 3. Instead, you might want the numbers -4, 2, -1, -3 to appear in order of absolute value.

-1, 2, -3, -4

To obtain the absolute-value order, you must define external functions that treat negative integers as positive integers. The following steps create a new operator class called abs_btree_ops with strategy and support functions that provide the absolute-value order.

1. Write and register external functions for the new strategy functions: abs_lessthan(), abs_lessthanorequal(), abs_equal(), abs_greaterthan(), and abs_greaterthanorequal().

   For more information, refer to Develop a user-defined routine.

2. Register the five new strategy functions with the CREATE FUNCTION statement.
3. Write the C function for the new support function: abs_compare().

   Compile this function and store it in the absbtree.so shared-object file.

4. Register the new support function with the CREATE FUNCTION statement.
5. Create the new abs_btree_ops operator class for the B-tree secondary-access method.

You can now create a B-tree index on an INTEGER column and associate the new operator class with this column:

CREATE TABLE cust_tab
(
   cust_name varchar(20), 
   cust_num integer
   ...
);
CREATE INDEX c_num1_ix
   ON cust_tab (cust_num abs_btree_ops);

The c_num1_ix index uses the new operator class, abs_btree_ops, for the cust_num column. An end user can now use the absolute value functions in SQL statements, as in the following example:

SELECT * FROM cust_tab WHERE abs_lt(cust_num, 7)

The default operator class is still available for indexes. The following CREATE INDEX statement defines a second index on the cust_num column:

CREATE INDEX c_num2_ix ON cust_tab (cust_num);

The c_num2_ix index uses the default operator class, btree_ops, for the cust_num column. The following query uses the operator function for the default less than (<) operator:

SELECT * FROM cust_tab WHERE lessthan(cust_num, 7)