doc/src/sgml/ref/create_operator.sgml

   1 <!--
   2 doc/src/sgml/ref/create_operator.sgml
   3 PostgreSQL documentation
   4 -->
   5
   6 <refentry id="sql-createoperator">
   7  <indexterm zone="sql-createoperator">
   8   <primary>CREATE OPERATOR</primary>
   9  </indexterm>
  10
  11  <refmeta>
  12   <refentrytitle>CREATE OPERATOR</refentrytitle>
  13   <manvolnum>7</manvolnum>
  14   <refmiscinfo>SQL - Language Statements</refmiscinfo>
  15  </refmeta>
  16
  17  <refnamediv>
  18   <refname>CREATE OPERATOR</refname>
  19   <refpurpose>define a new operator</refpurpose>
  20  </refnamediv>
  21
  22  <refsynopsisdiv>
  23 <synopsis>
  24 CREATE OPERATOR <replaceable>name</replaceable> (
  25     {FUNCTION|PROCEDURE} = <replaceable class="parameter">function_name</replaceable>
  26     [, LEFTARG = <replaceable class="parameter">left_type</replaceable> ] [, RIGHTARG = <replaceable class="parameter">right_type</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 )
  31 </synopsis>
  32  </refsynopsisdiv>
  33
  34  <refsect1>
  35   <title>Description</title>
  36
  37   <para>
  38    <command>CREATE OPERATOR</command> defines a new operator,
  39    <replaceable class="parameter">name</replaceable>.  The user who
  40    defines an operator becomes its owner.  If a schema name is given
  41    then the operator is created in the specified schema.  Otherwise it
  42    is created in the current schema.
  43   </para>
  44
  45   <para>
  46    The operator name is a sequence of up to <symbol>NAMEDATALEN</symbol>-1
  47    (63 by default) characters from the following list:
  48 <literallayout>
  49 + - * / &lt; &gt; = ~ ! @ # % ^ &amp; | ` ?
  50 </literallayout>
  51
  52    There are a few restrictions on your choice of name:
  53    <itemizedlist>
  54     <listitem>
  55      <para>
  56      <literal>--</literal> and <literal>/*</literal> cannot appear anywhere in an operator name,
  57      since they will be taken as the start of a comment.
  58      </para>
  59     </listitem>
  60     <listitem>
  61      <para>
  62      A multicharacter operator name cannot end in <literal>+</literal> or
  63      <literal>-</literal>,
  64      unless the name also contains at least one of these characters:
  65 <literallayout>
  66 ~ ! @ # % ^ &amp; | ` ?
  67 </literallayout>
  68      For example, <literal>@-</literal> is an allowed operator name,
  69      but <literal>*-</literal> is not.
  70      This restriction allows <productname>PostgreSQL</productname> to
  71      parse SQL-compliant commands without requiring spaces between tokens.
  72      </para>
  73     </listitem>
  74     <listitem>
  75      <para>
  76      The symbol <literal>=&gt;</literal> is reserved by the SQL grammar,
  77      so it cannot be used as an operator name.
  78      </para>
  79     </listitem>
  80    </itemizedlist>
  81   </para>
  82
  83   <para>
  84    The operator <literal>!=</literal> is mapped to
  85    <literal>&lt;&gt;</literal> on input, so these two names are always
  86    equivalent.
  87   </para>
  88
  89   <para>
  90    For binary operators, both <literal>LEFTARG</literal> and
  91    <literal>RIGHTARG</literal> must be defined.  For prefix operators only
  92    <literal>RIGHTARG</literal> should be defined.
  93    The <replaceable class="parameter">function_name</replaceable>
  94    function must have been previously defined using <command>CREATE
  95    FUNCTION</command> and must be defined to accept the correct number
  96    of arguments (either one or two) of the indicated types.
  97   </para>
  98
  99   <para>
 100    In the syntax of <literal>CREATE OPERATOR</literal>, the keywords
 101    <literal>FUNCTION</literal> and <literal>PROCEDURE</literal> are
 102    equivalent, but the referenced function must in any case be a function, not
 103    a procedure.  The use of the keyword <literal>PROCEDURE</literal> here is
 104    historical and deprecated.
 105   </para>
 106
 107   <para>
 108    The other clauses specify optional operator optimization attributes.
 109    Their meaning is detailed in <xref linkend="xoper-optimization"/>.
 110   </para>
 111
 112   <para>
 113    To be able to create an operator, you must have <literal>USAGE</literal>
 114    privilege on the argument types and the return type, as well
 115    as <literal>EXECUTE</literal> privilege on the underlying function.  If a
 116    commutator or negator operator is specified, you must own those operators.
 117   </para>
 118  </refsect1>
 119
 120  <refsect1>
 121   <title>Parameters</title>
 122
 123     <variablelist>
 124      <varlistentry>
 125       <term><replaceable class="parameter">name</replaceable></term>
 126       <listitem>
 127        <para>
 128         The name of the operator to be defined. See above for allowable
 129         characters.  The name can be schema-qualified, for example
 130         <literal>CREATE OPERATOR myschema.+ (...)</literal>.  If not, then
 131         the operator is created in the current schema.  Two operators
 132         in the same schema can have the same name if they operate on
 133         different data types.  This is called
 134         <firstterm>overloading</firstterm>.
 135        </para>
 136       </listitem>
 137      </varlistentry>
 138
 139      <varlistentry>
 140       <term><replaceable class="parameter">function_name</replaceable></term>
 141       <listitem>
 142        <para>
 143         The function used to implement this operator.
 144        </para>
 145       </listitem>
 146      </varlistentry>
 147
 148      <varlistentry>
 149       <term><replaceable class="parameter">left_type</replaceable></term>
 150       <listitem>
 151        <para>
 152         The data type of the operator's left operand, if any.
 153         This option would be omitted for a prefix operator.
 154        </para>
 155       </listitem>
 156      </varlistentry>
 157
 158      <varlistentry>
 159       <term><replaceable class="parameter">right_type</replaceable></term>
 160       <listitem>
 161        <para>
 162         The data type of the operator's right operand.
 163        </para>
 164       </listitem>
 165      </varlistentry>
 166
 167      <varlistentry>
 168       <term><replaceable class="parameter">com_op</replaceable></term>
 169       <listitem>
 170        <para>
 171         The commutator of this operator.
 172        </para>
 173       </listitem>
 174      </varlistentry>
 175
 176      <varlistentry>
 177       <term><replaceable class="parameter">neg_op</replaceable></term>
 178       <listitem>
 179        <para>
 180         The negator of this operator.
 181        </para>
 182       </listitem>
 183      </varlistentry>
 184
 185      <varlistentry>
 186       <term><replaceable class="parameter">res_proc</replaceable></term>
 187       <listitem>
 188        <para>
 189         The restriction selectivity estimator function for this operator.
 190        </para>
 191       </listitem>
 192      </varlistentry>
 193
 194      <varlistentry>
 195       <term><replaceable class="parameter">join_proc</replaceable></term>
 196       <listitem>
 197        <para>
 198         The join selectivity estimator function for this operator.
 199        </para>
 200       </listitem>
 201      </varlistentry>
 202
 203      <varlistentry>
 204       <term><literal>HASHES</literal></term>
 205       <listitem>
 206        <para>
 207        Indicates this operator can support a hash join.
 208        </para>
 209       </listitem>
 210      </varlistentry>
 211
 212      <varlistentry>
 213       <term><literal>MERGES</literal></term>
 214       <listitem>
 215        <para>
 216        Indicates this operator can support a merge join.
 217        </para>
 218       </listitem>
 219      </varlistentry>
 220     </variablelist>
 221
 222   <para>
 223    To give a schema-qualified operator name in <replaceable
 224    class="parameter">com_op</replaceable> or the other optional
 225    arguments, use the <literal>OPERATOR()</literal> syntax, for example:
 226 <programlisting>
 227 COMMUTATOR = OPERATOR(myschema.===) ,
 228 </programlisting></para>
 229  </refsect1>
 230
 231  <refsect1>
 232   <title>Notes</title>
 233
 234   <para>
 235    Refer to <xref linkend="xoper"/> and <xref linkend="xoper-optimization"/>
 236    for further information.
 237   </para>
 238
 239   <para>
 240    When you are defining a self-commutative operator, you just do it.
 241    When you are defining a pair of commutative operators, things are
 242    a little trickier: how can the first one to be defined refer to the
 243    other one, which you haven't defined yet?  There are three solutions
 244    to this problem:
 245
 246    <itemizedlist>
 247     <listitem>
 248      <para>
 249       One way is to omit the <literal>COMMUTATOR</literal> clause in the
 250       first operator that you define, and then provide one in the second
 251       operator's definition.  Since <productname>PostgreSQL</productname>
 252       knows that commutative operators come in pairs, when it sees the
 253       second definition it will automatically go back and fill in the
 254       missing <literal>COMMUTATOR</literal> clause in the first
 255       definition.
 256      </para>
 257     </listitem>
 258
 259     <listitem>
 260      <para>
 261       Another, more straightforward way is just to
 262       include <literal>COMMUTATOR</literal> clauses in both definitions.
 263       When <productname>PostgreSQL</productname> processes the first
 264       definition and realizes that <literal>COMMUTATOR</literal> refers to
 265       a nonexistent operator, the system will make a dummy entry for that
 266       operator in the system catalog.  This dummy entry will have valid
 267       data only for the operator name, left and right operand types, and
 268       owner, since that's all that <productname>PostgreSQL</productname>
 269       can deduce at this point.  The first operator's catalog entry will
 270       link to this dummy entry.  Later, when you define the second
 271       operator, the system updates the dummy entry with the additional
 272       information from the second definition.  If you try to use the dummy
 273       operator before it's been filled in, you'll just get an error
 274       message.
 275      </para>
 276     </listitem>
 277
 278     <listitem>
 279      <para>
 280       Alternatively, both operators can be defined
 281       without <literal>COMMUTATOR</literal> clauses
 282       and then <command>ALTER OPERATOR</command> can be used to set their
 283       commutator links.  It's sufficient to <command>ALTER</command>
 284       either one of the pair.
 285      </para>
 286     </listitem>
 287    </itemizedlist>
 288
 289    In all three cases, you must own both operators in order to mark
 290    them as commutators.
 291   </para>
 292
 293   <para>
 294    Pairs of negator operators can be defined using the same methods
 295    as for commutator pairs.
 296   </para>
 297
 298   <para>
 299    It is not possible to specify an operator's lexical precedence in
 300    <command>CREATE OPERATOR</command>, because the parser's precedence behavior
 301    is hard-wired.  See <xref linkend="sql-precedence"/> for precedence details.
 302   </para>
 303
 304   <para>
 305    The obsolete options <literal>SORT1</literal>, <literal>SORT2</literal>,
 306    <literal>LTCMP</literal>, and <literal>GTCMP</literal> were formerly used to
 307    specify the names of sort operators associated with a merge-joinable
 308    operator.  This is no longer necessary, since information about
 309    associated operators is found by looking at B-tree operator families
 310    instead.  If one of these options is given, it is ignored except
 311    for implicitly setting <literal>MERGES</literal> true.
 312   </para>
 313
 314   <para>
 315    Use <link linkend="sql-dropoperator"><command>DROP OPERATOR</command></link> to delete user-defined operators
 316    from a database.  Use <link linkend="sql-alteroperator"><command>ALTER OPERATOR</command></link> to modify operators in a
 317    database.
 318   </para>
 319  </refsect1>
 320
 321  <refsect1>
 322   <title>Examples</title>
 323
 324   <para>
 325    The following command defines a new operator, area-equality, for
 326    the data type <type>box</type>:
 327 <programlisting>
 328 CREATE OPERATOR === (
 329     LEFTARG = box,
 330     RIGHTARG = box,
 331     FUNCTION = area_equal_function,
 332     COMMUTATOR = ===,
 333     NEGATOR = !==,
 334     RESTRICT = area_restriction_function,
 335     JOIN = area_join_function,
 336     HASHES, MERGES
 337 );
 338 </programlisting></para>
 339  </refsect1>
 340
 341  <refsect1>
 342   <title>Compatibility</title>
 343
 344   <para>
 345    <command>CREATE OPERATOR</command> is a
 346    <productname>PostgreSQL</productname> extension.  There are no
 347    provisions for user-defined operators in the SQL standard.
 348   </para>
 349  </refsect1>
 350
 351  <refsect1>
 352   <title>See Also</title>
 353
 354   <simplelist type="inline">
 355    <member><xref linkend="sql-alteroperator"/></member>
 356    <member><xref linkend="sql-createopclass"/></member>
 357    <member><xref linkend="sql-dropoperator"/></member>
 358   </simplelist>
 359  </refsect1>
 360 </refentry>