I686LINUX/util/I686LINUX/doc/postgresql/html/sql-createoperator.html

   1 <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
   2 <HTML
   3 ><HEAD
   4 ><TITLE
   5 >CREATE OPERATOR</TITLE
   6 ><META
   7 NAME="GENERATOR"
   8 CONTENT="Modular DocBook HTML Stylesheet Version 1.7"><LINK
   9 REV="MADE"
  10 HREF="mailto:pgsql-docs@postgresql.org"><LINK
  11 REL="HOME"
  12 TITLE="PostgreSQL 7.4.1 Documentation"
  13 HREF="index.html"><LINK
  14 REL="UP"
  15 TITLE="SQL Commands"
  16 HREF="sql-commands.html"><LINK
  17 REL="PREVIOUS"
  18 TITLE="CREATE LANGUAGE"
  19 HREF="sql-createlanguage.html"><LINK
  20 REL="NEXT"
  21 TITLE="CREATE OPERATOR CLASS"
  22 HREF="sql-createopclass.html"><LINK
  23 REL="STYLESHEET"
  24 TYPE="text/css"
  25 HREF="stylesheet.css"><META
  26 NAME="creation"
  27 CONTENT="2003-12-22T03:48:47"></HEAD
  28 ><BODY
  29 CLASS="REFENTRY"
  30 ><DIV
  31 CLASS="NAVHEADER"
  32 ><TABLE
  33 SUMMARY="Header navigation table"
  34 WIDTH="100%"
  35 BORDER="0"
  36 CELLPADDING="0"
  37 CELLSPACING="0"
  38 ><TR
  39 ><TH
  40 COLSPAN="5"
  41 ALIGN="center"
  42 VALIGN="bottom"
  43 >PostgreSQL 7.4.1 Documentation</TH
  44 ></TR
  45 ><TR
  46 ><TD
  47 WIDTH="10%"
  48 ALIGN="left"
  49 VALIGN="top"
  50 ><A
  51 HREF="sql-createlanguage.html"
  52 ACCESSKEY="P"
  53 >Prev</A
  54 ></TD
  55 ><TD
  56 WIDTH="10%"
  57 ALIGN="left"
  58 VALIGN="top"
  59 ><A
  60 HREF="sql-createlanguage.html"
  61 >Fast Backward</A
  62 ></TD
  63 ><TD
  64 WIDTH="60%"
  65 ALIGN="center"
  66 VALIGN="bottom"
  67 ></TD
  68 ><TD
  69 WIDTH="10%"
  70 ALIGN="right"
  71 VALIGN="top"
  72 ><A
  73 HREF="sql-createopclass.html"
  74 >Fast Forward</A
  75 ></TD
  76 ><TD
  77 WIDTH="10%"
  78 ALIGN="right"
  79 VALIGN="top"
  80 ><A
  81 HREF="sql-createopclass.html"
  82 ACCESSKEY="N"
  83 >Next</A
  84 ></TD
  85 ></TR
  86 ></TABLE
  87 ><HR
  88 ALIGN="LEFT"
  89 WIDTH="100%"></DIV
  90 ><H1
  91 ><A
  92 NAME="SQL-CREATEOPERATOR"
  93 ></A
  94 >CREATE OPERATOR</H1
  95 ><DIV
  96 CLASS="REFNAMEDIV"
  97 ><A
  98 NAME="AEN37436"
  99 ></A
 100 ><H2
 101 >Name</H2
 102 >CREATE OPERATOR&nbsp;--&nbsp;define a new operator</DIV
 103 ><A
 104 NAME="AEN37439"
 105 ></A
 106 ><DIV
 107 CLASS="REFSYNOPSISDIV"
 108 ><A
 109 NAME="AEN37441"
 110 ></A
 111 ><H2
 112 >Synopsis</H2
 113 ><PRE
 114 CLASS="SYNOPSIS"
 115 >CREATE OPERATOR <VAR
 116 CLASS="REPLACEABLE"
 117 >name</VAR
 118 > (
 119     PROCEDURE = <VAR
 120 CLASS="REPLACEABLE"
 121 >funcname</VAR
 122 >
 123     [, LEFTARG = <VAR
 124 CLASS="REPLACEABLE"
 125 >lefttype</VAR
 126 > ] [, RIGHTARG = <VAR
 127 CLASS="REPLACEABLE"
 128 >righttype</VAR
 129 > ]
 130     [, COMMUTATOR = <VAR
 131 CLASS="REPLACEABLE"
 132 >com_op</VAR
 133 > ] [, NEGATOR = <VAR
 134 CLASS="REPLACEABLE"
 135 >neg_op</VAR
 136 > ]
 137     [, RESTRICT = <VAR
 138 CLASS="REPLACEABLE"
 139 >res_proc</VAR
 140 > ] [, JOIN = <VAR
 141 CLASS="REPLACEABLE"
 142 >join_proc</VAR
 143 > ]
 144     [, HASHES ] [, MERGES ]
 145     [, SORT1 = <VAR
 146 CLASS="REPLACEABLE"
 147 >left_sort_op</VAR
 148 > ] [, SORT2 = <VAR
 149 CLASS="REPLACEABLE"
 150 >right_sort_op</VAR
 151 > ]
 152     [, LTCMP = <VAR
 153 CLASS="REPLACEABLE"
 154 >less_than_op</VAR
 155 > ] [, GTCMP = <VAR
 156 CLASS="REPLACEABLE"
 157 >greater_than_op</VAR
 158 > ]
 159 )</PRE
 160 ></DIV
 161 ><DIV
 162 CLASS="REFSECT1"
 163 ><A
 164 NAME="AEN37455"
 165 ></A
 166 ><H2
 167 >Description</H2
 168 ><P
 169 >   <TT
 170 CLASS="COMMAND"
 171 >CREATE OPERATOR</TT
 172 > defines a new operator,
 173    <VAR
 174 CLASS="REPLACEABLE"
 175 >name</VAR
 176 >.  The user who
 177    defines an operator becomes its owner.  If a schema name is given
 178    then the operator is created in the specified schema.  Otherwise it
 179    is created in the current schema.
 180   </P
 181 ><P
 182 >   The operator name is a sequence of up to <TT
 183 CLASS="SYMBOL"
 184 >NAMEDATALEN</TT
 185 >-1
 186    (63 by default) characters from the following list:
 187 <P
 188 CLASS="LITERALLAYOUT"
 189 >+&nbsp;-&nbsp;*&nbsp;/&nbsp;&lt;&nbsp;&gt;&nbsp;=&nbsp;~&nbsp;!&nbsp;@&nbsp;#&nbsp;%&nbsp;^&nbsp;&amp;&nbsp;|&nbsp;`&nbsp;?</P
 190 >
 191
 192    There are a few restrictions on your choice of name:
 193    <P
 194 ></P
 195 ></P><UL
 196 ><LI
 197 ><P
 198 >     <TT
 199 CLASS="LITERAL"
 200 >--</TT
 201 > and <TT
 202 CLASS="LITERAL"
 203 >/*</TT
 204 > cannot appear anywhere in an operator name,
 205      since they will be taken as the start of a comment.
 206      </P
 207 ></LI
 208 ><LI
 209 ><P
 210 >     A multicharacter operator name cannot end in <TT
 211 CLASS="LITERAL"
 212 >+</TT
 213 > or
 214      <TT
 215 CLASS="LITERAL"
 216 >-</TT
 217 >,
 218      unless the name also contains at least one of these characters:
 219 <P
 220 CLASS="LITERALLAYOUT"
 221 >~&nbsp;!&nbsp;@&nbsp;#&nbsp;%&nbsp;^&nbsp;&amp;&nbsp;|&nbsp;`&nbsp;?</P
 222 >
 223      For example, <TT
 224 CLASS="LITERAL"
 225 >@-</TT
 226 > is an allowed operator name,
 227      but <TT
 228 CLASS="LITERAL"
 229 >*-</TT
 230 > is not.
 231      This restriction allows <SPAN
 232 CLASS="PRODUCTNAME"
 233 >PostgreSQL</SPAN
 234 > to
 235      parse SQL-compliant commands without requiring spaces between tokens.
 236      </P
 237 ></LI
 238 ></UL
 239 ><P>
 240   </P
 241 ><P
 242 >   The operator <TT
 243 CLASS="LITERAL"
 244 >!=</TT
 245 > is mapped to
 246    <TT
 247 CLASS="LITERAL"
 248 >&lt;&gt;</TT
 249 > on input, so these two names are always
 250    equivalent.
 251   </P
 252 ><P
 253 >   At least one of <TT
 254 CLASS="LITERAL"
 255 >LEFTARG</TT
 256 > and <TT
 257 CLASS="LITERAL"
 258 >RIGHTARG</TT
 259 > must be defined.  For
 260    binary operators, both must be defined. For right  unary
 261    operators, only <TT
 262 CLASS="LITERAL"
 263 >LEFTARG</TT
 264 > should be defined, while for left
 265    unary operators only <TT
 266 CLASS="LITERAL"
 267 >RIGHTARG</TT
 268 > should be defined.
 269   </P
 270 ><P
 271 >   The <VAR
 272 CLASS="REPLACEABLE"
 273 >funcname</VAR
 274 >
 275    procedure must have been previously defined using <TT
 276 CLASS="COMMAND"
 277 >CREATE
 278    FUNCTION</TT
 279 > and must be defined to accept the correct number
 280    of arguments (either one or two) of the indicated types.
 281   </P
 282 ><P
 283 >   The other clauses specify optional operator optimization clauses.
 284    Their meaning is detailed in <A
 285 HREF="xoper.html"
 286 >Section 33.11</A
 287 >.
 288   </P
 289 ></DIV
 290 ><DIV
 291 CLASS="REFSECT1"
 292 ><A
 293 NAME="AEN37489"
 294 ></A
 295 ><H2
 296 >Parameters</H2
 297 ><P
 298 ></P
 299 ><DIV
 300 CLASS="VARIABLELIST"
 301 ><DL
 302 ><DT
 303 ><VAR
 304 CLASS="REPLACEABLE"
 305 >name</VAR
 306 ></DT
 307 ><DD
 308 ><P
 309 >       The name of the operator to be defined. See above for allowable
 310         characters.  The name may be schema-qualified, for example
 311         <TT
 312 CLASS="LITERAL"
 313 >CREATE OPERATOR myschema.+ (...)</TT
 314 >.  If not, then
 315         the operator is created in the current schema.  Two operators
 316         in the same schema can have the same name if they operate on
 317         different data types.  This is called
 318         <I
 319 CLASS="FIRSTTERM"
 320 >overloading</I
 321 >.
 322        </P
 323 ></DD
 324 ><DT
 325 ><VAR
 326 CLASS="REPLACEABLE"
 327 >funcname</VAR
 328 ></DT
 329 ><DD
 330 ><P
 331 >       The function used to implement this operator.
 332        </P
 333 ></DD
 334 ><DT
 335 ><VAR
 336 CLASS="REPLACEABLE"
 337 >lefttype</VAR
 338 ></DT
 339 ><DD
 340 ><P
 341 >       The type of the left-hand argument of the operator, if any.
 342         This option would be omitted for a left-unary operator.
 343        </P
 344 ></DD
 345 ><DT
 346 ><VAR
 347 CLASS="REPLACEABLE"
 348 >righttype</VAR
 349 ></DT
 350 ><DD
 351 ><P
 352 >       The type of the right-hand argument of the operator, if any.
 353         This option would be omitted for a right-unary operator.
 354        </P
 355 ></DD
 356 ><DT
 357 ><VAR
 358 CLASS="REPLACEABLE"
 359 >com_op</VAR
 360 ></DT
 361 ><DD
 362 ><P
 363 >       The commutator of this operator.
 364        </P
 365 ></DD
 366 ><DT
 367 ><VAR
 368 CLASS="REPLACEABLE"
 369 >neg_op</VAR
 370 ></DT
 371 ><DD
 372 ><P
 373 >       The negator of this operator.
 374        </P
 375 ></DD
 376 ><DT
 377 ><VAR
 378 CLASS="REPLACEABLE"
 379 >res_proc</VAR
 380 ></DT
 381 ><DD
 382 ><P
 383 >       The restriction selectivity estimator function for this operator.
 384        </P
 385 ></DD
 386 ><DT
 387 ><VAR
 388 CLASS="REPLACEABLE"
 389 >join_proc</VAR
 390 ></DT
 391 ><DD
 392 ><P
 393 >       The join selectivity estimator function for this operator.
 394        </P
 395 ></DD
 396 ><DT
 397 ><TT
 398 CLASS="LITERAL"
 399 >HASHES</TT
 400 ></DT
 401 ><DD
 402 ><P
 403 >       Indicates this operator can support a hash join.
 404        </P
 405 ></DD
 406 ><DT
 407 ><TT
 408 CLASS="LITERAL"
 409 >MERGES</TT
 410 ></DT
 411 ><DD
 412 ><P
 413 >       Indicates this operator can support a merge join.
 414        </P
 415 ></DD
 416 ><DT
 417 ><VAR
 418 CLASS="REPLACEABLE"
 419 >left_sort_op</VAR
 420 ></DT
 421 ><DD
 422 ><P
 423 >       If this operator can support a merge join, the less-than
 424         operator that sorts the left-hand data type of this operator.
 425        </P
 426 ></DD
 427 ><DT
 428 ><VAR
 429 CLASS="REPLACEABLE"
 430 >right_sort_op</VAR
 431 ></DT
 432 ><DD
 433 ><P
 434 >       If this operator can support a merge join, the less-than
 435         operator that sorts the right-hand data type of this operator.
 436        </P
 437 ></DD
 438 ><DT
 439 ><VAR
 440 CLASS="REPLACEABLE"
 441 >less_than_op</VAR
 442 ></DT
 443 ><DD
 444 ><P
 445 >       If this operator can support a merge join, the less-than
 446         operator that compares the input data types of this operator.
 447        </P
 448 ></DD
 449 ><DT
 450 ><VAR
 451 CLASS="REPLACEABLE"
 452 >greater_than_op</VAR
 453 ></DT
 454 ><DD
 455 ><P
 456 >       If this operator can support a merge join, the greater-than
 457         operator that compares the input data types of this operator.
 458        </P
 459 ></DD
 460 ></DL
 461 ></DIV
 462 ><P
 463 >   To give a schema-qualified operator name in <VAR
 464 CLASS="REPLACEABLE"
 465 >com_op</VAR
 466 > or the other optional
 467    arguments, use the <TT
 468 CLASS="LITERAL"
 469 >OPERATOR()</TT
 470 > syntax, for example
 471 </P><PRE
 472 CLASS="PROGRAMLISTING"
 473 >COMMUTATOR = OPERATOR(myschema.===) ,</PRE
 474 ><P>
 475   </P
 476 ></DIV
 477 ><DIV
 478 CLASS="REFSECT1"
 479 ><A
 480 NAME="AEN37568"
 481 ></A
 482 ><H2
 483 >Notes</H2
 484 ><P
 485 >   Refer to <A
 486 HREF="xoper.html"
 487 >Section 33.11</A
 488 > for further information.
 489   </P
 490 ><P
 491 >   Use <TT
 492 CLASS="COMMAND"
 493 >DROP OPERATOR</TT
 494 > to delete user-defined
 495    operators from a database.
 496   </P
 497 ></DIV
 498 ><DIV
 499 CLASS="REFSECT1"
 500 ><A
 501 NAME="AEN37574"
 502 ></A
 503 ><H2
 504 >Examples</H2
 505 ><P
 506 >   The following command defines a new operator, area-equality, for
 507    the data type <TT
 508 CLASS="TYPE"
 509 >box</TT
 510 >:
 511 </P><PRE
 512 CLASS="PROGRAMLISTING"
 513 >CREATE OPERATOR === (
 514     LEFTARG = box,
 515     RIGHTARG = box,
 516     PROCEDURE = area_equal_procedure,
 517     COMMUTATOR = ===,
 518     NEGATOR = !==,
 519     RESTRICT = area_restriction_procedure,
 520     JOIN = area_join_procedure,
 521     HASHES,
 522     SORT1 = &lt;&lt;&lt;,
 523     SORT2 = &lt;&lt;&lt;
 524     -- Since sort operators were given, MERGES is implied.
 525     -- LTCMP and GTCMP are assumed to be &lt; and &gt; respectively
 526 );</PRE
 527 ><P>
 528   </P
 529 ></DIV
 530 ><DIV
 531 CLASS="REFSECT1"
 532 ><A
 533 NAME="AEN37579"
 534 ></A
 535 ><H2
 536 >Compatibility</H2
 537 ><P
 538 >   <TT
 539 CLASS="COMMAND"
 540 >CREATE OPERATOR</TT
 541 > is a
 542    <SPAN
 543 CLASS="PRODUCTNAME"
 544 >PostgreSQL</SPAN
 545 > extension.  There are no
 546    provisions for user-defined operators in the SQL standard.
 547   </P
 548 ></DIV
 549 ><DIV
 550 CLASS="NAVFOOTER"
 551 ><HR
 552 ALIGN="LEFT"
 553 WIDTH="100%"><TABLE
 554 SUMMARY="Footer navigation table"
 555 WIDTH="100%"
 556 BORDER="0"
 557 CELLPADDING="0"
 558 CELLSPACING="0"
 559 ><TR
 560 ><TD
 561 WIDTH="33%"
 562 ALIGN="left"
 563 VALIGN="top"
 564 ><A
 565 HREF="sql-createlanguage.html"
 566 ACCESSKEY="P"
 567 >Prev</A
 568 ></TD
 569 ><TD
 570 WIDTH="34%"
 571 ALIGN="center"
 572 VALIGN="top"
 573 ><A
 574 HREF="index.html"
 575 ACCESSKEY="H"
 576 >Home</A
 577 ></TD
 578 ><TD
 579 WIDTH="33%"
 580 ALIGN="right"
 581 VALIGN="top"
 582 ><A
 583 HREF="sql-createopclass.html"
 584 ACCESSKEY="N"
 585 >Next</A
 586 ></TD
 587 ></TR
 588 ><TR
 589 ><TD
 590 WIDTH="33%"
 591 ALIGN="left"
 592 VALIGN="top"
 593 >CREATE LANGUAGE</TD
 594 ><TD
 595 WIDTH="34%"
 596 ALIGN="center"
 597 VALIGN="top"
 598 ><A
 599 HREF="sql-commands.html"
 600 ACCESSKEY="U"
 601 >Up</A
 602 ></TD
 603 ><TD
 604 WIDTH="33%"
 605 ALIGN="right"
 606 VALIGN="top"
 607 >CREATE OPERATOR CLASS</TD
 608 ></TR
 609 ></TABLE
 610 ></DIV
 611 ></BODY
 612 ></HTML
 613 >