Creating an operator

The CREATE OPERATOR SQL command creates a new operator. Here is the syntax for CREATE OPERATOR:

  CREATE OPERATOR 
name
 ( PROCEDURE = 
functionname

                  [, LEFTARG = 
type1
 ]
                  [, RIGHTARG = 
type2
 ]
                  [, COMMUTATOR = 
commutatorop
 ]
                  [, NEGATOR = 
negatorop
 ]
                  [, RESTRICT = 
restrictproc
 ]
                  [, JOIN = 
joinproc
 ]
                  [, HASHES ]
                  [, SORT1 = 
leftsortop
 ]
                  [, SORT2 = 
rightsortop
 ] )

In this syntax, name is the name of the new operator, and functionname is the name of the function to be called by the operator. The remaining clauses are all optional, though at least one of the LEFTARG or RIGHTARG clauses must be applied. Note that the operator name may only consist of the following accepted characters:

 + - * / < > = ~ ! @ # % ^ & | ` ? $

Specifying only the LEFTARG data type creates an operator that operates only on a value (e.g., a constant or identifier) to its left. Conversely, specifying only the RIGHTARG data type creates an operator that operates only on a value to its right. Specifying both a LEFTARG and RIGHTARG type results in an operator that operates on a value to both the left and right.

The factorial operator (!) is an example of a built-in operator that affects values to its left, while the addition operator (+) is a good example of an operator that affects values both on the left and right of the operator. Note that the functionname must accept the appropriate number of arguments as implied by the use of the LEFTARG and RIGHTARG keywords (either one or two arguments). Furthermore, the function's accepted argument types should match the relevant operator types defined by CREATE OPERATOR for each respective value to be operated on.

Example 7-53 creates an operator named !#, which passes the value to its left to the is_zero() function (defined in Example 7-49). This means that the syntax of value !# will be effectively identical to using the functional syntax of is_zero(value).

booktown=# 
CREATE OPERATOR !# (PROCEDURE = is_zero,

booktown(# 
                   LEFTARG = integer);

CREATE

The CREATE message returned by Example 7-53 indicates that the operator was successfully created. As with functions, any user connected to the database will be able to use the new operator. The operator is owned by the user who creates it, meaning that no other user may remove it (unless they have superuser rights). Example 7-54 demonstrates the use of the new !# operator to check for books that are out of stock in Book Town's stock table.

booktown=# 
SELECT * FROM stock WHERE stock !#;

    isbn    | cost  | retail | stock
------------+-------+--------+-------
 0394900014 | 23.00 |  23.95 |     0
 0451198492 | 36.00 |  46.95 |     0
 0451457994 | 17.00 |  22.95 |     0
(3 rows)

Overloading an operator

Operators may become overloaded in much the same way as functions. This means that an operator is created with the same name as an existing operator, but affects a different set of defined types. More than one operator may have the same name, although two operators may not share the same name if they accept the same argument definitions. As long as a function exists to accept the number and type of arguments implied by the type of operator defined, though, the operator may be overloaded.

Example 7-53 overloads the !# operator. The first CREATE OPERATOR statement creates a similar operator to the one created in Example 7-53. However, it specifies a RIGHTARG clause rather than a LEFTARG clause, resulting in a version of the operator with the same name which operates on an argument of type integer to the right of the operator, rather than the left. The second statement creates a third variant of the !# operator, which operates on both an argument to the left and right of the operator, simultaneously.

booktown=# 
CREATE OPERATOR !# (PROCEDURE = is_zero,

booktown(# 
                    RIGHTARG = integer);

CREATE
booktown=# 
CREATE OPERATOR !# (PROCEDURE = is_zero,

booktown(# 
                    LEFTARG = integer,

booktown(# 
                    RIGHTARG = integer);

CREATE

Example 7-55 overloads the !# operator with the same is_zero() function because the function itself was overloaded in Example 7-50 in the Section called Creating C functions ," earlier in this chapter. As there are two copies of the is_zero() function--one that accepts one argument, and one that accepts two--the !# operator can be safely overloaded to accept both a single argument (on either the left or right side), as well as to accept parameters from both sides.

Once overloaded, PostgreSQL's parser can correctly interpret each defined operator in your SQL statements. Example 7-56 demonstrates three uses of the same operator, with different left and right arguments. Each use is valid, as the operator was overloaded in Example 7-55.

booktown=# 
SELECT isbn, stock FROM stock

booktown-# 
                   WHERE stock !#

booktown-# 
                   AND !# stock

booktown-# 
                   AND stock !# stock;

    isbn    | stock
------------+-------
 0394900014 |     0
 0451198492 |     0
 0451457994 |     0
(3 rows)

Dropping an operator

An operator can be dropped with the DROP OPERATOR command. An operator may only be dropped by the user who created it, or by a PostgreSQL superuser.

As operators are defined by their arguments as well as their name, DROP OPERATOR requires that you specify the left and right argument types of the operator. If there is no type for a given side, specify the NONE keyword.

Here is the syntax for DROP OPERATOR:

  DROP OPERATOR 
name
 ( { 
lefttype
 | NONE } , { 
righttype
 | NONE } )

Example 7-57 drops the variant of the !# operator that affects both left and right arguments.

booktown=# 
DROP OPERATOR !# (integer, integer);

DROP

Example 7-58 uses very similar syntax to Example 7-57, but drops the overloaded operator that affects only arguments to the right of the operator.

booktown=# 
DROP OPERATOR !# (NONE, integer);

DROP