CREATE OPERATOR - Defines a new user operator
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 ] )
INPUTS
name The operator to be defined. See below for allowable
characters.
func_name
The function used to implement this operator.
type1 The type of the left-hand argument of the operator,
if any. This option would be omitted for a left-
unary operator.
type2 The type of the right-hand argument of the opera
tor, if any. This option would be omitted for a
right-unary operator.
com_op The commutator of this operator.
neg_op The negator of this operator.
res_proc
The restriction selectivity estimator function for
this operator.
join_proc
The join selectivity estimator function for this
operator.
HASHES Indicates this operator can support a hash join.
left_sort_op
If this operator can support a merge join, the
operator that sorts the left-hand data type of this
operator.
right_sort_op
If this operator can support a merge join, the
operator that sorts the right-hand data type of
this operator.
OUTPUTS
CREATE Message returned if the operator is successfully
DESCRIPTION
CREATE OPERATOR defines a new operator, name. The user
who defines an operator becomes its owner.
The operator name is a sequence of up to NAMEDATALEN-1 (31
by default) characters from the following list:
+ - * / < > = ~ ! @ # % ^ & | ` ? $
There are a few restrictions on your choice of name:
· "$" cannot be defined as a single-character operator,
although it can be part of a multi-character operator
name.
· "--" and "/*" cannot appear anywhere in an operator
name, since they will be taken as the start of a com
ment.
· A multi-character operator name cannot end in "+" or
"-", unless the name also contains at least one of these
characters:
~ ! @ # % ^ & | ` ? $
For example, @- is an allowed operator name, but *- is
not. This restriction allows Postgres to parse SQL-com
pliant queries without requiring spaces between tokens.
Note: When working with non-SQL-standard operator
names, you will usually need to separate adjacent
operators with spaces to avoid ambiguity. For
example, if you have defined a left-unary operator
named "@", you cannot write X*@Y; you must write X*
@Y to ensure that Postgres reads it as two operator
names not one.
The operator "!=" is mapped to "<>" on input, so these two
names are always equivalent.
At least one of LEFTARG and RIGHTARG must be defined. For
binary operators, both should be defined. For right unary
operators, only LEFTARG should be defined, while for left
unary operators only RIGHTARG should be defined.
The func_name procedure must have been previously defined
correct number of arguments (either one or two) of the
indicated types.
The commutator operator should be identified if one
exists, so that Postgres can reverse the order of the
operands if it wishes. For example, the operator area-
less-than, <<<, would probably have a commutator operator,
area-greater-than, >>>. Hence, the query optimizer could
freely convert:
box '((0,0), (1,1))' >>> MYBOXES.description
to
MYBOXES.description <<< box '((0,0), (1,1))'
This allows the execution code to always use the latter
representation and simplifies the query optimizer some
what.
Similarly, if there is a negator operator then it should
be identified. Suppose that an operator, area-equal, ===,
exists, as well as an area not equal, !==. The negator
link allows the query optimizer to simplify
NOT MYBOXES.description === box '((0,0), (1,1))'
to
MYBOXES.description !== box '((0,0), (1,1))'
If a commutator operator name is supplied, Postgres
searches for it in the catalog. If it is found and it does
not yet have a commutator itself, then the commutator's
entry is updated to have the newly created operator as its
commutator. This applies to the negator, as well. This is
to allow the definition of two operators that are the com
mutators or the negators of each other. The first operator
should be defined without a commutator or negator (as
appropriate). When the second operator is defined, name
the first as the commutator or negator. The first will be
updated as a side effect. (As of Postgres 6.5, it also
works to just have both operators refer to each other.)
The HASHES, SORT1, and SORT2 options are present to sup
port the query optimizer in performing joins. Postgres
can always evaluate a join (i.e., processing a clause with
a boolean) by iterative substitution [WONG76]. In addi
tion, Postgres can use a hash-join algorithm along the
lines of [SHAP86]; however, it must know whether this
strategy is applicable. The current hash-join algorithm is
only correct for operators that represent equality tests;
furthermore, equality of the data type must mean bitwise
equality of the representation of the type. (For example,
a data type that contains unused bits that don't matter
for equality tests could not be hashjoined.) The HASHES
flag indicates to the query optimizer that a hash join may
safely be used with this operator.
Similarly, the two sort operators indicate to the query
optimizer whether merge-sort is a usable join strategy and
which operators should be used to sort the two operand
classes. Sort operators should only be provided for an
equality operator, and they should refer to less-than
operators for the left and right side data types respec
tively.
If other join strategies are found to be practical, Post
gres will change the optimizer and run-time system to use
them and will require additional specification when an
operator is defined. Fortunately, the research community
invents new join strategies infrequently, and the added
generality of user-defined join strategies was not felt to
be worth the complexity involved.
The RESTRICT and JOIN options assist the query optimizer
in estimating result sizes. If a clause of the form:
MYBOXES.description <<< box '((0,0), (1,1))'
is present in the qualification, then Postgres may have to
estimate the fraction of the instances in MYBOXES that
satisfy the clause. The function res_proc must be a regis
tered function (meaning it is already defined using CREATE
FUNCTION) which accepts arguments of the correct data
types and returns a floating point number. The query opti
mizer simply calls this function, passing the parameter
((0,0), (1,1)) and multiplies the result by the relation
size to get the expected number of instances.
Similarly, when the operands of the operator both contain
instance variables, the query optimizer must estimate the
size of the resulting join. The function join_proc will
return another floating point number which will be multi
plied by the cardinalities of the two tables involved to
compute the expected result size.
The difference between the function
and the operator
MYBOXES.description === box '((0,0), (1,1))'
is that Postgres attempts to optimize operators and can
decide to use an index to restrict the search space when
operators are involved. However, there is no attempt to
optimize functions, and they are performed by brute force.
Moreover, functions can have any number of arguments while
operators are restricted to one or two.
NOTES
Refer to the chapter on operators in the PostgreSQL User's
Guide for further information. Refer to DROP OPERATOR to
delete user-defined operators from a database.
USAGE
The following command defines a new operator, area-equal
ity, for the BOX data type:
CREATE OPERATOR === (
LEFTARG = box,
RIGHTARG = box,
PROCEDURE = area_equal_procedure,
COMMUTATOR = ===,
NEGATOR = !==,
RESTRICT = area_restriction_procedure,
JOIN = area_join_procedure,
HASHES,
SORT1 = <<<,
SORT2 = <<<
);
COMPATIBILITY
SQL92
CREATE OPERATOR is a Postgres extension. There is no CRE
ATE OPERATOR statement in SQL92.
Man(1) output converted with
man2html