CREATE OPERATOR

Name

CREATE OPERATOR -- Defines a new operator within the database.

Synopsis

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 ] )

Parameters

name

The character sequence of the new operator. Read further for a list of valid characters to use in names and symbols.

func_name

The function that implements the the new operator.

type1

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.

type2

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.

com_op

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.

neg_op

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.

res_proc

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.

join_proc

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.

HASHES

The HASHES keyword, which indicates that the new operator can support a hash join.

left_sort_op

The operator that sorts left-hand values, if the new operator can support a merge join.

right_sort_op

The operator that sorts right-hand values, if the new operator can support a merge join.

Results

CREATE

The message returned when a new operator is created successfully.

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:

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.

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