1<!--
2doc/src/sgml/ref/create_operator.sgml
3PostgreSQL documentation
4-->
5
6<refentry id="sql-createoperator">
7 <indexterm zone="sql-createoperator">
8  <primary>CREATE OPERATOR</primary>
9 </indexterm>
10
11 <refmeta>
12  <refentrytitle>CREATE OPERATOR</refentrytitle>
13  <manvolnum>7</manvolnum>
14  <refmiscinfo>SQL - Language Statements</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18  <refname>CREATE OPERATOR</refname>
19  <refpurpose>define a new operator</refpurpose>
20 </refnamediv>
21
22 <refsynopsisdiv>
23<synopsis>
24CREATE OPERATOR <replaceable>name</replaceable> (
25    {FUNCTION|PROCEDURE} = <replaceable class="parameter">function_name</replaceable>
26    [, LEFTARG = <replaceable class="parameter">left_type</replaceable> ] [, RIGHTARG = <replaceable class="parameter">right_type</replaceable> ]
27    [, COMMUTATOR = <replaceable class="parameter">com_op</replaceable> ] [, NEGATOR = <replaceable class="parameter">neg_op</replaceable> ]
28    [, RESTRICT = <replaceable class="parameter">res_proc</replaceable> ] [, JOIN = <replaceable class="parameter">join_proc</replaceable> ]
29    [, HASHES ] [, MERGES ]
30)
31</synopsis>
32 </refsynopsisdiv>
33
34 <refsect1>
35  <title>Description</title>
36
37  <para>
38   <command>CREATE OPERATOR</command> defines a new operator,
39   <replaceable class="parameter">name</replaceable>.  The user who
40   defines an operator becomes its owner.  If a schema name is given
41   then the operator is created in the specified schema.  Otherwise it
42   is created in the current schema.
43  </para>
44
45  <para>
46   The operator name is a sequence of up to <symbol>NAMEDATALEN</symbol>-1
47   (63 by default) characters from the following list:
48<literallayout>
49+ - * / &lt; &gt; = ~ ! @ # % ^ &amp; | ` ?
50</literallayout>
51
52   There are a few restrictions on your choice of name:
53   <itemizedlist>
54    <listitem>
55     <para><literal>--</literal> and <literal>/*</literal> cannot appear anywhere in an operator name,
56     since they will be taken as the start of a comment.
57     </para>
58    </listitem>
59    <listitem>
60     <para>
61     A multicharacter operator name cannot end in <literal>+</literal> or
62     <literal>-</literal>,
63     unless the name also contains at least one of these characters:
64<literallayout>
65~ ! @ # % ^ &amp; | ` ?
66</literallayout>
67     For example, <literal>@-</literal> is an allowed operator name,
68     but <literal>*-</literal> is not.
69     This restriction allows <productname>PostgreSQL</productname> to
70     parse SQL-compliant commands without requiring spaces between tokens.
71     </para>
72    </listitem>
73    <listitem>
74     <para>
75     The use of <literal>=&gt;</literal> as an operator name is deprecated.  It may
76     be disallowed altogether in a future release.
77     </para>
78    </listitem>
79   </itemizedlist>
80  </para>
81
82  <para>
83   The operator <literal>!=</literal> is mapped to
84   <literal>&lt;&gt;</literal> on input, so these two names are always
85   equivalent.
86  </para>
87
88  <para>
89   At least one of <literal>LEFTARG</literal> and <literal>RIGHTARG</literal> must be defined.  For
90   binary operators, both must be defined. For right unary
91   operators, only <literal>LEFTARG</literal> should be defined, while for left
92   unary operators only <literal>RIGHTARG</literal> should be defined.
93  </para>
94
95  <note>
96   <para>
97    Right unary, also called postfix, operators are deprecated and will be
98    removed in <productname>PostgreSQL</productname> version 14.
99   </para>
100  </note>
101
102  <para>
103   The <replaceable class="parameter">function_name</replaceable>
104   function must have been previously defined using <command>CREATE
105   FUNCTION</command> and must be defined to accept the correct number
106   of arguments (either one or two) of the indicated types.
107  </para>
108
109  <para>
110   In the syntax of <literal>CREATE OPERATOR</literal>, the keywords
111   <literal>FUNCTION</literal> and <literal>PROCEDURE</literal> are
112   equivalent, but the referenced function must in any case be a function, not
113   a procedure.  The use of the keyword <literal>PROCEDURE</literal> here is
114   historical and deprecated.
115  </para>
116
117  <para>
118   The other clauses specify optional operator optimization clauses.
119   Their meaning is detailed in <xref linkend="xoper-optimization"/>.
120  </para>
121
122  <para>
123   To be able to create an operator, you must have <literal>USAGE</literal>
124   privilege on the argument types and the return type, as well
125   as <literal>EXECUTE</literal> privilege on the underlying function.  If a
126   commutator or negator operator is specified, you must own these operators.
127  </para>
128 </refsect1>
129
130 <refsect1>
131  <title>Parameters</title>
132
133    <variablelist>
134     <varlistentry>
135      <term><replaceable class="parameter">name</replaceable></term>
136      <listitem>
137       <para>
138        The name of the operator to be defined. See above for allowable
139        characters.  The name can be schema-qualified, for example
140        <literal>CREATE OPERATOR myschema.+ (...)</literal>.  If not, then
141        the operator is created in the current schema.  Two operators
142        in the same schema can have the same name if they operate on
143        different data types.  This is called
144        <firstterm>overloading</firstterm>.
145       </para>
146      </listitem>
147     </varlistentry>
148
149     <varlistentry>
150      <term><replaceable class="parameter">function_name</replaceable></term>
151      <listitem>
152       <para>
153        The function used to implement this operator.
154       </para>
155      </listitem>
156     </varlistentry>
157
158     <varlistentry>
159      <term><replaceable class="parameter">left_type</replaceable></term>
160      <listitem>
161       <para>
162        The data type of the operator's left operand, if any.
163        This option would be omitted for a left-unary operator.
164       </para>
165      </listitem>
166     </varlistentry>
167
168     <varlistentry>
169      <term><replaceable class="parameter">right_type</replaceable></term>
170      <listitem>
171       <para>
172        The data type of the operator's right operand, if any.
173        This option would be omitted for a right-unary operator.
174       </para>
175      </listitem>
176     </varlistentry>
177
178     <varlistentry>
179      <term><replaceable class="parameter">com_op</replaceable></term>
180      <listitem>
181       <para>
182        The commutator of this operator.
183       </para>
184      </listitem>
185     </varlistentry>
186
187     <varlistentry>
188      <term><replaceable class="parameter">neg_op</replaceable></term>
189      <listitem>
190       <para>
191        The negator of this operator.
192       </para>
193      </listitem>
194     </varlistentry>
195
196     <varlistentry>
197      <term><replaceable class="parameter">res_proc</replaceable></term>
198      <listitem>
199       <para>
200        The restriction selectivity estimator function for this operator.
201       </para>
202      </listitem>
203     </varlistentry>
204
205     <varlistentry>
206      <term><replaceable class="parameter">join_proc</replaceable></term>
207      <listitem>
208       <para>
209        The join selectivity estimator function for this operator.
210       </para>
211      </listitem>
212     </varlistentry>
213
214     <varlistentry>
215      <term><literal>HASHES</literal></term>
216      <listitem>
217       <para>
218       Indicates this operator can support a hash join.
219       </para>
220      </listitem>
221     </varlistentry>
222
223     <varlistentry>
224      <term><literal>MERGES</literal></term>
225      <listitem>
226       <para>
227       Indicates this operator can support a merge join.
228       </para>
229      </listitem>
230     </varlistentry>
231    </variablelist>
232
233  <para>
234   To give a schema-qualified operator name in <replaceable
235   class="parameter">com_op</replaceable> or the other optional
236   arguments, use the <literal>OPERATOR()</literal> syntax, for example:
237<programlisting>
238COMMUTATOR = OPERATOR(myschema.===) ,
239</programlisting></para>
240 </refsect1>
241
242 <refsect1>
243  <title>Notes</title>
244
245  <para>
246   Refer to <xref linkend="xoper"/> for further information.
247  </para>
248
249  <para>
250   It is not possible to specify an operator's lexical precedence in
251   <command>CREATE OPERATOR</command>, because the parser's precedence behavior
252   is hard-wired.  See <xref linkend="sql-precedence"/> for precedence details.
253  </para>
254
255  <para>
256   The obsolete options <literal>SORT1</literal>, <literal>SORT2</literal>,
257   <literal>LTCMP</literal>, and <literal>GTCMP</literal> were formerly used to
258   specify the names of sort operators associated with a merge-joinable
259   operator.  This is no longer necessary, since information about
260   associated operators is found by looking at B-tree operator families
261   instead.  If one of these options is given, it is ignored except
262   for implicitly setting <literal>MERGES</literal> true.
263  </para>
264
265  <para>
266   Use <xref linkend="sql-dropoperator"/> to delete user-defined operators
267   from a database.  Use <xref linkend="sql-alteroperator"/> to modify operators in a
268   database.
269  </para>
270 </refsect1>
271
272 <refsect1>
273  <title>Examples</title>
274
275  <para>
276   The following command defines a new operator, area-equality, for
277   the data type <type>box</type>:
278<programlisting>
279CREATE OPERATOR === (
280    LEFTARG = box,
281    RIGHTARG = box,
282    FUNCTION = area_equal_function,
283    COMMUTATOR = ===,
284    NEGATOR = !==,
285    RESTRICT = area_restriction_function,
286    JOIN = area_join_function,
287    HASHES, MERGES
288);
289</programlisting></para>
290 </refsect1>
291
292 <refsect1>
293  <title>Compatibility</title>
294
295  <para>
296   <command>CREATE OPERATOR</command> is a
297   <productname>PostgreSQL</productname> extension.  There are no
298   provisions for user-defined operators in the SQL standard.
299  </para>
300 </refsect1>
301
302 <refsect1>
303  <title>See Also</title>
304
305  <simplelist type="inline">
306   <member><xref linkend="sql-alteroperator"/></member>
307   <member><xref linkend="sql-createopclass"/></member>
308   <member><xref linkend="sql-dropoperator"/></member>
309  </simplelist>
310 </refsect1>
311</refentry>
312