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##;...