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.26 2002/04/16 23:08:10 tgl Exp $
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="SQL-CREATEOPERATOR">
   7  <refmeta>
   8   <refentrytitle id="sql-createoperator-title">
   9    CREATE OPERATOR
  10   </refentrytitle>
  11   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  12  </refmeta>
  13  <refnamediv>
  14   <refname>
  15    CREATE OPERATOR
  16   </refname>
  17   <refpurpose>
  18    define a new operator
  19   </refpurpose>
  20   </refnamediv>
  21  <refsynopsisdiv>
  22   <refsynopsisdivinfo>
  23    <date>2000-03-25</date>
  24   </refsynopsisdivinfo>
  25   <synopsis>
  26 CREATE OPERATOR <replaceable>name</replaceable> ( PROCEDURE = <replaceable class="parameter">func_name</replaceable>
  27      [, LEFTARG = <replaceable class="parameter">lefttype</replaceable>
  28      ] [, RIGHTARG = <replaceable class="parameter">righttype</replaceable> ]
  29      [, COMMUTATOR = <replaceable class="parameter">com_op</replaceable> ] [, NEGATOR = <replaceable class="parameter">neg_op</replaceable> ]
  30      [, RESTRICT = <replaceable class="parameter">res_proc</replaceable> ] [, JOIN = <replaceable class="parameter">join_proc</replaceable> ]
  31      [, HASHES ] [, MERGES ]
  32      [, SORT1 = <replaceable class="parameter">left_sort_op</replaceable> ] [, SORT2 = <replaceable class="parameter">right_sort_op</replaceable> ]
  33      [, LTCMP = <replaceable class="parameter">less_than_op</replaceable> ] [, GTCMP = <replaceable class="parameter">greater_than_op</replaceable> ] )
  34   </synopsis>
  35
  36   <refsect2 id="R2-SQL-CREATEOPERATOR-1">
  37    <refsect2info>
  38     <date>2000-03-25</date>
  39    </refsect2info>
  40    <title>
  41     Inputs
  42    </title>
  43    <para>
  44
  45     <variablelist>
  46      <varlistentry>
  47       <term><replaceable class="parameter">name</replaceable></term>
  48       <listitem>
  49        <para>
  50         The operator to be defined. See below for allowable characters.
  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
 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    The operator <replaceable class="parameter">name</replaceable>
 206    is a sequence of up to <symbol>NAMEDATALEN</>-1 (31 by default) characters
 207    from the following list:
 208    <literallayout>
 209 + - * / &lt; &gt; = ~ ! @ # % ^ &amp; | ` ? $
 210    </literallayout>
 211
 212    There are a few restrictions on your choice of name:
 213    <itemizedlist>
 214     <listitem>
 215      <para>
 216      <literal>$</literal> cannot be defined as a single-character operator,
 217      although it can be part of a multicharacter operator name.
 218      </para>
 219     </listitem>
 220     <listitem>
 221      <para>
 222      <literal>--</literal> and <literal>/*</literal> cannot appear anywhere in an operator name,
 223      since they will be taken as the start of a comment.
 224      </para>
 225     </listitem>
 226     <listitem>
 227      <para>
 228      A multicharacter operator name cannot end in <literal>+</literal> or
 229      <literal>-</literal>,
 230      unless the name also contains at least one of these characters:
 231      <literallayout>
 232 ~ ! @ # % ^ &amp; | ` ? $
 233      </literallayout>
 234      For example, <literal>@-</literal> is an allowed operator name,
 235      but <literal>*-</literal> is not.
 236      This restriction allows <productname>PostgreSQL</productname> to
 237      parse SQL-compliant queries without requiring spaces between tokens.
 238      </para>
 239     </listitem>
 240    </itemizedlist>
 241
 242    <note>
 243     <para>
 244      When working with non-SQL-standard operator names, you will usually
 245      need to separate adjacent operators with spaces to avoid ambiguity.
 246      For example, if you have defined a left-unary operator named <literal>@</literal>,
 247      you cannot write <literal>X*@Y</literal>; you must write
 248      <literal>X* @Y</literal> to ensure that
 249      <productname>PostgreSQL</productname> reads it as two operator names
 250      not one.
 251      </para>
 252    </note>
 253   </para>
 254   <para>
 255    The operator <literal>!=</literal> is mapped to <literal>&lt;&gt;</literal> on input, so these two names
 256    are always equivalent.
 257   </para>
 258   <para>
 259    At least one of LEFTARG and RIGHTARG must be defined.  For
 260    binary operators, both should be defined. For right  unary
 261    operators,  only  LEFTARG  should  be defined, while for left
 262    unary operators only RIGHTARG should be defined.
 263   </para>
 264   <para>
 265    The
 266    <replaceable class="parameter">func_name</replaceable> procedure must have
 267    been previously defined using <command>CREATE FUNCTION</command> and  must
 268    be defined to accept the correct number of arguments
 269    (either  one or two) of the indicated types.
 270   </para>
 271   <para>
 272    The commutator operator should be identified if one exists,
 273    so that <productname>PostgreSQL</productname> can
 274    reverse the order of the operands if it wishes.
 275    For example, the operator area-less-than, &lt;&lt;&lt;,
 276    would probably have a commutator
 277    operator, area-greater-than, &gt;&gt;&gt;.
 278    Hence, the query optimizer could freely  convert:
 279
 280    <programlisting>
 281 box '((0,0), (1,1))'  &gt;&gt;&gt; MYBOXES.description
 282    </programlisting>
 283
 284    to
 285
 286    <programlisting>
 287 MYBOXES.description &lt;&lt;&lt; box '((0,0), (1,1))'
 288    </programlisting>
 289   </para>
 290   <para>
 291    This  allows  the  execution code to always use the latter
 292    representation and simplifies the  query  optimizer  somewhat.
 293   </para>
 294   <para>
 295    Similarly, if there is a negator operator then it should be
 296    identified.
 297    Suppose  that  an
 298    operator,  area-equal, ===, exists, as well as an area not
 299    equal, !==.
 300    The negator link allows the query optimizer to simplify
 301    <programlisting>
 302 NOT MYBOXES.description === box '((0,0), (1,1))'
 303    </programlisting>
 304    to
 305    <programlisting>
 306 MYBOXES.description !== box '((0,0), (1,1))'
 307    </programlisting>
 308   </para>
 309   <para>
 310    If  a  commutator  operator  name  is  supplied,
 311    <productname>PostgreSQL</productname>
 312    searches  for  it  in  the catalog.  If it is found and it
 313    does not yet have a commutator itself, then the commutator's
 314    entry is updated to have the newly created operator as its
 315    commutator.  This applies to the negator, as well.
 316    This  is to allow the definition of two operators that are
 317    the commutators or the negators of each other.  The  first
 318    operator should be defined without a commutator or negator
 319    (as appropriate).  When the second  operator  is  defined,
 320    name  the  first  as the commutator or negator.  The first
 321    will be updated as a side effect.  (As of
 322    <application>PostgreSQL</application> <literal>6.5</literal>,
 323    it also works to just have both operators refer to each other.)
 324   </para>
 325   <para>
 326    The HASHES, MERGES, SORT1, SORT2, LTCMP, and GTCMP options are present to
 327    support the query optimizer in performing joins.
 328    <productname>PostgreSQL</productname> can always evaluate a join (i.e.,
 329    processing a clause with two tuple variables separated by an operator that
 330    returns a <type>boolean</type>) by iterative substitution [WONG76].  In
 331    addition, <productname>PostgreSQL</productname> can use a hash-join
 332    algorithm along the lines of [SHAP86]; however, it must know whether this
 333    strategy is applicable.  The current hash-join algorithm is only correct
 334    for operators that represent equality tests; furthermore, equality of the
 335    data type must mean bitwise equality of the representation of the type.
 336    (For example, a data type that contains unused bits that don't matter for
 337    equality tests could not be hash-joined.)  The HASHES flag indicates to the
 338    query optimizer that a hash join may safely be used with this
 339    operator.
 340   </para>
 341   <para>
 342    Similarly, the MERGES flag indicates whether merge-sort is a usable join
 343    strategy for this operator.  A merge join requires that the two input
 344    datatypes have consistent orderings, and that the mergejoin operator
 345    behave like equality with respect to that ordering.  For example, it is
 346    possible to merge-join equality between an integer and a float variable by
 347    sorting both inputs in ordinary
 348    numeric order.  Execution of a merge join requires that the system be
 349    able to identify four operators related to the mergejoin equality operator:
 350    less-than comparison for the left input datatype,
 351    less-than comparison for the right input datatype,
 352    less-than comparison between the two datatypes, and
 353    greater-than comparison between the two datatypes.  It is possible to
 354    specify these by name, as the SORT1, SORT2, LTCMP, and GTCMP options
 355    respectively.  The system will fill in the default names <literal>&lt;</>,
 356    <literal>&lt;</>, <literal>&lt;</>, <literal>&gt;</> respectively if
 357    any of these are omitted when MERGES is specified.  Also, MERGES will
 358    be assumed to be implied if any of these four operator options appear.
 359   </para>
 360   <para>
 361    If  other join strategies are found to be practical,
 362    <productname>PostgreSQL</productname>
 363    will change the optimizer and run-time system to  use
 364    them  and  will  require  additional specification when an
 365    operator is defined.  Fortunately, the research  community
 366    invents  new  join  strategies infrequently, and the added
 367    generality of user-defined join strategies was not felt to
 368    be worth the complexity involved.
 369   </para>
 370   <para>
 371    The RESTRICT and JOIN options assist the query optimizer in estimating
 372    result sizes.  If a clause of the form:
 373    <programlisting>
 374 MYBOXES.description &lt;&lt;&lt; box '((0,0), (1,1))'
 375    </programlisting>
 376    is present in the qualification,
 377    then <productname>PostgreSQL</productname> may have to
 378    estimate the fraction of the  instances  in  MYBOXES  that
 379    satisfy  the clause.  The function
 380    <replaceable class="parameter">res_proc</replaceable>
 381    must be a registered function (meaning  it  is  already  defined  using
 382    <command>CREATE FUNCTION</command>) which accepts arguments of the correct
 383    data types and returns a floating-point number.   The
 384    query  optimizer  simply  calls this function, passing the
 385    parameter <literal>((0,0), (1,1))</literal> and multiplies the result by the relation
 386    size to get the expected number of instances.
 387   </para>
 388   <para>
 389    Similarly, when the operands of the operator both  contain
 390    instance  variables, the query optimizer must estimate the
 391    size of the resulting join.  The function  join_proc  will
 392    return  another floating-point number which will be multiplied
 393    by the cardinalities of the two tables involved  to
 394    compute the expected result size.
 395   </para>
 396   <para>
 397    The difference between the function
 398    <programlisting>
 399 my_procedure_1 (MYBOXES.description, box '((0,0), (1,1))')
 400    </programlisting>
 401    and the operator
 402    <programlisting>
 403 MYBOXES.description === box '((0,0), (1,1))'
 404    </programlisting>
 405    is  that  <productname>PostgreSQL</productname>
 406    attempts to optimize operators and can
 407    decide to use an index to restrict the search  space  when
 408    operators  are  involved.  However, there is no attempt to
 409    optimize functions, and they are performed by brute force.
 410    Moreover, functions can have any number of arguments while
 411    operators are restricted to one or two.
 412   </para>
 413
 414   <refsect2 id="R2-SQL-CREATEOPERATOR-3">
 415    <refsect2info>
 416     <date>2000-03-25</date>
 417    </refsect2info>
 418    <title>
 419     Notes
 420    </title>
 421    <para>
 422     Refer to the chapter on operators in the
 423     <citetitle>PostgreSQL User's Guide</citetitle>
 424     for further information.
 425     Refer to <command>DROP OPERATOR</command> to delete
 426     user-defined operators from a database.
 427    </para>
 428   </refsect2>
 429  </refsect1>
 430
 431  <refsect1 id="R1-SQL-CREATEOPERATOR-2">
 432   <title>
 433    Usage
 434   </title>
 435   <para>The following command defines a new operator,
 436    area-equality, for the BOX data type:
 437   </para>
 438   <programlisting>
 439 CREATE OPERATOR === (
 440    LEFTARG = box,
 441    RIGHTARG = box,
 442    PROCEDURE = area_equal_procedure,
 443    COMMUTATOR = ===,
 444    NEGATOR = !==,
 445    RESTRICT = area_restriction_procedure,
 446    JOIN = area_join_procedure,
 447    HASHES,
 448    SORT1 = &lt;&lt;&lt;,
 449    SORT2 = &lt;&lt;&lt;
 450    -- Since sort operators were given, MERGES is implied.
 451    -- LTCMP and GTCMP are assumed to be &lt; and &gt; respectively
 452 );
 453   </programlisting>
 454  </refsect1>
 455
 456  <refsect1 id="R1-SQL-CREATEOPERATOR-3">
 457   <title>
 458    Compatibility
 459   </title>
 460
 461   <refsect2 id="R2-SQL-CREATEOPERATOR-4">
 462    <refsect2info>
 463     <date>2000-03-25</date>
 464    </refsect2info>
 465    <title>
 466     SQL92
 467    </title>
 468
 469    <para>
 470     <command>CREATE OPERATOR</command>
 471     is a <productname>PostgreSQL</productname> extension.
 472     There is no <command>CREATE OPERATOR</command>
 473     statement in <acronym>SQL92</acronym>.
 474    </para>
 475   </refsect2>
 476  </refsect1>
 477 </refentry>
 478
 479 <!-- Keep this comment at the end of the file
 480 Local variables:
 481 mode: sgml
 482 sgml-omittag:nil
 483 sgml-shorttag:t
 484 sgml-minimize-attributes:nil
 485 sgml-always-quote-attributes:t
 486 sgml-indent-step:1
 487 sgml-indent-data:t
 488 sgml-parent-document:nil
 489 sgml-default-dtd-file:"../reference.ced"
 490 sgml-exposed-tags:nil
 491 sgml-local-catalogs:"/usr/lib/sgml/catalog"
 492 sgml-local-ecat-files:nil
 493 End:
 494 -->