Mastering PostgreSQL 10

Creating new operators

The first thing that has to be done 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 ...