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

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