2 $Header: /cvsroot/pgsql/doc/src/sgml/ref/create_operator.sgml,v 1.13 2000/03/27 17:14:42 thomas Exp $
6 <refentry id="SQL-CREATEOPERATOR">
8 <refentrytitle id="sql-createoperator-title">
11 <refmiscinfo>SQL - Language Statements</refmiscinfo>
18 Defines a new user operator
23 <date>2000-03-25</date>
26 CREATE OPERATOR <replaceable>name</replaceable> ( PROCEDURE = <replaceable class="parameter">func_name</replaceable>
27 [, LEFTARG = <replaceable class="parameter">type1</replaceable> ] [, RIGHTARG = <replaceable class="parameter">type2</replaceable> ]
28 [, COMMUTATOR = <replaceable class="parameter">com_op</replaceable> ] [, NEGATOR = <replaceable class="parameter">neg_op</replaceable> ]
29 [, RESTRICT = <replaceable class="parameter">res_proc</replaceable> ] [, JOIN = <replaceable class="parameter">join_proc</replaceable> ]
30 [, HASHES ] [, SORT1 = <replaceable class="parameter">left_sort_op</replaceable> ] [, SORT2 = <replaceable class="parameter">right_sort_op</replaceable> ] )
33 <refsect2 id="R2-SQL-CREATEOPERATOR-1">
35 <date>2000-03-25</date>
44 <term><replaceable class="parameter">name</replaceable></term>
47 The operator to be defined. See below for allowable characters.
52 <term><replaceable class="parameter">func_name</replaceable></term>
55 The function used to implement this operator.
60 <term><replaceable class="parameter">type1</replaceable></term>
63 The type of the left-hand argument of the operator, if any.
64 This option would be omitted for a left-unary operator.
69 <term><replaceable class="parameter">type2</replaceable></term>
72 The type of the right-hand argument of the operator, if any.
73 This option would be omitted for a right-unary operator.
78 <term><replaceable class="parameter">com_op</replaceable></term>
81 The commutator of this operator.
86 <term><replaceable class="parameter">neg_op</replaceable></term>
89 The negator of this operator.
94 <term><replaceable class="parameter">res_proc</replaceable></term>
97 The restriction selectivity estimator function for this operator.
102 <term><replaceable class="parameter">join_proc</replaceable></term>
105 The join selectivity estimator function for this operator.
113 Indicates this operator can support a hash join.
118 <term><replaceable class="parameter">left_sort_op</replaceable></term>
121 If this operator can support a merge join, the
122 operator that sorts the left-hand data type of this operator.
127 <term><replaceable class="parameter">right_sort_op</replaceable></term>
130 If this operator can support a merge join, the
131 operator that sorts the right-hand data type of this operator.
139 <refsect2 id="R2-SQL-CREATEOPERATOR-2">
141 <date>2000-03-25</date>
149 <term><computeroutput>
151 </computeroutput></term>
154 Message returned if the operator is successfully created.
163 <refsect1 id="R1-SQL-CREATEOPERATOR-1">
165 <date>2000-03-25</date>
171 <command>CREATE OPERATOR</command> defines a new operator,
172 <replaceable class="parameter">name</replaceable>.
173 The user who defines an operator becomes its owner.
176 The operator <replaceable class="parameter">name</replaceable>
177 is a sequence of up to NAMEDATALEN-1 (31 by default) characters
178 from the following list:
180 + - * / < > = ~ ! @ # % ^ & | ` ? $ :
183 There are a few restrictions on your choice of name:
187 "$" and ":" cannot be defined as single-character operators,
188 although they can be part of a multi-character operator name.
193 "--" and "/*" cannot appear anywhere in an operator name,
194 since they will be taken as the start of a comment.
199 A multi-character operator name cannot end in "+" or "-",
200 unless the name also contains at least one of these characters:
202 ~ ! @ # % ^ & | ` ? $ :
204 For example, <literal>@-</literal> is an allowed operator name,
205 but <literal>*-</literal> is not.
206 This restriction allows <productname>Postgres</productname> to
207 parse SQL-compliant queries without requiring spaces between tokens.
214 When working with non-SQL-standard operator names, you will usually
215 need to separate adjacent operators with spaces to avoid ambiguity.
216 For example, if you have defined a left-unary operator named "@",
217 you cannot write <literal>X*@Y</literal>; you must write
218 <literal>X* @Y</literal> to ensure that
219 <productname>Postgres</productname> reads it as two operator names
225 The operator "!=" is mapped to "<>" on input, so these two names
226 are always equivalent.
229 At least one of LEFTARG and RIGHTARG must be defined. For
230 binary operators, both should be defined. For right unary
231 operators, only LEFTARG should be defined, while for left
232 unary operators only RIGHTARG should be defined.
236 <replaceable class="parameter">func_name</replaceable> procedure must have
237 been previously defined using <command>CREATE FUNCTION</command> and must
238 be defined to accept the correct number of arguments
239 (either one or two) of the indicated types.
242 The commutator operator should be identified if one exists,
243 so that <productname>Postgres</productname> can
244 reverse the order of the operands if it wishes.
245 For example, the operator area-less-than, <<<,
246 would probably have a commutator
247 operator, area-greater-than, >>>.
248 Hence, the query optimizer could freely convert:
251 box '((0,0),(1,1))' >>> MYBOXES.description
257 MYBOXES.description <<< box '((0,0),(1,1))'
261 This allows the execution code to always use the latter
262 representation and simplifies the query optimizer somewhat.
265 Similarly, if there is a negator operator then it should be
268 operator, area-equal, ===, exists, as well as an area not
270 The negator link allows the query optimizer to simplify
272 NOT MYBOXES.description === box '((0,0),(1,1))'
276 MYBOXES.description !== box '((0,0),(1,1))'
280 If a commutator operator name is supplied,
281 <productname>Postgres</productname>
282 searches for it in the catalog. If it is found and it
283 does not yet have a commutator itself, then the commutator's
284 entry is updated to have the newly created operator as its
285 commutator. This applies to the negator, as well.
286 This is to allow the definition of two operators that are
287 the commutators or the negators of each other. The first
288 operator should be defined without a commutator or negator
289 (as appropriate). When the second operator is defined,
290 name the first as the commutator or negator. The first
291 will be updated as a side effect. (As of Postgres 6.5,
292 it also works to just have both operators refer to each other.)
295 The HASHES, SORT1, and SORT2 options are present to support the
296 query optimizer in performing joins.
297 <productname>Postgres</productname> can always
298 evaluate a join (i.e., processing a clause with two tuple
299 variables separated by an operator that returns a boolean)
300 by iterative substitution [WONG76].
301 In addition, <productname>Postgres</productname>
302 can use a hash-join algorithm along
303 the lines of [SHAP86]; however, it must know whether this
304 strategy is applicable. The current hash-join algorithm
305 is only correct for operators that represent equality tests;
306 furthermore, equality of the datatype must mean bitwise equality
307 of the representation of the type. (For example, a datatype that
308 contains unused bits that don't matter for equality tests could
310 The HASHES flag indicates to the query optimizer that a hash join
311 may safely be used with this operator.</para>
313 Similarly, the two sort operators indicate to the query
314 optimizer whether merge-sort is a usable join strategy and
315 which operators should be used to sort the two operand
316 classes. Sort operators should only be provided for an equality
317 operator, and they should refer to less-than operators for the
318 left and right side data types respectively.
321 If other join strategies are found to be practical,
322 <productname>Postgres</productname>
323 will change the optimizer and run-time system to use
324 them and will require additional specification when an
325 operator is defined. Fortunately, the research community
326 invents new join strategies infrequently, and the added
327 generality of user-defined join strategies was not felt to
328 be worth the complexity involved.
331 The RESTRICT and JOIN options assist the query optimizer in estimating
332 result sizes. If a clause of the form:
334 MYBOXES.description <<< box '((0,0),(1,1))'
336 is present in the qualification,
337 then <productname>Postgres</productname> may have to
338 estimate the fraction of the instances in MYBOXES that
339 satisfy the clause. The function
340 <replaceable class="parameter">res_proc</replaceable>
341 must be a registered function (meaning it is already defined using
342 <command>CREATE FUNCTION</command>) which accepts arguments of the correct
343 data types and returns a floating point number. The
344 query optimizer simply calls this function, passing the
345 parameter <literal>((0,0),(1,1))</literal> and multiplies the result by the relation
346 size to get the expected number of instances.
349 Similarly, when the operands of the operator both contain
350 instance variables, the query optimizer must estimate the
351 size of the resulting join. The function join_proc will
352 return another floating point number which will be multiplied
353 by the cardinalities of the two classes involved to
354 compute the expected result size.
357 The difference between the function
359 my_procedure_1 (MYBOXES.description, box '((0,0),(1,1))')
363 MYBOXES.description === box '((0,0),(1,1))'
365 is that <productname>Postgres</productname>
366 attempts to optimize operators and can
367 decide to use an index to restrict the search space when
368 operators are involved. However, there is no attempt to
369 optimize functions, and they are performed by brute force.
370 Moreover, functions can have any number of arguments while
371 operators are restricted to one or two.
374 <refsect2 id="R2-SQL-CREATEOPERATOR-3">
376 <date>2000-03-25</date>
382 Refer to the chapter on operators in the
383 <citetitle>PostgreSQL User's Guide</citetitle>
384 for further information.
385 Refer to <command>DROP OPERATOR</command> to delete
386 user-defined operators from a database.
391 <refsect1 id="R1-SQL-CREATEOPERATOR-2">
395 <para>The following command defines a new operator,
396 area-equality, for the BOX data type.
399 CREATE OPERATOR === (
402 PROCEDURE = area_equal_procedure,
405 RESTRICT = area_restriction_procedure,
406 JOIN = area_join_procedure,
414 <refsect1 id="R1-SQL-CREATEOPERATOR-3">
419 <refsect2 id="R2-SQL-CREATEOPERATOR-4">
421 <date>2000-03-25</date>
428 <command>CREATE OPERATOR</command>
429 is a <productname>Postgres</productname> extension.
430 There is no <command>CREATE OPERATOR</command>
431 statement in <acronym>SQL92</acronym>.
437 <!-- Keep this comment at the end of the file
442 sgml-minimize-attributes:nil
443 sgml-always-quote-attributes:t
446 sgml-parent-document:nil
447 sgml-default-dtd-file:"../reference.ced"
448 sgml-exposed-tags:nil
449 sgml-local-catalogs:"/usr/lib/sgml/catalog"
450 sgml-local-ecat-files:nil