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 ] )
This parameter takes the name (or symbol) of the new operator. Read further for a list of valid characters to use.
Use this parameter to define what function will handle the new operator.
This parameter takes the left-hand argument type (if a left-hand argument exists). Do not use this option with a left-unary operator.
This parameter takes the right-hand argument type (if a right-hand argument exists). Do not use this option with a right-unary operator.
This parameter takes the commutator of the new operator.
This parameter takes the negator of the new operator.
Use this parameter to specify the name of the restriction selectivity estimator function for the new operator.
This parameter takes the name of the join selectivity estimator function for the new operator.
Use this keyword to indicate that the new operator can support a hash join.
If the operator can support a merge join, use this parameter to specify the operator that sorts the left-hand data type.
If the operator can support a merge join, use this parameter to specify the operator that sorts the right-hand data type.
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:
Exclamation mark (!)
At symbol (@)
Number symbol (#)
Percent sign (%)
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 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 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.
The func_name you specify 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. 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.
As of PostgreSQL 6.5, you can also have both operators refering to each other.
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.
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 = + );