create_operator.sgml 10 KB
Newer Older
1
<!--
2
$PostgreSQL: pgsql/doc/src/sgml/ref/create_operator.sgml,v 1.44 2005/01/04 00:39:53 tgl Exp $
3
PostgreSQL documentation
4 5
-->

6 7
<refentry id="SQL-CREATEOPERATOR">
 <refmeta>
8
  <refentrytitle id="sql-createoperator-title">CREATE OPERATOR</refentrytitle>
9 10
  <refmiscinfo>SQL - Language Statements</refmiscinfo>
 </refmeta>
11

12
 <refnamediv>
13 14 15 16
  <refname>CREATE OPERATOR</refname>
  <refpurpose>define a new operator</refpurpose>
 </refnamediv>

Peter Eisentraut's avatar
Peter Eisentraut committed
17 18 19 20
 <indexterm zone="sql-createoperator">
  <primary>CREATE OPERATOR</primary>
 </indexterm>

21
 <refsynopsisdiv>
22 23
<synopsis>
CREATE OPERATOR <replaceable>name</replaceable> (
24
    PROCEDURE = <replaceable class="parameter">funcname</replaceable>
25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49
    [, LEFTARG = <replaceable class="parameter">lefttype</replaceable> ] [, RIGHTARG = <replaceable class="parameter">righttype</replaceable> ]
    [, COMMUTATOR = <replaceable class="parameter">com_op</replaceable> ] [, NEGATOR = <replaceable class="parameter">neg_op</replaceable> ]
    [, RESTRICT = <replaceable class="parameter">res_proc</replaceable> ] [, JOIN = <replaceable class="parameter">join_proc</replaceable> ]
    [, HASHES ] [, MERGES ]
    [, SORT1 = <replaceable class="parameter">left_sort_op</replaceable> ] [, SORT2 = <replaceable class="parameter">right_sort_op</replaceable> ]
    [, LTCMP = <replaceable class="parameter">less_than_op</replaceable> ] [, GTCMP = <replaceable class="parameter">greater_than_op</replaceable> ]
)
</synopsis>
 </refsynopsisdiv>

 <refsect1>
  <title>Description</title>

  <para>
   <command>CREATE OPERATOR</command> defines a new operator,
   <replaceable class="parameter">name</replaceable>.  The user who
   defines an operator becomes its owner.  If a schema name is given
   then the operator is created in the specified schema.  Otherwise it
   is created in the current schema.
  </para>

  <para>
   The operator name is a sequence of up to <symbol>NAMEDATALEN</>-1
   (63 by default) characters from the following list:
<literallayout>
50
+ - * / &lt; &gt; = ~ ! @ # % ^ &amp; | ` ?
51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66
</literallayout>

   There are a few restrictions on your choice of name:
   <itemizedlist>
    <listitem>
     <para>
     <literal>--</literal> and <literal>/*</literal> cannot appear anywhere in an operator name,
     since they will be taken as the start of a comment.
     </para>
    </listitem>
    <listitem>
     <para>
     A multicharacter operator name cannot end in <literal>+</literal> or
     <literal>-</literal>,
     unless the name also contains at least one of these characters:
<literallayout>
67
~ ! @ # % ^ &amp; | ` ?
68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91
</literallayout>
     For example, <literal>@-</literal> is an allowed operator name,
     but <literal>*-</literal> is not.
     This restriction allows <productname>PostgreSQL</productname> to
     parse SQL-compliant commands without requiring spaces between tokens.
     </para>
    </listitem>
   </itemizedlist>
  </para>

  <para>
   The operator <literal>!=</literal> is mapped to
   <literal>&lt;&gt;</literal> on input, so these two names are always
   equivalent.
  </para>

  <para>
   At least one of <literal>LEFTARG</> and <literal>RIGHTARG</> must be defined.  For
   binary operators, both must be defined. For right  unary
   operators, only <literal>LEFTARG</> should be defined, while for left
   unary operators only <literal>RIGHTARG</> should be defined.
  </para>

  <para>
92
   The <replaceable class="parameter">funcname</replaceable>
93 94 95 96 97 98 99
   procedure must have been previously defined using <command>CREATE
   FUNCTION</command> and must be defined to accept the correct number
   of arguments (either one or two) of the indicated types.
  </para>

  <para>
   The other clauses specify optional operator optimization clauses.
100
   Their meaning is detailed in <xref linkend="xoper-optimization">.
101 102 103 104 105
  </para>
 </refsect1>

 <refsect1>
  <title>Parameters</title>
106 107 108 109 110 111

    <variablelist>
     <varlistentry>
      <term><replaceable class="parameter">name</replaceable></term>
      <listitem>
       <para>
112 113 114 115 116 117 118
        The name of the operator to be defined. See above for allowable
        characters.  The name may be schema-qualified, for example
        <literal>CREATE OPERATOR myschema.+ (...)</>.  If not, then
        the operator is created in the current schema.  Two operators
        in the same schema can have the same name if they operate on
        different data types.  This is called
        <firstterm>overloading</>.
119 120 121
       </para>
      </listitem>
     </varlistentry>
122

123
     <varlistentry>
124
      <term><replaceable class="parameter">funcname</replaceable></term>
125 126
      <listitem>
       <para>
127
        The function used to implement this operator.
128 129 130
       </para>
      </listitem>
     </varlistentry>
131

132
     <varlistentry>
Bruce Momjian's avatar
Bruce Momjian committed
133
      <term><replaceable class="parameter">lefttype</replaceable></term>
134 135
      <listitem>
       <para>
136 137
        The data type of the operator's left operand, if any.
        This option would be omitted for a left-unary operator.
138 139 140
       </para>
      </listitem>
     </varlistentry>
141

142
     <varlistentry>
Bruce Momjian's avatar
Bruce Momjian committed
143
      <term><replaceable class="parameter">righttype</replaceable></term>
144 145
      <listitem>
       <para>
146 147
        The data type of the operator's right operand, if any.
        This option would be omitted for a right-unary operator.
148 149 150
       </para>
      </listitem>
     </varlistentry>
151

152 153 154 155
     <varlistentry>
      <term><replaceable class="parameter">com_op</replaceable></term>
      <listitem>
       <para>
156
        The commutator of this operator.
157 158 159
       </para>
      </listitem>
     </varlistentry>
160

161 162 163 164
     <varlistentry>
      <term><replaceable class="parameter">neg_op</replaceable></term>
      <listitem>
       <para>
165
        The negator of this operator.
166 167 168
       </para>
      </listitem>
     </varlistentry>
169

170 171 172 173
     <varlistentry>
      <term><replaceable class="parameter">res_proc</replaceable></term>
      <listitem>
       <para>
174
        The restriction selectivity estimator function for this operator.
175 176 177
       </para>
      </listitem>
     </varlistentry>
178

179 180 181 182
     <varlistentry>
      <term><replaceable class="parameter">join_proc</replaceable></term>
      <listitem>
       <para>
183
        The join selectivity estimator function for this operator.
184 185 186
       </para>
      </listitem>
     </varlistentry>
187

188
     <varlistentry>
189
      <term><literal>HASHES</literal></term>
190 191
      <listitem>
       <para>
192
       Indicates this operator can support a hash join.
193 194 195
       </para>
      </listitem>
     </varlistentry>
196

197
     <varlistentry>
198
      <term><literal>MERGES</literal></term>
199 200 201 202 203 204
      <listitem>
       <para>
       Indicates this operator can support a merge join.
       </para>
      </listitem>
     </varlistentry>
205

206 207 208 209
     <varlistentry>
      <term><replaceable class="parameter">left_sort_op</replaceable></term>
      <listitem>
       <para>
210 211
        If this operator can support a merge join, the less-than
        operator that sorts the left-hand data type of this operator.
212 213 214
       </para>
      </listitem>
     </varlistentry>
215

216 217 218 219
     <varlistentry>
      <term><replaceable class="parameter">right_sort_op</replaceable></term>
      <listitem>
       <para>
220 221
        If this operator can support a merge join, the less-than
        operator that sorts the right-hand data type of this operator.
222 223 224
       </para>
      </listitem>
     </varlistentry>
225

226 227 228 229
     <varlistentry>
      <term><replaceable class="parameter">less_than_op</replaceable></term>
      <listitem>
       <para>
230 231
        If this operator can support a merge join, the less-than
        operator that compares the input data types of this operator.
232 233 234
       </para>
      </listitem>
     </varlistentry>
235

236 237 238 239
     <varlistentry>
      <term><replaceable class="parameter">greater_than_op</replaceable></term>
      <listitem>
       <para>
240 241
        If this operator can support a merge join, the greater-than
        operator that compares the input data types of this operator.
242 243 244
       </para>
      </listitem>
     </varlistentry>
245 246
    </variablelist>

247
  <para>
248 249 250 251 252 253
   To give a schema-qualified operator name in <replaceable
   class="parameter">com_op</replaceable> or the other optional
   arguments, use the <literal>OPERATOR()</> syntax, for example
<programlisting>
COMMUTATOR = OPERATOR(myschema.===) ,
</programlisting>  
254
  </para>
255 256 257 258
 </refsect1>
  
 <refsect1>
  <title>Notes</title>
259

260
  <para>
261
   Refer to <xref linkend="xoper"> for further information.
262
  </para>
263

264
  <para>
265 266 267 268 269
   Use <xref linkend="sql-dropoperator"
   endterm="sql-dropoperator-title"> to delete user-defined operators
   from a database.  Use <xref linkend="sql-alteroperator"
   endterm="sql-alteroperator-title"> to modify operators in a
   database.
270
  </para>
271
 </refsect1>
272
  
273 274 275 276 277 278 279
 <refsect1>
  <title>Examples</title>

  <para>
   The following command defines a new operator, area-equality, for
   the data type <type>box</type>:
<programlisting>
280
CREATE OPERATOR === (
281 282 283 284 285 286 287 288 289 290 291 292
    LEFTARG = box,
    RIGHTARG = box,
    PROCEDURE = area_equal_procedure,
    COMMUTATOR = ===,
    NEGATOR = !==,
    RESTRICT = area_restriction_procedure,
    JOIN = area_join_procedure,
    HASHES,
    SORT1 = &lt;&lt;&lt;,
    SORT2 = &lt;&lt;&lt;
    -- Since sort operators were given, MERGES is implied.
    -- LTCMP and GTCMP are assumed to be &lt; and &gt; respectively
293
);
294 295
</programlisting>  
  </para>
296
 </refsect1>
297
 
298 299 300 301 302 303 304 305
 <refsect1>
  <title>Compatibility</title>

  <para>
   <command>CREATE OPERATOR</command> is a
   <productname>PostgreSQL</productname> extension.  There are no
   provisions for user-defined operators in the SQL standard.
  </para>
306
 </refsect1>
307 308 309 310 311 312 313 314 315 316

 <refsect1>
  <title>See Also</title>

  <simplelist type="inline">
   <member><xref linkend="sql-alteroperator" endterm="sql-alteroperator-title"></member>
   <member><xref linkend="sql-createopclass" endterm="sql-createopclass-title"></member>
   <member><xref linkend="sql-dropoperator" endterm="sql-dropoperator-title"></member>
  </simplelist>
 </refsect1>
317
</refentry>
318 319 320 321

<!-- Keep this comment at the end of the file
Local variables:
mode: sgml
322
sgml-omittag:nil
323 324 325 326 327 328 329 330 331 332 333
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
sgml-indent-step:1
sgml-indent-data:t
sgml-parent-document:nil
sgml-default-dtd-file:"../reference.ced"
sgml-exposed-tags:nil
sgml-local-catalogs:"/usr/lib/sgml/catalog"
sgml-local-ecat-files:nil
End:
334
-->