Creating new operators

The first thing we must do is come up with the desired operators. Note that five operators are needed. There is one operator for each strategy. A strategy of an index is really like a plugin that allows you to put in your own code.

Before getting started, I have compiled some test data:

CREATE TABLE t_sva (sva text); 

INSERT INTO t_sva VALUES ('1118090878');  
INSERT INTO t_sva VALUES ('2345010477'); 

Now that the test data is there, it is time to create an operator. For this purpose, PostgreSQL offers the CREATE OPERATOR command:

test=# \h CREATE OPERATOR  
Command: CREATE OPERATOR  
Description: define a new operator  
Syntax: 
CREATE OPERATOR name ( 
    PROCEDURE = function_name 
    [, LEFTARG = left_type ] [, RIGHTARG = right_type ] 
    [, COMMUTATOR = com_op ] [, NEGATOR = neg_op ] 
    [, RESTRICT = res_proc ] [, JOIN = join_proc ] 
    [, HASHES ] [, MERGES ] 
)

URL: https://www.postgresql.org/docs/12/sql-createoperator.html

Basically, the concept is as follows: operator calls a function, which gets one or two parameters; one for the left argument and one for the right argument of operator.

As you can see, an operator is nothing more than a function call. So, consequently, it is necessary to implement the logic needed in those functions that are hidden by the operators. In order to fix the sort order, I have written a function called normalize_si:

CREATE OR REPLACE FUNCTION normalize_si(text) RETURNS text AS $$ 
        BEGIN 
        RETURN substring($1, 9, 2) || 
               substring($1, 7, 2) ||
               substring($1, 5, 2) ||
               substring($1, 1, 4); 
        END; $$ 
LANGUAGE 'plpgsql' IMMUTABLE;

Calling this function will return the following result:

test=# SELECT normalize_si('1118090878'); 
 normalize_si 
-------------- 
 7808091118
(1 row) 

As you can see, all we did is swap some digits. It is now possible to just use the normal string sort order. In the next step, this function can already be used to compare social security numbers directly.

The first function that's needed is the less than function, which is needed by the first strategy:

CREATE OR REPLACE FUNCTION si_lt(text, text) RETURNS boolean AS $$ 
        BEGIN 
             RETURN normalize_si($1) < normalize_si($2); 
        END; 
$$ LANGUAGE 'plpgsql' IMMUTABLE; 

There are two important things to note here:

• The function must not be written in SQL. It only works in a procedural or compiled language. The reason for this is that SQL functions can be inlined under some circumstances, and this would cripple the entire endeavor.
• The second issue is that you should stick to the naming convention used in this chapter—it is widely accepted by the community. Less than functions should be called _lt, less than or equal to functions should be called _le, and so on.

Given this knowledge, the next set of functions that are needed by our future operators can be defined:

-- lower equals 
CREATE OR REPLACE FUNCTION si_le(text, text)  
  RETURNS boolean AS  
$$  
  BEGIN 
    RETURN normalize_si($1) <= normalize_si($2);  
  END; 
$$  
LANGUAGE 'plpgsql' IMMUTABLE; 
 
-- greater equal 
CREATE OR REPLACE FUNCTION si_ge(text, text)  
  RETURNS boolean AS  
$$  
BEGIN 
  RETURN normalize_si($1) >= normalize_si($2);  
END; 
$$  
LANGUAGE 'plpgsql' IMMUTABLE; 
 
-- greater 
CREATE OR REPLACE FUNCTION si_gt(text, text)  
  RETURNS boolean AS  
$$  
BEGIN 
  RETURN normalize_si($1) > normalize_si($2);  
END; 
$$  
LANGUAGE 'plpgsql' IMMUTABLE; 

So far, four functions have been defined. A fifth function for the equals operator is not necessary. We can simply take the existing operator because equals does not depend on sort order anyway.

Now that all the functions are in place, it is time to define these operators:

-- define operators 
CREATE OPERATOR <# ( PROCEDURE=si_lt, 
                     LEFTARG=text, 
                     RIGHTARG=text); 

The design of the operator is actually very simple. The operator needs a name (in my case, <#), a procedure, which is supposed to be called, and the data type of the left and the right argument. When the operator is called, the left argument will be the first parameter of si_lt, and the right argument will be the second argument.

The remaining three operators follow the same principle:

CREATE OPERATOR <=# ( PROCEDURE=si_le, 
                      LEFTARG=text, 
                      RIGHTARG=text);

CREATE OPERATOR >=# ( PROCEDURE=si_ge, 
                      LEFTARG=text, 
                      RIGHTARG=text);

CREATE OPERATOR ># ( PROCEDURE=si_gt, 
                     LEFTARG=text, 
                     RIGHTARG=text);

Depending on the type of index you are using, a couple of support functions are needed. In the case of standard B-trees, there is only one support function needed, which is used to speed things up internally:

CREATE OR REPLACE FUNCTION si_same(text, text) RETURNS int AS $$
        BEGIN 
                IF normalize_si($1) < normalize_si($2) 
                THEN 
                        RETURN -1; 
                ELSIF normalize_si($1) > normalize_si($2) 
                THEN 
                        RETURN +1; 
                ELSE 
                        RETURN 0; 
                END IF; 
        END; 
$$ LANGUAGE 'plpgsql' IMMUTABLE;

The si_same function will either return -1 if the first parameter is smaller, 0 if both parameters are equal, and 1 if the first parameter is greater. Internally, the _same function is the workhorse, so you should make sure that your code is optimized.