Debian Codegraph @

doc/src/sgml/ref/create_operator.sgml

raw
1 <!--
2 doc/src/sgml/ref/create_operator.sgml
3 PostgreSQL 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>
24 CREATE 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>
56 <literal>--</literal> and <literal>/*</literal> cannot appear anywhere in an operator name,
57 since they will be taken as the start of a comment.
58 </para>
59 </listitem>
60 <listitem>
61 <para>
62 A multicharacter operator name cannot end in <literal>+</literal> or
63 <literal>-</literal>,
64 unless the name also contains at least one of these characters:
65 <literallayout>
66 ~ ! @ # % ^ &amp; | ` ?
67 </literallayout>
68 For example, <literal>@-</literal> is an allowed operator name,
69 but <literal>*-</literal> is not.
70 This restriction allows <productname>PostgreSQL</productname> to
71 parse SQL-compliant commands without requiring spaces between tokens.
72 </para>
73 </listitem>
74 <listitem>
75 <para>
76 The symbol <literal>=&gt;</literal> is reserved by the SQL grammar,
77 so it cannot be used as an operator name.
78 </para>
79 </listitem>
80 </itemizedlist>
81 </para>
82
83 <para>
84 The operator <literal>!=</literal> is mapped to
85 <literal>&lt;&gt;</literal> on input, so these two names are always
86 equivalent.
87 </para>
88
89 <para>
90 For binary operators, both <literal>LEFTARG</literal> and
91 <literal>RIGHTARG</literal> must be defined. For prefix operators only
92 <literal>RIGHTARG</literal> should be defined.
93 The <replaceable class="parameter">function_name</replaceable>
94 function must have been previously defined using <command>CREATE
95 FUNCTION</command> and must be defined to accept the correct number
96 of arguments (either one or two) of the indicated types.
97 </para>
98
99 <para>
100 In the syntax of <literal>CREATE OPERATOR</literal>, the keywords
101 <literal>FUNCTION</literal> and <literal>PROCEDURE</literal> are
102 equivalent, but the referenced function must in any case be a function, not
103 a procedure. The use of the keyword <literal>PROCEDURE</literal> here is
104 historical and deprecated.
105 </para>
106
107 <para>
108 The other clauses specify optional operator optimization attributes.
109 Their meaning is detailed in <xref linkend="xoper-optimization"/>.
110 </para>
111
112 <para>
113 To be able to create an operator, you must have <literal>USAGE</literal>
114 privilege on the argument types and the return type, as well
115 as <literal>EXECUTE</literal> privilege on the underlying function. If a
116 commutator or negator operator is specified, you must own those operators.
117 </para>
118 </refsect1>
119
120 <refsect1>
121 <title>Parameters</title>
122
123 <variablelist>
124 <varlistentry>
125 <term><replaceable class="parameter">name</replaceable></term>
126 <listitem>
127 <para>
128 The name of the operator to be defined. See above for allowable
129 characters. The name can be schema-qualified, for example
130 <literal>CREATE OPERATOR myschema.+ (...)</literal>. If not, then
131 the operator is created in the current schema. Two operators
132 in the same schema can have the same name if they operate on
133 different data types. This is called
134 <firstterm>overloading</firstterm>.
135 </para>
136 </listitem>
137 </varlistentry>
138
139 <varlistentry>
140 <term><replaceable class="parameter">function_name</replaceable></term>
141 <listitem>
142 <para>
143 The function used to implement this operator.
144 </para>
145 </listitem>
146 </varlistentry>
147
148 <varlistentry>
149 <term><replaceable class="parameter">left_type</replaceable></term>
150 <listitem>
151 <para>
152 The data type of the operator's left operand, if any.
153 This option would be omitted for a prefix operator.
154 </para>
155 </listitem>
156 </varlistentry>
157
158 <varlistentry>
159 <term><replaceable class="parameter">right_type</replaceable></term>
160 <listitem>
161 <para>
162 The data type of the operator's right operand.
163 </para>
164 </listitem>
165 </varlistentry>
166
167 <varlistentry>
168 <term><replaceable class="parameter">com_op</replaceable></term>
169 <listitem>
170 <para>
171 The commutator of this operator.
172 </para>
173 </listitem>
174 </varlistentry>
175
176 <varlistentry>
177 <term><replaceable class="parameter">neg_op</replaceable></term>
178 <listitem>
179 <para>
180 The negator of this operator.
181 </para>
182 </listitem>
183 </varlistentry>
184
185 <varlistentry>
186 <term><replaceable class="parameter">res_proc</replaceable></term>
187 <listitem>
188 <para>
189 The restriction selectivity estimator function for this operator.
190 </para>
191 </listitem>
192 </varlistentry>
193
194 <varlistentry>
195 <term><replaceable class="parameter">join_proc</replaceable></term>
196 <listitem>
197 <para>
198 The join selectivity estimator function for this operator.
199 </para>
200 </listitem>
201 </varlistentry>
202
203 <varlistentry>
204 <term><literal>HASHES</literal></term>
205 <listitem>
206 <para>
207 Indicates this operator can support a hash join.
208 </para>
209 </listitem>
210 </varlistentry>
211
212 <varlistentry>
213 <term><literal>MERGES</literal></term>
214 <listitem>
215 <para>
216 Indicates this operator can support a merge join.
217 </para>
218 </listitem>
219 </varlistentry>
220 </variablelist>
221
222 <para>
223 To give a schema-qualified operator name in <replaceable
224 class="parameter">com_op</replaceable> or the other optional
225 arguments, use the <literal>OPERATOR()</literal> syntax, for example:
226 <programlisting>
227 COMMUTATOR = OPERATOR(myschema.===) ,
228 </programlisting></para>
229 </refsect1>
230
231 <refsect1>
232 <title>Notes</title>
233
234 <para>
235 Refer to <xref linkend="xoper"/> and <xref linkend="xoper-optimization"/>
236 for further information.
237 </para>
238
239 <para>
240 When you are defining a self-commutative operator, you just do it.
241 When you are defining a pair of commutative operators, things are
242 a little trickier: how can the first one to be defined refer to the
243 other one, which you haven't defined yet? There are three solutions
244 to this problem:
245
246 <itemizedlist>
247 <listitem>
248 <para>
249 One way is to omit the <literal>COMMUTATOR</literal> clause in the
250 first operator that you define, and then provide one in the second
251 operator's definition. Since <productname>PostgreSQL</productname>
252 knows that commutative operators come in pairs, when it sees the
253 second definition it will automatically go back and fill in the
254 missing <literal>COMMUTATOR</literal> clause in the first
255 definition.
256 </para>
257 </listitem>
258
259 <listitem>
260 <para>
261 Another, more straightforward way is just to
262 include <literal>COMMUTATOR</literal> clauses in both definitions.
263 When <productname>PostgreSQL</productname> processes the first
264 definition and realizes that <literal>COMMUTATOR</literal> refers to
265 a nonexistent operator, the system will make a dummy entry for that
266 operator in the system catalog. This dummy entry will have valid
267 data only for the operator name, left and right operand types, and
268 owner, since that's all that <productname>PostgreSQL</productname>
269 can deduce at this point. The first operator's catalog entry will
270 link to this dummy entry. Later, when you define the second
271 operator, the system updates the dummy entry with the additional
272 information from the second definition. If you try to use the dummy
273 operator before it's been filled in, you'll just get an error
274 message.
275 </para>
276 </listitem>
277
278 <listitem>
279 <para>
280 Alternatively, both operators can be defined
281 without <literal>COMMUTATOR</literal> clauses
282 and then <command>ALTER OPERATOR</command> can be used to set their
283 commutator links. It's sufficient to <command>ALTER</command>
284 either one of the pair.
285 </para>
286 </listitem>
287 </itemizedlist>
288
289 In all three cases, you must own both operators in order to mark
290 them as commutators.
291 </para>
292
293 <para>
294 Pairs of negator operators can be defined using the same methods
295 as for commutator pairs.
296 </para>
297
298 <para>
299 It is not possible to specify an operator's lexical precedence in
300 <command>CREATE OPERATOR</command>, because the parser's precedence behavior
301 is hard-wired. See <xref linkend="sql-precedence"/> for precedence details.
302 </para>
303
304 <para>
305 The obsolete options <literal>SORT1</literal>, <literal>SORT2</literal>,
306 <literal>LTCMP</literal>, and <literal>GTCMP</literal> were formerly used to
307 specify the names of sort operators associated with a merge-joinable
308 operator. This is no longer necessary, since information about
309 associated operators is found by looking at B-tree operator families
310 instead. If one of these options is given, it is ignored except
311 for implicitly setting <literal>MERGES</literal> true.
312 </para>
313
314 <para>
315 Use <link linkend="sql-dropoperator"><command>DROP OPERATOR</command></link> to delete user-defined operators
316 from a database. Use <link linkend="sql-alteroperator"><command>ALTER OPERATOR</command></link> to modify operators in a
317 database.
318 </para>
319 </refsect1>
320
321 <refsect1>
322 <title>Examples</title>
323
324 <para>
325 The following command defines a new operator, area-equality, for
326 the data type <type>box</type>:
327 <programlisting>
328 CREATE OPERATOR === (
329 LEFTARG = box,
330 RIGHTARG = box,
331 FUNCTION = area_equal_function,
332 COMMUTATOR = ===,
333 NEGATOR = !==,
334 RESTRICT = area_restriction_function,
335 JOIN = area_join_function,
336 HASHES, MERGES
337 );
338 </programlisting></para>
339 </refsect1>
340
341 <refsect1>
342 <title>Compatibility</title>
343
344 <para>
345 <command>CREATE OPERATOR</command> is a
346 <productname>PostgreSQL</productname> extension. There are no
347 provisions for user-defined operators in the SQL standard.
348 </para>
349 </refsect1>
350
351 <refsect1>
352 <title>See Also</title>
353
354 <simplelist type="inline">
355 <member><xref linkend="sql-alteroperator"/></member>
356 <member><xref linkend="sql-createopclass"/></member>
357 <member><xref linkend="sql-dropoperator"/></member>
358 </simplelist>
359 </refsect1>
360 </refentry>