CREATE OPERATOR

Description

Use the CREATE OPERATOR command to define a new operator. The PostgreSQL user that creates the operator becomes the operator owner when creation is successful.

The operator name is a character sequence up to 31 characters in length. Characters in the operator name must be within the following list of accepted characters:

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

There are some restrictions on allowed character sequences for the name:

• The dollar sign ($) is only allowed within an operator name consisting of multiple characters. It cannot be specified as single-character operator name.

• The double-dash (- -) and the forward slash and star (/*) character combinations cannot appear anywhere in an operator name, as they will be interpreted as the start of a comment.

• A multiple character operator cannot end with a plus sign (+) or dash (-), unless the name also contains at least one of the following characters:

  • Tilde (~)

  • Exclamation mark (!)

  • At symbol (@)

  • Number symbol (#)

  • Percent sign (%)

  • Caret (^)

  • Ampersand (&)

  • Pipe (|)

  • Backtick (`)

  • Question mark (?)

  • Dollar sign ($)

  These restrictions on operator names let PostgreSQL parse syntactically valid queries without requiring the user to separate tokens with spaces (or other characters).

When you create an operator , you must include at least one LEFTARG or one RIGHTARG (as the operator must take an argument). If you are defining a binary operator (one which operators on a value to the left and right of the operator), both the LEFTARG and RIGHTARG must be specified. If you are creating a right unary operator, you will only need to define LEFTARG; likewise, when creating a left unary operator, you will only need to define RIGHTARG.

For the query optimizer to correctly reverse the order of operands, it needs to know what the commutator operator is (if it exists). For some operators, a commutator should exist (or at least, the existence of one would make sense). For example, the commutator of the greater-than symbol (>) is the less-than (<) symbol, and it makes sense that both of these should exist, and be related to one another in this fashion. With this information, your operator order can easily be reversed, changing something like x < y to y > x if the query optimizer finds it to be more efficient.

In the same way that specifying the commutator operator can help the optimizer, so can specifying a negator operator (if one exists). The negator to the equals sign (=) is !=, signifying not-equals or not-equivalent. When a negator is specified, the query optimizer can simplify statements like this:

booktown=# SELECT * FROM employees WHERE NOT name = 'John';

This simplified form of this query, using the negator operator, is:

booktown=# SELECT * FROM employees WHERE name != 'John';

Note that you may technically specify an operator's commutator or negator operator to PostgreSQL before that operator actually exists. This is to allow you to create two operators which reference one another. Exercise care, however, in remembering to create the appropriate operator to fit the definition of your commutator or negator if it does not yet exist.

Use the HASH option to indicate to the query optimizer that an operator can support the use of a hash-join algorithm (usable only if the operator represents equality tests where equality of the type also means bitwise equality of the representation of the type). Likewise, use SORT1 and SORT2 to inform the optimizer that, respectively, the left and right side operators can support a merge-sort. These sorting operators should only be given for an equality operator, and when they are given they should be represented by less-than and greater-than symbols for the left- and right-hand data types, respectively.

The RESTRICT and JOIN parameters provide functions that assist the query optimizer with estimation of result set sizes. With some query conditions, it may be necessary for PostgreSQL to estimate the number of results an action with operators may produce; in these situations, the query optimizer will call the res_proc function with the same arguments passed to the operator, then use the number returned by that function to estimate how many results there will be. Because of this, the res_proc function must already be defined using CREATE_FUNCTION and must accept the correct arguments.

The function specified with JOIN is used to help with estimation of the size of a join when the operands of an operator are not constant values. The query optimizer uses the floating point number returned from join_proc to compute the estimated size of the result.

Examples

The following example defines a non-zero boolean operator for the integer type, using the is_zero function (see Chapter 7 for more on this function and its definition):

booktown=# CREATE OPERATOR !# (PROCEDURE = is_zero,
booktown(#                     LEFTARG = integer,
booktown(#                     RIGHTARG = integer);
CREATE