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    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</>-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;</> 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</> and <literal>RIGHTARG</> must be defined.  For
90   binary operators, both must be defined. For right unary
91   operators, only <literal>LEFTARG</> should be defined, while for left
92   unary operators only <literal>RIGHTARG</> 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   procedure 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   The other clauses specify optional operator optimization clauses.
111   Their meaning is detailed in <xref linkend="xoper-optimization">.
112  </para>
113
114  <para>
115   To be able to create an operator, you must have <literal>USAGE</literal>
116   privilege on the argument types and the return type, as well
117   as <literal>EXECUTE</literal> privilege on the underlying function.  If a
118   commutator or negator operator is specified, you must own these operators.
119  </para>
120 </refsect1>
121
122 <refsect1>
123  <title>Parameters</title>
124
125    <variablelist>
126     <varlistentry>
127      <term><replaceable class="parameter">name</replaceable></term>
128      <listitem>
129       <para>
130        The name of the operator to be defined. See above for allowable
131        characters.  The name can be schema-qualified, for example
132        <literal>CREATE OPERATOR myschema.+ (...)</>.  If not, then
133        the operator is created in the current schema.  Two operators
134        in the same schema can have the same name if they operate on
135        different data types.  This is called
136        <firstterm>overloading</>.
137       </para>
138      </listitem>
139     </varlistentry>
140
141     <varlistentry>
142      <term><replaceable class="parameter">function_name</replaceable></term>
143      <listitem>
144       <para>
145        The function used to implement this operator.
146       </para>
147      </listitem>
148     </varlistentry>
149
150     <varlistentry>
151      <term><replaceable class="parameter">left_type</replaceable></term>
152      <listitem>
153       <para>
154        The data type of the operator's left operand, if any.
155        This option would be omitted for a left-unary operator.
156       </para>
157      </listitem>
158     </varlistentry>
159
160     <varlistentry>
161      <term><replaceable class="parameter">right_type</replaceable></term>
162      <listitem>
163       <para>
164        The data type of the operator's right operand, if any.
165        This option would be omitted for a right-unary operator.
166       </para>
167      </listitem>
168     </varlistentry>
169
170     <varlistentry>
171      <term><replaceable class="parameter">com_op</replaceable></term>
172      <listitem>
173       <para>
174        The commutator of this operator.
175       </para>
176      </listitem>
177     </varlistentry>
178
179     <varlistentry>
180      <term><replaceable class="parameter">neg_op</replaceable></term>
181      <listitem>
182       <para>
183        The negator of this operator.
184       </para>
185      </listitem>
186     </varlistentry>
187
188     <varlistentry>
189      <term><replaceable class="parameter">res_proc</replaceable></term>
190      <listitem>
191       <para>
192        The restriction selectivity estimator function for this operator.
193       </para>
194      </listitem>
195     </varlistentry>
196
197     <varlistentry>
198      <term><replaceable class="parameter">join_proc</replaceable></term>
199      <listitem>
200       <para>
201        The join selectivity estimator function for this operator.
202       </para>
203      </listitem>
204     </varlistentry>
205
206     <varlistentry>
207      <term><literal>HASHES</literal></term>
208      <listitem>
209       <para>
210       Indicates this operator can support a hash join.
211       </para>
212      </listitem>
213     </varlistentry>
214
215     <varlistentry>
216      <term><literal>MERGES</literal></term>
217      <listitem>
218       <para>
219       Indicates this operator can support a merge join.
220       </para>
221      </listitem>
222     </varlistentry>
223    </variablelist>
224
225  <para>
226   To give a schema-qualified operator name in <replaceable
227   class="parameter">com_op</replaceable> or the other optional
228   arguments, use the <literal>OPERATOR()</> syntax, for example:
229<programlisting>
230COMMUTATOR = OPERATOR(myschema.===) ,
231</programlisting></para>
232 </refsect1>
233
234 <refsect1>
235  <title>Notes</title>
236
237  <para>
238   Refer to <xref linkend="xoper"> for further information.
239  </para>
240
241  <para>
242   It is not possible to specify an operator's lexical precedence in
243   <command>CREATE OPERATOR</>, because the parser's precedence behavior
244   is hard-wired.  See <xref linkend="sql-precedence"> for precedence details.
245  </para>
246
247  <para>
248   The obsolete options <literal>SORT1</>, <literal>SORT2</>,
249   <literal>LTCMP</>, and <literal>GTCMP</> were formerly used to
250   specify the names of sort operators associated with a merge-joinable
251   operator.  This is no longer necessary, since information about
252   associated operators is found by looking at B-tree operator families
253   instead.  If one of these options is given, it is ignored except
254   for implicitly setting <literal>MERGES</> true.
255  </para>
256
257  <para>
258   Use <xref linkend="sql-dropoperator"> to delete user-defined operators
259   from a database.  Use <xref linkend="sql-alteroperator"> to modify operators in a
260   database.
261  </para>
262 </refsect1>
263
264 <refsect1>
265  <title>Examples</title>
266
267  <para>
268   The following command defines a new operator, area-equality, for
269   the data type <type>box</type>:
270<programlisting>
271CREATE OPERATOR === (
272    LEFTARG = box,
273    RIGHTARG = box,
274    PROCEDURE = area_equal_procedure,
275    COMMUTATOR = ===,
276    NEGATOR = !==,
277    RESTRICT = area_restriction_procedure,
278    JOIN = area_join_procedure,
279    HASHES, MERGES
280);
281</programlisting></para>
282 </refsect1>
283
284 <refsect1>
285  <title>Compatibility</title>
286
287  <para>
288   <command>CREATE OPERATOR</command> is a
289   <productname>PostgreSQL</productname> extension.  There are no
290   provisions for user-defined operators in the SQL standard.
291  </para>
292 </refsect1>
293
294 <refsect1>
295  <title>See Also</title>
296
297  <simplelist type="inline">
298   <member><xref linkend="sql-alteroperator"></member>
299   <member><xref linkend="sql-createopclass"></member>
300   <member><xref linkend="sql-dropoperator"></member>
301  </simplelist>
302 </refsect1>
303</refentry>
304