2 $Header: /cvsroot/pgsql/doc/src/sgml/ref/create_operator.sgml,v 1.11 1999/07/22 15:09:08 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>1999-07-20</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>1999-04-14</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 for the left-hand side of the operator, if any. This option would be
64 omitted for a right-unary operator.
69 <term><replaceable class="parameter">type2</replaceable></term>
72 The type for the right-hand side of the operator, if any. This option would be
73 omitted for a left-unary operator.
78 <term><replaceable class="parameter">com_op</replaceable></term>
81 The commutator for 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 algorithm.
118 <term><replaceable class="parameter">left_sort_op</replaceable></term>
121 Operator that sorts the left-hand data type of this operator.
126 <term><replaceable class="parameter">right_sort_op</replaceable></term>
129 Operator that sorts the right-hand data type of this operator.
137 <refsect2 id="R2-SQL-CREATEOPERATOR-2">
139 <date>1999-04-14</date>
147 <term><computeroutput>
149 </computeroutput></term>
152 Message returned if the operator is successfully created.
161 <refsect1 id="R1-SQL-CREATEOPERATOR-1">
163 <date>1999-04-14</date>
169 <command>CREATE OPERATOR</command> defines a new operator,
170 <replaceable class="parameter">name</replaceable>.
171 The user who defines an operator becomes its owner.
174 The operator <replaceable class="parameter">name</replaceable>
175 is a sequence of up to thirty two (32) characters in any combination
178 + - * / < > = ~ ! @ # % ^ & | ` ? $ :
182 No alphabetic characters are allowed in an operator name.
183 This enables <productname>Postgres</productname> to parse SQL input
184 into tokens without requiring spaces between each token.
189 The operator "!=" is mapped to "<>" on input, so they are
190 therefore equivalent.
193 At least one of LEFTARG and RIGHTARG must be defined. For
194 binary operators, both should be defined. For right unary
195 operators, only LEFTARG should be defined, while for left
196 unary operators only RIGHTARG should be defined.
200 <replaceable class="parameter">func_name</replaceable> procedure must have
201 been previously defined using <command>CREATE FUNCTION</command> and must
202 be defined to accept the correct number of arguments
206 The commutator operator should be identified if one exists,
207 so that <productname>Postgres</productname> can
208 reverse the order of the operands if it wishes.
209 For example, the operator area-less-than, <<<,
210 would probably have a commutator
211 operator, area-greater-than, >>>.
212 Hence, the query optimizer could freely convert:
215 "0,0,1,1"::box >>> MYBOXES.description
221 MYBOXES.description <<< "0,0,1,1"::box
225 This allows the execution code to always use the latter
226 representation and simplifies the query optimizer somewhat.
229 Similarly, if there is a negator operator then it should be
232 operator, area-equal, ===, exists, as well as an area not
234 The negator link allows the query optimizer to simplify
236 NOT MYBOXES.description === "0,0,1,1"::box
240 MYBOXES.description !== "0,0,1,1"::box
244 If a commutator operator name is supplied,
245 <productname>Postgres</productname>
246 searches for it in the catalog. If it is found and it
247 does not yet have a commutator itself, then the commutator's
248 entry is updated to have the newly created operator as its
249 commutator. This applies to the negator, as well.
252 This is to allow the definition of two operators that are
253 the commutators or the negators of each other. The first
254 operator should be defined without a commutator or negator
255 (as appropriate). When the second operator is defined,
256 name the first as the commutator or negator. The first
257 will be updated as a side effect. (As of Postgres 6.5,
258 it also works to just have both operators refer to each other.)
261 The next three specifications are present to support the
262 query optimizer in performing joins.
263 <productname>Postgres</productname> can always
264 evaluate a join (i.e., processing a clause with two tuple
265 variables separated by an operator that returns a boolean)
266 by iterative substitution [WONG76].
267 In addition, <productname>Postgres</productname>
268 can use a hash-join algorithm along
269 the lines of [SHAP86]; however, it must know whether this
270 strategy is applicable. The current hash-join algorithm
271 is only correct for operators that represent equality tests;
272 furthermore, equality of the datatype must mean bitwise equality
273 of the representation of the type. (For example, a datatype that
274 contains unused bits that don't matter for equality tests could
276 The HASHES flag indicates to the query optimizer that a hash join
277 may safely be used with this operator.</para>
279 Similarly, the two sort operators indicate to the query
280 optimizer whether merge-sort is a usable join strategy and
281 which operators should be used to sort the two operand
282 classes. Sort operators should only be provided for an equality
283 operator, and they should refer to less-than operators for the
284 left and right side data types respectively.
287 If other join strategies are found to be practical,
288 <productname>Postgres</productname>
289 will change the optimizer and run-time system to use
290 them and will require additional specification when an
291 operator is defined. Fortunately, the research community
292 invents new join strategies infrequently, and the added
293 generality of user-defined join strategies was not felt to
294 be worth the complexity involved.
297 The last two pieces of the specification are present so
298 the query optimizer can estimate result sizes. If a
301 MYBOXES.description <<< "0,0,1,1"::box
303 is present in the qualification,
304 then <productname>Postgres</productname> may have to
305 estimate the fraction of the instances in MYBOXES that
306 satisfy the clause. The function
307 <replaceable class="parameter">res_proc</replaceable>
308 must be a registered function (meaning it is already defined using
309 <command>CREATE FUNCTION</command>) which accepts arguments of the correct
310 data types and returns a floating point number. The
311 query optimizer simply calls this function, passing the
312 parameter "0,0,1,1" and multiplies the result by the relation
313 size to get the desired expected number of instances.
316 Similarly, when the operands of the operator both contain
317 instance variables, the query optimizer must estimate the
318 size of the resulting join. The function join_proc will
319 return another floating point number which will be multiplied
320 by the cardinalities of the two classes involved to
321 compute the desired expected result size.
324 The difference between the function
326 my_procedure_1 (MYBOXES.description, "0,0,1,1"::box)
330 MYBOXES.description === "0,0,1,1"::box
332 is that <productname>Postgres</productname>
333 attempts to optimize operators and can
334 decide to use an index to restrict the search space when
335 operators are involved. However, there is no attempt to
336 optimize functions, and they are performed by brute force.
337 Moreover, functions can have any number of arguments while
338 operators are restricted to one or two.
341 <refsect2 id="R2-SQL-CREATEOPERATOR-3">
343 <date>1999-04-14</date>
349 Refer to the chapter on operators in the
350 <citetitle>PostgreSQL User's Guide</citetitle>
351 for further information.
352 Refer to <command>DROP OPERATOR</command> to delete
353 user-defined operators from a database.
358 <refsect1 id="R1-SQL-CREATEOPERATOR-2">
362 <para>The following command defines a new operator,
363 area-equality, for the BOX data type.
366 CREATE OPERATOR === (
369 PROCEDURE = area_equal_procedure,
372 RESTRICT = area_restriction_procedure,
373 JOIN = area_join_procedure,
381 <refsect1 id="R1-SQL-CREATEOPERATOR-3">
386 <refsect2 id="R2-SQL-CREATEOPERATOR-4">
388 <date>1999-04-14</date>
395 <command>CREATE OPERATOR</command>
396 is a <productname>Postgres</productname> extension.
397 There is no <command>CREATE OPERATOR</command>
398 statement in <acronym>SQL92</acronym>.
404 <!-- Keep this comment at the end of the file
409 sgml-minimize-attributes:nil
410 sgml-always-quote-attributes:t
413 sgml-parent-document:nil
414 sgml-default-dtd-file:"../reference.ced"
415 sgml-exposed-tags:nil
416 sgml-local-catalogs:"/usr/lib/sgml/catalog"
417 sgml-local-ecat-files:nil