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