8th September 2022: PostgreSQL 15 Beta 4 Released!
Unsupported versions: 7.0 / 6.5 / 6.4
This documentation is for an unsupported version of PostgreSQL.
You may want to view the same page for the current version, or one of the other supported versions listed above instead.

Chapter 41. Extending SQL: Operators

Postgres supports left unary, right unary and binary operators. Operators can be overloaded, or re-used with different numbers and types of arguments. If there is an ambiguous situation and the system cannot determine the correct operator to use, it will return an error and you may have to typecast the left and/or right operands to help it understand which operator you meant to use. To create an operator for adding two complex numbers can be done as follows. First we need to create a function to add the new types. Then, we can create the operator with the function.

         CREATE FUNCTION complex_add(complex, complex)
            RETURNS complex
            AS '$PWD/obj/complex.so'
            LANGUAGE 'c';

         CREATE OPERATOR + (
            leftarg = complex,
            rightarg = complex,
            procedure = complex_add,
            commutator = +

We've shown how to create a binary operator here. To create unary operators, just omit one of leftarg (for left unary) or rightarg (for right unary). If we give the system enough type information, it can automatically figure out which operators to use.

         SELECT (a + b) AS c FROM test_complex;

         |c               |
         |(5.2,6.05)      |
         |(133.42,144.95) |