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 ] )
The character sequence of the new operator. Read further for a list of valid characters to use in names and symbols.
The function that implements the the new operator.
The type of the left-hand argument. Do not use this option with a unary operator that always appears to the left of the value on which it operates.
The data type of the right-hand argument. Do not use this option with a unary operator that always appears to the right of the value on which it operates.
The commutator operator for the new operator. A commutator is another existing operator which executes the commutation (order reversal) of the procedure defined for the new operator, e.g., with the left argument treated as the right argument, and the right argument treated as the left argument.
The negator operator for the new operator. A negator is another existing operator which executes the literal inversion of the procedure define for the new operator. A negator should only be defined if the result of applying the NOT keyword to the new operator always results in the same output that the negator would return on the same condition.
The name of the restriction selectivity estimator function for the new operator. This function must already exist, must accept arguments of the same data types as defined for this new operator, and return a floating point value.
The name of the join selectivity estimator function for the new operator. This function must already exist, and must be defined to accept arguments of the same data types as defined for this new operator, and return a floating point value.
The HASHES keyword, which indicates that the new operator can support a hash join.
The operator that sorts left-hand values, if the new operator can support a merge join.
The operator that sorts right-hand values, if the new operator can support a merge join.
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:
Exclamation mark (!)
At symbol (@)
Number symbol (#)
Percent sign (%)
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).
Note: When using non-SQL standard (i.e., user-defined) operator names, you should make it a habit to separate adjacent operators with a space to clearly define your intended meaning.
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.
Note: The function you specify as the func_name parameter when creating an operator must have been defined to accept the correct number of arguments for that operator.
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.
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