create_operator(7)

1CREATE OPERATOR()                SQL Commands                CREATE OPERATOR()
2
3
4

NAME

6       CREATE OPERATOR - define a new operator
7
8

SYNOPSIS

10       CREATE OPERATOR name (
11           PROCEDURE = funcname
12           [, LEFTARG = lefttype ] [, RIGHTARG = righttype ]
13           [, COMMUTATOR = com_op ] [, NEGATOR = neg_op ]
14           [, RESTRICT = res_proc ] [, JOIN = join_proc ]
15           [, HASHES ] [, MERGES ]
16           [, SORT1 = left_sort_op ] [, SORT2 = right_sort_op ]
17           [, LTCMP = less_than_op ] [, GTCMP = greater_than_op ]
18       )
19
20

DESCRIPTION

22       CREATE  OPERATOR  defines a new operator, name. The user who defines an
23       operator becomes its owner. If a schema name is given then the operator
24       is created in the specified schema. Otherwise it is created in the cur‐
25       rent schema.
26
27       The operator name is a sequence of up to NAMEDATALEN-1 (63 by  default)
28       characters from the following list:
29
30       + - * / < > = ~ ! @ # % ^ & | ` ?
31
32       There are a few restrictions on your choice of name:
33
34       · -- and /* cannot appear anywhere in an operator name, since they will
35         be taken as the start of a comment.
36
37       · A multicharacter operator name cannot end in + or -, unless the  name
38         also contains at least one of these characters:
39
40         ~ ! @ # % ^ & | ` ?
41
42         For  example,  @-  is  an allowed operator name, but *- is not.  This
43         restriction allows PostgreSQL to parse SQL-compliant commands without
44         requiring spaces between tokens.
45
46       The operator != is mapped to <> on input, so these two names are always
47       equivalent.
48
49       At least one of LEFTARG and RIGHTARG must be defined. For binary opera‐
50       tors,  both  must  be  defined. For right unary operators, only LEFTARG
51       should be defined, while for left unary operators only RIGHTARG  should
52       be defined.
53
54       The  funcname  procedure must have been previously defined using CREATE
55       FUNCTION and must be defined to accept the correct number of  arguments
56       (either one or two) of the indicated types.
57
58       The  other  clauses  specify  optional  operator  optimization clauses.
59       Their meaning is detailed in in the documentation.
60

PARAMETERS

62       name   The name of the operator to be defined. See above for  allowable
63              characters. The name may be schema-qualified, for example CREATE
64              OPERATOR myschema.+ (...). If not, then the operator is  created
65              in the current schema. Two operators in the same schema can have
66              the same name if they operate on different data types.  This  is
67              called overloading.
68
69       funcname
70              The function used to implement this operator.
71
72       lefttype
73              The  data  type  of  the  operator's left operand, if any.  This
74              option would be omitted for a left-unary operator.
75
76       righttype
77              The data type of the operator's right  operand,  if  any.   This
78              option would be omitted for a right-unary operator.
79
80       com_op The commutator of this operator.
81
82       neg_op The negator of this operator.
83
84       res_proc
85              The  restriction  selectivity estimator function for this opera‐
86              tor.
87
88       join_proc
89              The join selectivity estimator function for this operator.
90
91       HASHES Indicates this operator can support a hash join.
92
93       MERGES Indicates this operator can support a merge join.
94
95       left_sort_op
96              If this operator can support a merge join, the less-than  opera‐
97              tor that sorts the left-hand data type of this operator.
98
99       right_sort_op
100              If  this operator can support a merge join, the less-than opera‐
101              tor that sorts the right-hand data type of this operator.
102
103       less_than_op
104              If this operator can support a merge join, the less-than  opera‐
105              tor that compares the input data types of this operator.
106
107       greater_than_op
108              If  this  operator  can  support  a merge join, the greater-than
109              operator that compares the input data types of this operator.
110
111       To give a  schema-qualified  operator  name  in  com_op  or  the  other
112       optional arguments, use the OPERATOR() syntax, for example
113
114       COMMUTATOR = OPERATOR(myschema.===) ,
115
116

NOTES

118       Refer to in the documentation for further information.
119
120       Use  DROP  OPERATOR [drop_operator(7)] to delete user-defined operators
121       from a database. Use ALTER OPERATOR [alter_operator(7)] to modify oper‐
122       ators in a database.
123

EXAMPLES

125       The  following  command  defines a new operator, area-equality, for the
126       data type box:
127
128       CREATE OPERATOR === (
129           LEFTARG = box,
130           RIGHTARG = box,
131           PROCEDURE = area_equal_procedure,
132           COMMUTATOR = ===,
133           NEGATOR = !==,
134           RESTRICT = area_restriction_procedure,
135           JOIN = area_join_procedure,
136           HASHES,
137           SORT1 = <<<,
138           SORT2 = <<<
139           -- Since sort operators were given, MERGES is implied.
140           -- LTCMP and GTCMP are assumed to be < and > respectively
141       );
142
143

COMPATIBILITY

145       CREATE OPERATOR is a PostgreSQL extension. There are no provisions  for
146       user-defined operators in the SQL standard.
147