CREATE_OPERATOR(7)

1CREATE OPERATOR(7)       PostgreSQL 12.6 Documentation      CREATE OPERATOR(7)
2
3
4

NAME

6       CREATE_OPERATOR - define a new operator
7

SYNOPSIS

9       CREATE OPERATOR name (
10           {FUNCTION|PROCEDURE} = function_name
11           [, LEFTARG = left_type ] [, RIGHTARG = right_type ]
12           [, COMMUTATOR = com_op ] [, NEGATOR = neg_op ]
13           [, RESTRICT = res_proc ] [, JOIN = join_proc ]
14           [, HASHES ] [, MERGES ]
15       )
16

DESCRIPTION

18       CREATE OPERATOR defines a new operator, name. The user who defines an
19       operator becomes its owner. If a schema name is given then the operator
20       is created in the specified schema. Otherwise it is created in the
21       current schema.
22
23       The operator name is a sequence of up to NAMEDATALEN-1 (63 by default)
24       characters from the following list:
25
26           + - * / < > = ~ ! @ # % ^ & | ` ?
27
28       There are a few restrictions on your choice of name:
29
30       ·   -- and /* cannot appear anywhere in an operator name, since they
31           will be taken as the start of a comment.
32
33       ·   A multicharacter operator name cannot end in + or -, unless the
34           name also contains at least one of these characters:
35
36               ~ ! @ # % ^ & | ` ?
37
38           For example, @- is an allowed operator name, but *- is not. This
39           restriction allows PostgreSQL to parse SQL-compliant commands
40           without requiring spaces between tokens.
41
42       ·   The use of => as an operator name is deprecated. It may be
43           disallowed altogether in a future release.
44
45       The operator != is mapped to <> on input, so these two names are always
46       equivalent.
47
48       At least one of LEFTARG and RIGHTARG must be defined. For binary
49       operators, both must be defined. For right unary operators, only
50       LEFTARG should be defined, while for left unary operators only RIGHTARG
51       should be defined.
52
53           Note
54           Right unary, also called postfix, operators are deprecated and will
55           be removed in PostgreSQL version 14.
56
57       The function_name function must have been previously defined using
58       CREATE FUNCTION and must be defined to accept the correct number of
59       arguments (either one or two) of the indicated types.
60
61       In the syntax of CREATE OPERATOR, the keywords FUNCTION and PROCEDURE
62       are equivalent, but the referenced function must in any case be a
63       function, not a procedure. The use of the keyword PROCEDURE here is
64       historical and deprecated.
65
66       The other clauses specify optional operator optimization clauses. Their
67       meaning is detailed in Section 37.15.
68
69       To be able to create an operator, you must have USAGE privilege on the
70       argument types and the return type, as well as EXECUTE privilege on the
71       underlying function. If a commutator or negator operator is specified,
72       you must own these operators.
73

PARAMETERS

75       name
76           The name of the operator to be defined. See above for allowable
77           characters. The name can be schema-qualified, for example CREATE
78           OPERATOR myschema.+ (...). If not, then the operator is created in
79           the current schema. Two operators in the same schema can have the
80           same name if they operate on different data types. This is called
81           overloading.
82
83       function_name
84           The function used to implement this operator.
85
86       left_type
87           The data type of the operator's left operand, if any. This option
88           would be omitted for a left-unary operator.
89
90       right_type
91           The data type of the operator's right operand, if any. This option
92           would be omitted for a right-unary operator.
93
94       com_op
95           The commutator of this operator.
96
97       neg_op
98           The negator of this operator.
99
100       res_proc
101           The restriction selectivity estimator function for this operator.
102
103       join_proc
104           The join selectivity estimator function for this operator.
105
106       HASHES
107           Indicates this operator can support a hash join.
108
109       MERGES
110           Indicates this operator can support a merge join.
111
112       To give a schema-qualified operator name in com_op or the other
113       optional arguments, use the OPERATOR() syntax, for example:
114
115           COMMUTATOR = OPERATOR(myschema.===) ,
116

NOTES

118       Refer to Section 37.14 for further information.
119
120       It is not possible to specify an operator's lexical precedence in
121       CREATE OPERATOR, because the parser's precedence behavior is
122       hard-wired. See Section 4.1.6 for precedence details.
123
124       The obsolete options SORT1, SORT2, LTCMP, and GTCMP were formerly used
125       to specify the names of sort operators associated with a merge-joinable
126       operator. This is no longer necessary, since information about
127       associated operators is found by looking at B-tree operator families
128       instead. If one of these options is given, it is ignored except for
129       implicitly setting MERGES true.
130
131       Use DROP OPERATOR (DROP_OPERATOR(7)) to delete user-defined operators
132       from a database. Use ALTER OPERATOR (ALTER_OPERATOR(7)) to modify
133       operators in a database.
134

EXAMPLES

136       The following command defines a new operator, area-equality, for the
137       data type box:
138
139           CREATE OPERATOR === (
140               LEFTARG = box,
141               RIGHTARG = box,
142               FUNCTION = area_equal_function,
143               COMMUTATOR = ===,
144               NEGATOR = !==,
145               RESTRICT = area_restriction_function,
146               JOIN = area_join_function,
147               HASHES, MERGES
148           );
149

COMPATIBILITY

151       CREATE OPERATOR is a PostgreSQL extension. There are no provisions for
152       user-defined operators in the SQL standard.
153