CREATE OPERATOR

CREATE OPERATOR name ( PROCEDURE = func_name
     [, LEFTARG = type1 ] [, RIGHTARG = type2 ]
     [, COMMUTATOR = com_op ] [, NEGATOR = neg_op ]
     [, RESTRICT = res_proc ] [, JOIN = join_proc ]
     [, HASHES ] [, SORT1 = left_sort_op ] [, SORT2 = right_sort_op ] )
  

Description

Use the CREATE OPERATOR command to define a new operator named name. The user account that created the operator will become the operator owner when creation is complete.

The operator name is a sequence of length NAMEDATALEN-1 (by default, this constant is 31 characters). The characters can be any of the following accepted operators:

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

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

• The dollar sign ($) and colon (:) are only allowed within an operator name consisting of multiple characters. Neither of them can be specified as single-character operator names.

• 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 or comment block.

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

  • Tilde (~)

  • Exclamation mark (!)

  • At symbol (@)

  • Number symbol (#)

  • Percent sign (%)

  • Caret (^)

  • Ampersand (&)

  • Pipe (|)

  • Backtick (`)

  • Questionmark (?)

  • Dollar sign ($)

  • Colon (:)

  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 it is required that there is at least one LEFTARG or one RIGHTARG (as the operator must take an argument). If you are defining a binary operator, both the LEFTARG and RIGHTARG must be defined. 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. With this information, your operand order can easily be reversed, changing something like x < y to y > x.

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

SELECT * FROM employees WHERE NOT name = 'Andrew';

To something like the following:

SELECT * FROM employees WHERE name != 'Andrew';

When creating two operators that will use each other as commutators or negators, the first operator should be defined without a commutator or negator (whichever is appropriate). When you define the second operator, specify that the first operator be used as the commutator or negator. When this happens, PostgreSQL will update the first operator and the second operator will become either the commutator or negator of the first.

Use the HASH option to indicate to the query optimizer that this operator can support the use of a hash-join algorithm (only usable 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 sizes. With some qualifications it may be neccessary 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 to estimate how many results there will be. Because of this, the res_proc function must already be defined through 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 both contain instance variables. The query optimizer uses the floating point number returned from join_proc to compute the estimated size of the result.

Examples

The following example demonstrates the definition of an addition operator for the simple type (a type we have previously created).

CREATE OPERATOR + ( 
   LEFTARG = simple,
   RIGHTARG = simple, 
   PROCEDURE = add_simple,
   commutator = +
);