1 <REFENTRY ID="SQL-CREATEOPERATOR">
6 <REFMISCINFO>SQL - Language Statements</REFMISCINFO>
13 Defines a new user operator
18 <DATE>1998-09-09</DATE>
21 CREATE OPERATOR <replaceable>name</replaceable> (
22 PROCEDURE = <replaceable class="parameter">func_name</replaceable>
23 [, LEFTARG = <replaceable class="parameter">type1</replaceable> ]
24 [, RIGHTARG = <replaceable class="parameter">type2</replaceable> ]
25 [, COMMUTATOR = <replaceable class="parameter">com_op</replaceable> ]
26 [, NEGATOR = <replaceable class="parameter">neg_op</replaceable> ]
27 [, RESTRICT = <replaceable class="parameter">res_proc</replaceable> ]
29 [, JOIN = <replaceable class="parameter">join_proc</replaceable> ]
30 [, SORT = <replaceable class="parameter">sort_op</replaceable> [, ...] ]
34 <REFSECT2 ID="R2-SQL-CREATEOPERATOR-1">
36 <DATE>1998-09-09</DATE>
46 <replaceable class="parameter">name</replaceable>
50 The operator to be defined. See below for allowable characters.
56 <replaceable class="parameter">func_name</replaceable>
60 The function used to implement this operator.
66 <replaceable class="parameter">type1</replaceable>
70 The type for the left-hand side of the operator, if any. This option would be
71 omitted for a right-unary operator.
77 <replaceable class="parameter">type2</replaceable>
81 The type for the right-hand side of the operator, if any. This option would be
82 omitted for a left-unary operator.
88 <replaceable class="parameter">com_op</replaceable>
92 The corresponding commutative operator.
98 <replaceable class="parameter">neg_op</replaceable>
102 The corresponding negation operator.
108 <replaceable class="parameter">res_proc</replaceable>
112 The corresponding restriction operator.
122 This operator can support a hash-join algorithm.
128 <replaceable class="parameter">join_proc</replaceable>
132 Procedure supporting table joins.
138 <replaceable class="parameter">sort_op</replaceable>
142 Operator to use for sorting.
150 <REFSECT2 ID="R2-SQL-CREATEOPERATOR-2">
152 <DATE>1998-09-09</DATE>
161 <ReturnValue>CREATE</ReturnValue>
165 Message returned if the operator is successfully created.
171 <REFSECT1 ID="R1-SQL-CREATEOPERATOR-1">
173 <DATE>1998-09-09</DATE>
179 <command>CREATE OPERATOR</command> defines a new operator,
180 <replaceable class="parameter">name</replaceable>.
181 The user who defines an operator becomes its owner.
184 The operator <replaceable class="parameter">name</replaceable>
185 is a sequence of up to thirty two (32) characters in any combination
188 + - * / < > = ~ ! @ # % ^ & | ` ? $ :
192 No alphabetic characters are allowed in an operator name.
193 This enables <productname>Postgres</productname> to parse SQL input
194 into tokens without requiring spaces between each token.
199 The operator "!=" is mapped to "<>" on input, so they are
200 therefore equivalent.
203 At least one of LEFTARG and RIGHTARG must be defined. For
204 binary operators, both should be defined. For right unary
205 operators, only LEFTARG should be defined, while for left
206 unary operators only RIGHTARG should be defined.
210 <replaceable class="parameter">func_name</replaceable> procedure must have
211 been previously defined using <command>CREATE FUNCTION</command> and must
212 be defined to accept the correct number of arguments
216 The commutator operator is present so that
217 <productname>Postgres</productname> can
218 reverse the order of the operands if it wishes.
219 For example, the operator area-less-than, <<<,
220 would have a commutator
221 operator, area-greater-than, >>>.
222 Hence, the query optimizer could freely convert:
224 "0,0,1,1"::box >>> MYBOXES.description
228 MYBOXES.description <<< "0,0,1,1"::box</programlisting>
231 This allows the execution code to always use the latter
232 representation and simplifies the query optimizer some
237 operator, area-equal, ===, exists, as well as an area not
239 The negator operator allows the query optimizer to convert
241 NOT MYBOXES.description === "0,0,1,1"::box
245 MYBOXES.description !== "0,0,1,1"::box
249 If a commutator operator name is supplied,
250 <productname>Postgres</productname>
251 searches for it in the catalog. If it is found and it
252 does not yet have a commutator itself, then the commutator's
253 entry is updated to have the current (new) operator
254 as its commutator. This applies to the negator, as well.
257 This is to allow the definition of two operators that are
258 the commutators or the negators of each other. The first
259 operator should be defined without a commutator or negator
260 (as appropriate). When the second operator is defined,
261 name the first as the commutator or negator. The first
262 will be updated as a side effect.
265 The next two specifications are present to support the
266 query optimizer in performing joins.
267 <productname>Postgres</productname> can always
268 evaluate a join (i.e., processing a clause with two tuple
269 variables separated by an operator that returns a boolean)
270 by iterative substitution [WONG76].
271 In addition, <productname>Postgres</productname>
272 is planning on implementing a hash-join algorithm along
273 the lines of [SHAP86]; however, it must know whether this
274 strategy is applicable.
275 For example, a hash-join
276 algorithm is usable for a clause of the form:
278 MYBOXES.description === MYBOXES2.description
280 but not for a clause of the form:
282 MYBOXES.description <<< MYBOXES2.description.
284 The HASHES flag gives the needed information to the query
285 optimizer concerning whether a hash join strategy is
286 usable for the operator in question.</para>
288 Similarly, the two sort operators indicate to the query
289 optimizer whether merge-sort is a usable join strategy and
290 what operators should be used to sort the two operand
291 classes. For the === clause above, the optimizer must
292 sort both relations using the operator, <<<. On the other
293 hand, merge-sort is not usable with the clause:
295 MYBOXES.description <<< MYBOXES2.description
299 If other join strategies are found to be practical,
300 <productname>Postgres</productname>
301 will change the optimizer and run-time system to use
302 them and will require additional specification when an
303 operator is defined. Fortunately, the research community
304 invents new join strategies infrequently, and the added
305 generality of user-defined join strategies was not felt to
306 be worth the complexity involved.
309 The last two pieces of the specification are present so
310 the query optimizer can estimate result sizes. If a
313 MYBOXES.description <<< "0,0,1,1"::box
315 is present in the qualification,
316 then <productname>Postgres</productname> may have to
317 estimate the fraction of the instances in MYBOXES that
318 satisfy the clause. The function
319 <replaceable class="parameter">res_proc</replaceable>
320 must be a registered function (meaning it is already defined using
321 define function(l)) which accepts one argument of the correct
322 data type and returns a floating point number. The
323 query optimizer simply calls this function, passing the
324 parameter "0,0,1,1" and multiplies the result by the relation
325 size to get the desired expected number of instances.
328 Similarly, when the operands of the operator both contain
329 instance variables, the query optimizer must estimate the
330 size of the resulting join. The function join_proc will
331 return another floating point number which will be multiplied
332 by the cardinalities of the two classes involved to
333 compute the desired expected result size.
336 The difference between the function
338 my_procedure_1 (MYBOXES.description, "0,0,1,1"::box)
342 MYBOXES.description === "0,0,1,1"::box
344 is that <productname>Postgres</productname>
345 attempts to optimize operators and can
346 decide to use an index to restrict the search space when
347 operators are involved. However, there is no attempt to
348 optimize functions, and they are performed by brute force.
349 Moreover, functions can have any number of arguments while
350 operators are restricted to one or two.
353 <REFSECT2 ID="R2-SQL-CREATEOPERATOR-3">
355 <DATE>1998-09-09</DATE>
361 Refer to the chapter on operators in the
362 <citetitle>PostgreSQL User's Guide</citetitle>
363 for further information.
364 Refer to <command>DROP OPERATOR</command> to delete
365 user-defined operators from a database.
369 <REFSECT1 ID="R1-SQL-CREATEOPERATOR-2">
373 <PARA>The following command defines a new operator,
374 area-equality, for the BOX data type.
377 CREATE OPERATOR === (
380 PROCEDURE = area_equal_procedure,
383 RESTRICT = area_restriction_procedure,
385 JOIN = area-join-procedure,
392 <REFSECT1 ID="R1-SQL-CREATEOPERATOR-3">
397 CREATE OPERATOR is a <productname>Postgres</productname> extension.
400 <REFSECT2 ID="R2-SQL-CREATEOPERATOR-4">
402 <DATE>1998-09-09</DATE>
408 There is no CREATE OPERATOR statement in <acronym>SQL92</acronym>.
414 <!-- Keep this comment at the end of the file
419 sgml-minimize-attributes:nil
420 sgml-always-quote-attributes:t
423 sgml-parent-document:nil
424 sgml-default-dtd-file:"../reference.ced"
425 sgml-exposed-tags:nil
426 sgml-local-catalogs:"/usr/lib/sgml/catalog"
427 sgml-local-ecat-files:nil