granicus.if.org Git - postgresql/blob - doc/src/sgml/ref/create_operator.sgml

   1 <!--
   2 $Header: /cvsroot/pgsql/doc/src/sgml/ref/create_operator.sgml,v 1.29 2002/05/18 15:44:47 petere Exp $
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="SQL-CREATEOPERATOR">
   7  <refmeta>
   8   <refentrytitle id="sql-createoperator-title">CREATE OPERATOR</refentrytitle>
   9   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  10  </refmeta>
  11  <refnamediv>
  12   <refname>
  13    CREATE OPERATOR
  14   </refname>
  15   <refpurpose>
  16    define a new operator
  17   </refpurpose>
  18   </refnamediv>
  19  <refsynopsisdiv>
  20   <refsynopsisdivinfo>
  21    <date>2000-03-25</date>
  22   </refsynopsisdivinfo>
  23   <synopsis>
  24 CREATE OPERATOR <replaceable>name</replaceable> ( PROCEDURE = <replaceable class="parameter">func_name</replaceable>
  25      [, LEFTARG = <replaceable class="parameter">lefttype</replaceable>
  26      ] [, RIGHTARG = <replaceable class="parameter">righttype</replaceable> ]
  27      [, COMMUTATOR = <replaceable class="parameter">com_op</replaceable> ] [, NEGATOR = <replaceable class="parameter">neg_op</replaceable> ]
  28      [, RESTRICT = <replaceable class="parameter">res_proc</replaceable> ] [, JOIN = <replaceable class="parameter">join_proc</replaceable> ]
  29      [, HASHES ] [, MERGES ]
  30      [, SORT1 = <replaceable class="parameter">left_sort_op</replaceable> ] [, SORT2 = <replaceable class="parameter">right_sort_op</replaceable> ]
  31      [, LTCMP = <replaceable class="parameter">less_than_op</replaceable> ] [, GTCMP = <replaceable class="parameter">greater_than_op</replaceable> ] )
  32   </synopsis>
  33
  34   <refsect2 id="R2-SQL-CREATEOPERATOR-1">
  35    <refsect2info>
  36     <date>2000-03-25</date>
  37    </refsect2info>
  38    <title>
  39     Inputs
  40    </title>
  41    <para>
  42
  43     <variablelist>
  44      <varlistentry>
  45       <term><replaceable class="parameter">name</replaceable></term>
  46       <listitem>
  47        <para>
  48         The operator to be defined. See below for allowable characters.
  49         The name may be schema-qualified, for example
  50         <literal>CREATE OPERATOR myschema.+ (...)</>.
  51        </para>
  52       </listitem>
  53      </varlistentry>
  54      <varlistentry>
  55       <term><replaceable class="parameter">func_name</replaceable></term>
  56       <listitem>
  57        <para>
  58         The function used to implement this operator.
  59        </para>
  60       </listitem>
  61      </varlistentry>
  62      <varlistentry>
  63       <term><replaceable class="parameter">lefttype</replaceable></term>
  64       <listitem>
  65        <para>
  66         The type of the left-hand argument of the operator, if any.
  67         This option would be omitted for a left-unary operator.
  68        </para>
  69       </listitem>
  70      </varlistentry>
  71      <varlistentry>
  72       <term><replaceable class="parameter">righttype</replaceable></term>
  73       <listitem>
  74        <para>
  75         The type of the right-hand argument of the operator, if any.
  76         This option would be omitted for a right-unary operator.
  77        </para>
  78       </listitem>
  79      </varlistentry>
  80      <varlistentry>
  81       <term><replaceable class="parameter">com_op</replaceable></term>
  82       <listitem>
  83        <para>
  84         The commutator of this operator.
  85        </para>
  86       </listitem>
  87      </varlistentry>
  88      <varlistentry>
  89       <term><replaceable class="parameter">neg_op</replaceable></term>
  90       <listitem>
  91        <para>
  92         The negator of this operator.
  93        </para>
  94       </listitem>
  95      </varlistentry>
  96      <varlistentry>
  97       <term><replaceable class="parameter">res_proc</replaceable></term>
  98       <listitem>
  99        <para>
 100         The restriction selectivity estimator function for this operator.
 101        </para>
 102       </listitem>
 103      </varlistentry>
 104      <varlistentry>
 105       <term><replaceable class="parameter">join_proc</replaceable></term>
 106       <listitem>
 107        <para>
 108         The join selectivity estimator function for this operator.
 109        </para>
 110       </listitem>
 111      </varlistentry>
 112      <varlistentry>
 113       <term>HASHES</term>
 114       <listitem>
 115        <para>
 116        Indicates this operator can support a hash join.
 117        </para>
 118       </listitem>
 119      </varlistentry>
 120      <varlistentry>
 121       <term>MERGES</term>
 122       <listitem>
 123        <para>
 124        Indicates this operator can support a merge join.
 125        </para>
 126       </listitem>
 127      </varlistentry>
 128      <varlistentry>
 129       <term><replaceable class="parameter">left_sort_op</replaceable></term>
 130       <listitem>
 131        <para>
 132         If this operator can support a merge join, the less-than
 133         operator that sorts the left-hand data type of this operator.
 134        </para>
 135       </listitem>
 136      </varlistentry>
 137      <varlistentry>
 138       <term><replaceable class="parameter">right_sort_op</replaceable></term>
 139       <listitem>
 140        <para>
 141         If this operator can support a merge join, the less-than
 142         operator that sorts the right-hand data type of this operator.
 143        </para>
 144       </listitem>
 145      </varlistentry>
 146      <varlistentry>
 147       <term><replaceable class="parameter">less_than_op</replaceable></term>
 148       <listitem>
 149        <para>
 150         If this operator can support a merge join, the less-than
 151         operator that compares the input data types of this operator.
 152        </para>
 153       </listitem>
 154      </varlistentry>
 155      <varlistentry>
 156       <term><replaceable class="parameter">greater_than_op</replaceable></term>
 157       <listitem>
 158        <para>
 159         If this operator can support a merge join, the greater-than
 160         operator that compares the input data types of this operator.
 161        </para>
 162       </listitem>
 163      </varlistentry>
 164     </variablelist>
 165    </para>
 166   </refsect2>
 167
 168   <refsect2 id="R2-SQL-CREATEOPERATOR-2">
 169    <refsect2info>
 170     <date>2000-03-25</date>
 171    </refsect2info>
 172    <title>
 173     Outputs
 174    </title>
 175    <para>
 176     <variablelist>
 177      <varlistentry>
 178       <term><computeroutput>
 179 CREATE OPERATOR
 180        </computeroutput></term>
 181       <listitem>
 182        <para>
 183         Message returned if the operator is successfully created.
 184        </para>
 185       </listitem>
 186      </varlistentry>
 187     </variablelist>
 188    </para>
 189   </refsect2>
 190  </refsynopsisdiv>
 191
 192  <refsect1 id="R1-SQL-CREATEOPERATOR-1">
 193   <refsect1info>
 194    <date>2000-03-25</date>
 195   </refsect1info>
 196   <title>
 197    Description
 198   </title>
 199   <para>
 200    <command>CREATE OPERATOR</command>  defines a new operator,
 201    <replaceable class="parameter">name</replaceable>.
 202    The user who defines an operator becomes its owner.
 203   </para>
 204   <para>
 205    If a schema name is given then the operator is created in the
 206    specified schema.  Otherwise it is created in the current schema (the one
 207    at the front of the search path; see <literal>CURRENT_SCHEMA()</>).
 208   </para>
 209   <para>
 210    Two operators in the same schema can have the same name if they operate on
 211    different data types.  This is called <firstterm>overloading</>.  The
 212    system will attempt to pick the intended operator based on the actual
 213    input data types when there is ambiguity.
 214   </para>
 215
 216   <para>
 217    The operator <replaceable class="parameter">name</replaceable>
 218    is a sequence of up to <symbol>NAMEDATALEN</>-1 (31 by default) characters
 219    from the following list:
 220    <literallayout>
 221 + - * / &lt; &gt; = ~ ! @ # % ^ &amp; | ` ? $
 222    </literallayout>
 223
 224    There are a few restrictions on your choice of name:
 225    <itemizedlist>
 226     <listitem>
 227      <para>
 228      <literal>$</literal> cannot be defined as a single-character operator,
 229      although it can be part of a multicharacter operator name.
 230      </para>
 231     </listitem>
 232     <listitem>
 233      <para>
 234      <literal>--</literal> and <literal>/*</literal> cannot appear anywhere in an operator name,
 235      since they will be taken as the start of a comment.
 236      </para>
 237     </listitem>
 238     <listitem>
 239      <para>
 240      A multicharacter operator name cannot end in <literal>+</literal> or
 241      <literal>-</literal>,
 242      unless the name also contains at least one of these characters:
 243      <literallayout>
 244 ~ ! @ # % ^ &amp; | ` ? $
 245      </literallayout>
 246      For example, <literal>@-</literal> is an allowed operator name,
 247      but <literal>*-</literal> is not.
 248      This restriction allows <productname>PostgreSQL</productname> to
 249      parse SQL-compliant queries without requiring spaces between tokens.
 250      </para>
 251     </listitem>
 252    </itemizedlist>
 253
 254    <note>
 255     <para>
 256      When working with non-SQL-standard operator names, you will usually
 257      need to separate adjacent operators with spaces to avoid ambiguity.
 258      For example, if you have defined a left-unary operator named <literal>@</literal>,
 259      you cannot write <literal>X*@Y</literal>; you must write
 260      <literal>X* @Y</literal> to ensure that
 261      <productname>PostgreSQL</productname> reads it as two operator names
 262      not one.
 263      </para>
 264    </note>
 265   </para>
 266   <para>
 267    The operator <literal>!=</literal> is mapped to <literal>&lt;&gt;</literal> on input, so these two names
 268    are always equivalent.
 269   </para>
 270   <para>
 271    At least one of LEFTARG and RIGHTARG must be defined.  For
 272    binary operators, both should be defined. For right  unary
 273    operators,  only  LEFTARG  should  be defined, while for left
 274    unary operators only RIGHTARG should be defined.
 275   </para>
 276   <para>
 277    The
 278    <replaceable class="parameter">func_name</replaceable> procedure must have
 279    been previously defined using <command>CREATE FUNCTION</command> and  must
 280    be defined to accept the correct number of arguments
 281    (either  one or two) of the indicated types.
 282   </para>
 283   <para>
 284    The commutator operator should be identified if one exists,
 285    so that <productname>PostgreSQL</productname> can
 286    reverse the order of the operands if it wishes.
 287    For example, the operator area-less-than, &lt;&lt;&lt;,
 288    would probably have a commutator
 289    operator, area-greater-than, &gt;&gt;&gt;.
 290    Hence, the query optimizer could freely  convert:
 291
 292    <programlisting>
 293 box '((0,0), (1,1))'  &gt;&gt;&gt; MYBOXES.description
 294    </programlisting>
 295
 296    to
 297
 298    <programlisting>
 299 MYBOXES.description &lt;&lt;&lt; box '((0,0), (1,1))'
 300    </programlisting>
 301   </para>
 302   <para>
 303    This  allows  the  execution code to always use the latter
 304    representation and simplifies the  query  optimizer  somewhat.
 305   </para>
 306   <para>
 307    Similarly, if there is a negator operator then it should be
 308    identified.
 309    Suppose  that  an
 310    operator,  area-equal, ===, exists, as well as an area not
 311    equal, !==.
 312    The negator link allows the query optimizer to simplify
 313    <programlisting>
 314 NOT MYBOXES.description === box '((0,0), (1,1))'
 315    </programlisting>
 316    to
 317    <programlisting>
 318 MYBOXES.description !== box '((0,0), (1,1))'
 319    </programlisting>
 320   </para>
 321   <para>
 322    If  a  commutator  operator  name  is  supplied,
 323    <productname>PostgreSQL</productname>
 324    searches  for  it  in  the catalog.  If it is found and it
 325    does not yet have a commutator itself, then the commutator's
 326    entry is updated to have the newly created operator as its
 327    commutator.  This applies to the negator, as well.
 328    This  is to allow the definition of two operators that are
 329    the commutators or the negators of each other.  The  first
 330    operator should be defined without a commutator or negator
 331    (as appropriate).  When the second  operator  is  defined,
 332    name  the  first  as the commutator or negator.  The first
 333    will be updated as a side effect.  (As of
 334    <application>PostgreSQL</application> <literal>6.5</literal>,
 335    it also works to just have both operators refer to each other.)
 336   </para>
 337   <para>
 338    The HASHES, MERGES, SORT1, SORT2, LTCMP, and GTCMP options are present to
 339    support the query optimizer in performing joins.
 340    <productname>PostgreSQL</productname> can always evaluate a join (i.e.,
 341    processing a clause with two tuple variables separated by an operator that
 342    returns a <type>boolean</type>) by iterative substitution [WONG76].  In
 343    addition, <productname>PostgreSQL</productname> can use a hash-join
 344    algorithm along the lines of [SHAP86]; however, it must know whether this
 345    strategy is applicable.  The current hash-join algorithm is only correct
 346    for operators that represent equality tests; furthermore, equality of the
 347    data type must mean bitwise equality of the representation of the type.
 348    (For example, a data type that contains unused bits that don't matter for
 349    equality tests could not be hash-joined.)  The HASHES flag indicates to the
 350    query optimizer that a hash join may safely be used with this
 351    operator.
 352   </para>
 353   <para>
 354    Similarly, the MERGES flag indicates whether merge-sort is a usable join
 355    strategy for this operator.  A merge join requires that the two input
 356    datatypes have consistent orderings, and that the mergejoin operator
 357    behave like equality with respect to that ordering.  For example, it is
 358    possible to merge-join equality between an integer and a float variable by
 359    sorting both inputs in ordinary
 360    numeric order.  Execution of a merge join requires that the system be
 361    able to identify four operators related to the mergejoin equality operator:
 362    less-than comparison for the left input datatype,
 363    less-than comparison for the right input datatype,
 364    less-than comparison between the two datatypes, and
 365    greater-than comparison between the two datatypes.  It is possible to
 366    specify these by name, as the SORT1, SORT2, LTCMP, and GTCMP options
 367    respectively.  The system will fill in the default names <literal>&lt;</>,
 368    <literal>&lt;</>, <literal>&lt;</>, <literal>&gt;</> respectively if
 369    any of these are omitted when MERGES is specified.  Also, MERGES will
 370    be assumed to be implied if any of these four operator options appear.
 371   </para>
 372   <para>
 373    If  other join strategies are found to be practical,
 374    <productname>PostgreSQL</productname>
 375    will change the optimizer and run-time system to  use
 376    them  and  will  require  additional specification when an
 377    operator is defined.  Fortunately, the research  community
 378    invents  new  join  strategies infrequently, and the added
 379    generality of user-defined join strategies was not felt to
 380    be worth the complexity involved.
 381   </para>
 382   <para>
 383    The RESTRICT and JOIN options assist the query optimizer in estimating
 384    result sizes.  If a clause of the form:
 385    <programlisting>
 386 MYBOXES.description &lt;&lt;&lt; box '((0,0), (1,1))'
 387    </programlisting>
 388    is present in the qualification,
 389    then <productname>PostgreSQL</productname> may have to
 390    estimate the fraction of the  instances  in  MYBOXES  that
 391    satisfy  the clause.  The function
 392    <replaceable class="parameter">res_proc</replaceable>
 393    must be a registered function (meaning  it  is  already  defined  using
 394    <command>CREATE FUNCTION</command>) which accepts arguments of the correct
 395    data types and returns a floating-point number.   The
 396    query  optimizer  simply  calls this function, passing the
 397    parameter <literal>((0,0), (1,1))</literal> and multiplies the result by the relation
 398    size to get the expected number of instances.
 399   </para>
 400   <para>
 401    Similarly, when the operands of the operator both  contain
 402    instance  variables, the query optimizer must estimate the
 403    size of the resulting join.  The function  join_proc  will
 404    return  another floating-point number which will be multiplied
 405    by the cardinalities of the two tables involved  to
 406    compute the expected result size.
 407   </para>
 408   <para>
 409    The difference between the function
 410    <programlisting>
 411 my_procedure_1 (MYBOXES.description, box '((0,0), (1,1))')
 412    </programlisting>
 413    and the operator
 414    <programlisting>
 415 MYBOXES.description === box '((0,0), (1,1))'
 416    </programlisting>
 417    is  that  <productname>PostgreSQL</productname>
 418    attempts to optimize operators and can
 419    decide to use an index to restrict the search  space  when
 420    operators  are  involved.  However, there is no attempt to
 421    optimize functions, and they are performed by brute force.
 422    Moreover, functions can have any number of arguments while
 423    operators are restricted to one or two.
 424   </para>
 425
 426   <refsect2 id="R2-SQL-CREATEOPERATOR-3">
 427    <refsect2info>
 428     <date>2000-03-25</date>
 429    </refsect2info>
 430    <title>
 431     Notes
 432    </title>
 433    <para>
 434     Refer to the chapter on operators in the
 435     <citetitle>PostgreSQL User's Guide</citetitle>
 436     for further information.
 437     Refer to <command>DROP OPERATOR</command> to delete
 438     user-defined operators from a database.
 439    </para>
 440   </refsect2>
 441  </refsect1>
 442
 443  <refsect1 id="R1-SQL-CREATEOPERATOR-2">
 444   <title>
 445    Usage
 446   </title>
 447   <para>The following command defines a new operator,
 448    area-equality, for the BOX data type:
 449   </para>
 450   <programlisting>
 451 CREATE OPERATOR === (
 452    LEFTARG = box,
 453    RIGHTARG = box,
 454    PROCEDURE = area_equal_procedure,
 455    COMMUTATOR = ===,
 456    NEGATOR = !==,
 457    RESTRICT = area_restriction_procedure,
 458    JOIN = area_join_procedure,
 459    HASHES,
 460    SORT1 = &lt;&lt;&lt;,
 461    SORT2 = &lt;&lt;&lt;
 462    -- Since sort operators were given, MERGES is implied.
 463    -- LTCMP and GTCMP are assumed to be &lt; and &gt; respectively
 464 );
 465   </programlisting>
 466  </refsect1>
 467
 468  <refsect1 id="R1-SQL-CREATEOPERATOR-3">
 469   <title>
 470    Compatibility
 471   </title>
 472
 473   <refsect2 id="R2-SQL-CREATEOPERATOR-4">
 474    <refsect2info>
 475     <date>2000-03-25</date>
 476    </refsect2info>
 477    <title>
 478     SQL92
 479    </title>
 480
 481    <para>
 482     <command>CREATE OPERATOR</command>
 483     is a <productname>PostgreSQL</productname> extension.
 484     There is no <command>CREATE OPERATOR</command>
 485     statement in <acronym>SQL92</acronym>.
 486    </para>
 487   </refsect2>
 488  </refsect1>
 489 </refentry>
 490
 491 <!-- Keep this comment at the end of the file
 492 Local variables:
 493 mode: sgml
 494 sgml-omittag:nil
 495 sgml-shorttag:t
 496 sgml-minimize-attributes:nil
 497 sgml-always-quote-attributes:t
 498 sgml-indent-step:1
 499 sgml-indent-data:t
 500 sgml-parent-document:nil
 501 sgml-default-dtd-file:"../reference.ced"
 502 sgml-exposed-tags:nil
 503 sgml-local-catalogs:"/usr/lib/sgml/catalog"
 504 sgml-local-ecat-files:nil
 505 End:
 506 -->