Now, let's take look at how we can add a new operator in PostgreSQL. Adding new operators is not too different from adding new functions. In fact, an operator is syntactically just a different way to use an existing function. For example, the + operator calls a built-in function called numeric_add and passes it the two arguments.
When you define a new operator, you must define the data types that the operator expects as arguments and define which function is to be called.
Let's take a look at how to define a simple operator. You have to use the CREATE OPERATOR command to create an operator.
In Chapter 2, Server Programming Environments, we wrote a function to calculate the Fibonacci number of a given integer. Let's use that function to create a new Fibonacci operator, ##, which will have an integer on its left-hand side:
CREATE OPERATOR ## (PROCEDURE=fib, LEFTARG=integer);
Now, you can use this operator in your SQL to calculate a Fibonacci number:
testdb=# SELECT 12##;
?column?
----------
144
(1 row)Note that we defined that the operator will have an integer on the left-hand side. If you try to put a value on the right-hand side of the operator, you will get an error:
postgres=# SELECT ##12;
ERROR: operator does not exist: ## integer at character 8
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.
STATEMENT: select ##12;
ERROR: operator does not exist: ## integer
LINE 1: select ##12;
^
HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.Operators can be overloaded in the same way as functions. This means, that an operator can have the same name as an existing operator but with a different set of argument types. More than one operator can have the same name, but two operators can't share the same name if they accept the same types and positions of the arguments. As long as there is a function that accepts the same kind and number of arguments that an operator defines, it can be overloaded.
Let's override the ## operator we defined in the last example, and also add the ability to provide an integer on the right-hand side of the operator:
CREATE OPERATOR ## (PROCEDURE=fib, RIGHTARG=integer);
Now, running the same SQL, which resulted in an error last time, should succeed, as shown here:
testdb=# SELECT ##12; ?column? ---------- 144 (1 row)
You can drop the operator using the DROP OPERATOR command.
Note
You can read more about creating and overloading new operators in the PostgreSQL documentation at http://www.postgresql.org/docs/current/static/sql-createoperator.html and http://www.postgresql.org/docs/current/static/xoper.html.
There are several optional clauses in the operator definition that can optimize the execution time of the operators by providing information about operator behavior. For example, you can specify the commutator and the negator of an operator that help the planner use the operators in index scans. You can read more about these optional clauses at http://www.postgresql.org/docs/current/static/xoper-optimization.html.
Since this chapter is just an introduction to the additional extensibility capabilities of PostgreSQL, we will just introduce a couple of optimization options; any serious production quality operator definitions should include these optimization clauses, if applicable.
The optional clauses tell the PostgreSQL server about how the operators behave. These options can result in considerable speedups in the execution of queries that use the operator. However, if you provide these options incorrectly, it can result in a slowdown of the queries. Let's take a look at two optimization clauses called commutator and negator.
This clause defines the commuter of the operator. An operator A is a commutator of operator B if it fulfils the following condition:
x A y = y B x.
It is important to provide this information for the operators that will be used in indexes and joins. As an example, the commutator for > is <, and the commutator of = is = itself.
This helps the optimizer to flip the operator in order to use an index. For example, consider the following query:
SELECT * FROM employee WHERE new_salary > salary;
If the index is defined on the salary column, then PostgreSQL can rewrite the preceding query as shown:
SELECT * from employee WHERE salary < new_salary
This allows PostgreSQL to use a range scan on the index column salary. For a user-defined operator, the optimizer can only do this flip around if the commutator of a user-defined operator is defined:
CREATE OPERATOR > (LEFTARG=integer, RIGHTARG=integer, PROCEDURE=comp, COMMUTATOR = <)
The negator clause defines the negator of the operator. For example, <> is a negator of =. Consider the following query:
SELECT * FROM employee WHERE NOT (dept = 10);
Since <> is defined as a negator of =, the optimizer can simplify the preceding query as follows:
SELECT * FROM employee WHERE dept <> 10;
You can even verify that using the EXPLAIN command:
postgres=# EXPLAIN SELECT * FROM employee WHERE NOT dept = 'WATER MGMNT';
QUERY PLAN
---------------------------------------------------------
Foreign Scan on employee (cost=0.00..1.10 rows=1 width=160)
Filter: ((dept)::text <> 'WATER MGMNT'::text)
Foreign File: /Users/usamadar/testdata.csv
Foreign File Size: 197
(4 rows)