1 <REFENTRY ID="SQL-CREATEOPERATOR-1">
6 <REFMISCINFO>SQL - Language Statements</REFMISCINFO>
13 Defines a new user operator
18 <DATE>1998-04-15</DATE>
21 CREATE OPERATOR <replaceable>name</replaceable>
22 ([ LEFTARG = <replaceable class="parameter">type1</replaceable> ]
23 [, RIGHTARG = <replaceable class="parameter">type2</replaceable> ]
24 , PROCEDURE = <replaceable class="parameter">func_name</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-04-15</DATE>
52 <replaceable class="parameter">name</replaceable>
56 The name of an existing aggregate function.
62 <replaceable class="parameter">type1</replaceable>
71 <replaceable class="parameter">type2</replaceable>
80 <replaceable class="parameter">func_name</replaceable>
89 <replaceable class="parameter">com_op</replaceable>
98 <replaceable class="parameter">neg_op</replaceable>
107 <replaceable class="parameter">res_proc</replaceable>
116 <replaceable class="parameter">join_proc</replaceable>
125 <replaceable class="parameter">sort_op</replaceable>
138 <REFSECT2 ID="R2-SQL-CREATEOPERATOR-2">
140 <DATE>1998-04-15</DATE>
156 <ReturnValue>CREATE</ReturnValue>
160 Message returned if the operator is successfully created.
171 <REFSECT1 ID="R1-SQL-CREATEOPERATOR-1">
173 <DATE>1998-04-15</DATE>
179 This command defines a new user operator, operator_name.
180 The user who defines an operator becomes its owner.
183 The operator_name is a sequence of up to sixteen punctua
184 tion characters. The following characters are valid for
185 single-character operator names:<literallayout>
187 ~ ! @ # % ^ & ` ? </literallayout>
190 If the operator name is more than one character long, it
191 may consist of any combination of the above characters or
192 the following additional characters:<literallayout>
194 | $ : + - * / < > =</literallayout>
197 The operator "!=" is mapped to "<>" on input, and they are
198 therefore equivalent.
201 At least one of leftarg and rightarg must be defined. For
202 binary operators, both should be defined. For right unary
203 operators, only arg1 should be defined, while for left
204 unary operators only arg2 should be defined.
207 The name of the operator, operator_name, can be composed
208 of symbols only. Also, the func_name procedure must have
209 been previously defined using create function(l) and must
210 have one or two arguments.
213 The commutator operator is present so that Postgres can
214 reverse the order of the operands if it wishes. For exam
215 ple, the operator area-less-than, >>>, would have a commu
216 tator operator, area-greater-than, <<<. Suppose that an
217 operator, area-equal, ===, exists, as well as an area not
218 equal, !==. Hence, the query optimizer could freely con
221 "0,0,1,1"::box >>> MYBOXES.description
225 MYBOXES.description <<< "0,0,1,1"::box</programlisting>
228 This allows the execution code to always use the latter
229 representation and simplifies the query optimizer some
233 The negator operator allows the query optimizer to convert
235 NOT MYBOXES.description === "0,0,1,1"::box
239 MYBOXES.description !== "0,0,1,1"::box
243 If a commutator operator name is supplied, Postgres
244 searches for it in the catalog. If it is found and it
245 does not yet have a commutator itself, then the commutator's
246 entry is updated to have the current (new) operator
247 as its commutator. This applies to the negator, as well.
250 This is to allow the definition of two operators that are
251 the commutators or the negators of each other. The first
252 operator should be defined without a commutator or negator
253 (as appropriate). When the second operator is defined,
254 name the first as the commutator or negator. The first
255 will be updated as a side effect.
258 The next two specifications are present to support the
259 query optimizer in performing joins. Postgres can always
260 evaluate a join (i.e., processing a clause with two tuple
261 variables separated by an operator that returns a boolean)
262 by iterative substitution [WONG76]. In addition, Postgres
263 is planning on implementing a hash-join algorithm along
264 the lines of [SHAP86]; however, it must know whether this
265 strategy is applicable. For example, a hash-join
266 algorithm is usable for a clause of the form:
268 MYBOXES.description === MYBOXES2.description
270 but not for a clause of the form:
272 MYBOXES.description <<< MYBOXES2.description.
274 The hashes flag gives the needed information to the query
275 optimizer concerning whether a hash join strategy is
276 usable for the operator in question.</para>
278 Similarly, the two sort operators indicate to the query
279 optimizer whether merge-sort is a usable join strategy and
280 what operators should be used to sort the two operand
281 classes. For the === clause above, the optimizer must
282 sort both relations using the operator, <<<. On the other
283 hand, merge-sort is not usable with the clause:
285 MYBOXES.description <<< MYBOXES2.description
289 If other join strategies are found to be practical, Post
290 gres will change the optimizer and run-time system to use
291 them and will require additional specification when an
292 operator is defined. Fortunately, the research community
293 invents new join strategies infrequently, and the added
294 generality of user-defined join strategies was not felt to
295 be worth the complexity involved.
298 The last two pieces of the specification are present so
299 the query optimizer can estimate result sizes. If a
302 MYBOXES.description <<< "0,0,1,1"::box
304 is present in the qualification, then Postgres may have to
305 estimate the fraction of the instances in MYBOXES that
306 satisfy the clause. The function res_proc must be a reg
307 istered function (meaning it is already defined using
308 define function(l)) which accepts one argument of the correct
309 data type and returns a floating point number. The
310 query optimizer simply calls this function, passing the
311 parameter "0,0,1,1" and multiplies the result by the relation
312 size to get the desired expected number of instances.
315 Similarly, when the operands of the operator both contain
316 instance variables, the query optimizer must estimate the
317 size of the resulting join. The function join_proc will
318 return another floating point number which will be multiplied
319 by the cardinalities of the two classes involved to
320 compute the desired expected result size.
323 The difference between the function
325 my_procedure_1 (MYBOXES.description, "0,0,1,1"::box)
329 MYBOXES.description === "0,0,1,1"::box
331 is that Postgres attempts to optimize operators and can
332 decide to use an index to restrict the search space when
333 operators are involved. However, there is no attempt to
334 optimize functions, and they are performed by brute force.
335 Moreover, functions can have any number of arguments while
336 operators are restricted to one or two.
339 <REFSECT2 ID="R2-SQL-CREATEOPERATOR-3">
341 <DATE>1998-04-15</DATE>
347 Refer to <citetitle>PostgreSQL User's Guide</citetitle> chapter 5
349 This reference must be corrected.
351 for further information.
352 Refer to DROP OPERATOR statement to drop operators.
356 <REFSECT1 ID="R1-SQL-CREATEOPERATOR-2">
360 <PARA>The following command defines a new operator,
361 area-equality, for the BOX data type.
364 CREATE OPERATOR === (
367 PROCEDURE = area_equal_procedure,
370 RESTRICT = area_restriction_procedure,
372 JOIN = area-join-procedure,
379 <REFSECT1 ID="R1-SQL-CREATEOPERATOR-3">
384 CREATE OPERATOR is a PostgreSQL extension of SQL.
387 <REFSECT2 ID="R2-SQL-CREATEOPERATOR-4">
389 <DATE>1998-04-15</DATE>
395 There is no CREATE OPERATOR statement on SQL92.
401 <!-- Keep this comment at the end of the file
406 sgml-minimize-attributes:nil
407 sgml-always-quote-attributes:t
410 sgml-parent-document:nil
411 sgml-default-dtd-file:"../reference.ced"
412 sgml-exposed-tags:nil
413 sgml-local-catalogs:"/usr/lib/sgml/catalog"
414 sgml-local-ecat-files:nil