2
$PostgreSQL: pgsql/doc/src/sgml/ref/create_operator.sgml,v 1.44 2005-01-04 00:39:53 tgl Exp $
3
PostgreSQL documentation
6
<refentry id="SQL-CREATEOPERATOR">
8
<refentrytitle id="sql-createoperator-title">CREATE OPERATOR</refentrytitle>
9
<refmiscinfo>SQL - Language Statements</refmiscinfo>
13
<refname>CREATE OPERATOR</refname>
14
<refpurpose>define a new operator</refpurpose>
17
<indexterm zone="sql-createoperator">
18
<primary>CREATE OPERATOR</primary>
23
CREATE OPERATOR <replaceable>name</replaceable> (
24
PROCEDURE = <replaceable class="parameter">funcname</replaceable>
25
[, LEFTARG = <replaceable class="parameter">lefttype</replaceable> ] [, RIGHTARG = <replaceable class="parameter">righttype</replaceable> ]
26
[, COMMUTATOR = <replaceable class="parameter">com_op</replaceable> ] [, NEGATOR = <replaceable class="parameter">neg_op</replaceable> ]
27
[, RESTRICT = <replaceable class="parameter">res_proc</replaceable> ] [, JOIN = <replaceable class="parameter">join_proc</replaceable> ]
28
[, HASHES ] [, MERGES ]
29
[, SORT1 = <replaceable class="parameter">left_sort_op</replaceable> ] [, SORT2 = <replaceable class="parameter">right_sort_op</replaceable> ]
30
[, LTCMP = <replaceable class="parameter">less_than_op</replaceable> ] [, GTCMP = <replaceable class="parameter">greater_than_op</replaceable> ]
36
<title>Description</title>
39
<command>CREATE OPERATOR</command> defines a new operator,
40
<replaceable class="parameter">name</replaceable>. The user who
41
defines an operator becomes its owner. If a schema name is given
42
then the operator is created in the specified schema. Otherwise it
43
is created in the current schema.
47
The operator name is a sequence of up to <symbol>NAMEDATALEN</>-1
48
(63 by default) characters from the following list:
50
+ - * / < > = ~ ! @ # % ^ & | ` ?
53
There are a few restrictions on your choice of name:
57
<literal>--</literal> and <literal>/*</literal> cannot appear anywhere in an operator name,
58
since they will be taken as the start of a comment.
63
A multicharacter operator name cannot end in <literal>+</literal> or
65
unless the name also contains at least one of these characters:
67
~ ! @ # % ^ & | ` ?
69
For example, <literal>@-</literal> is an allowed operator name,
70
but <literal>*-</literal> is not.
71
This restriction allows <productname>PostgreSQL</productname> to
72
parse SQL-compliant commands without requiring spaces between tokens.
79
The operator <literal>!=</literal> is mapped to
80
<literal><></literal> on input, so these two names are always
85
At least one of <literal>LEFTARG</> and <literal>RIGHTARG</> must be defined. For
86
binary operators, both must be defined. For right unary
87
operators, only <literal>LEFTARG</> should be defined, while for left
88
unary operators only <literal>RIGHTARG</> should be defined.
92
The <replaceable class="parameter">funcname</replaceable>
93
procedure must have been previously defined using <command>CREATE
94
FUNCTION</command> and must be defined to accept the correct number
95
of arguments (either one or two) of the indicated types.
99
The other clauses specify optional operator optimization clauses.
100
Their meaning is detailed in <xref linkend="xoper-optimization">.
105
<title>Parameters</title>
109
<term><replaceable class="parameter">name</replaceable></term>
112
The name of the operator to be defined. See above for allowable
113
characters. The name may be schema-qualified, for example
114
<literal>CREATE OPERATOR myschema.+ (...)</>. If not, then
115
the operator is created in the current schema. Two operators
116
in the same schema can have the same name if they operate on
117
different data types. This is called
118
<firstterm>overloading</>.
124
<term><replaceable class="parameter">funcname</replaceable></term>
127
The function used to implement this operator.
133
<term><replaceable class="parameter">lefttype</replaceable></term>
136
The data type of the operator's left operand, if any.
137
This option would be omitted for a left-unary operator.
143
<term><replaceable class="parameter">righttype</replaceable></term>
146
The data type of the operator's right operand, if any.
147
This option would be omitted for a right-unary operator.
153
<term><replaceable class="parameter">com_op</replaceable></term>
156
The commutator of this operator.
162
<term><replaceable class="parameter">neg_op</replaceable></term>
165
The negator of this operator.
171
<term><replaceable class="parameter">res_proc</replaceable></term>
174
The restriction selectivity estimator function for this operator.
180
<term><replaceable class="parameter">join_proc</replaceable></term>
183
The join selectivity estimator function for this operator.
189
<term><literal>HASHES</literal></term>
192
Indicates this operator can support a hash join.
198
<term><literal>MERGES</literal></term>
201
Indicates this operator can support a merge join.
207
<term><replaceable class="parameter">left_sort_op</replaceable></term>
210
If this operator can support a merge join, the less-than
211
operator that sorts the left-hand data type of this operator.
217
<term><replaceable class="parameter">right_sort_op</replaceable></term>
220
If this operator can support a merge join, the less-than
221
operator that sorts the right-hand data type of this operator.
227
<term><replaceable class="parameter">less_than_op</replaceable></term>
230
If this operator can support a merge join, the less-than
231
operator that compares the input data types of this operator.
237
<term><replaceable class="parameter">greater_than_op</replaceable></term>
240
If this operator can support a merge join, the greater-than
241
operator that compares the input data types of this operator.
248
To give a schema-qualified operator name in <replaceable
249
class="parameter">com_op</replaceable> or the other optional
250
arguments, use the <literal>OPERATOR()</> syntax, for example
252
COMMUTATOR = OPERATOR(myschema.===) ,
261
Refer to <xref linkend="xoper"> for further information.
265
Use <xref linkend="sql-dropoperator"
266
endterm="sql-dropoperator-title"> to delete user-defined operators
267
from a database. Use <xref linkend="sql-alteroperator"
268
endterm="sql-alteroperator-title"> to modify operators in a
274
<title>Examples</title>
277
The following command defines a new operator, area-equality, for
278
the data type <type>box</type>:
280
CREATE OPERATOR === (
283
PROCEDURE = area_equal_procedure,
286
RESTRICT = area_restriction_procedure,
287
JOIN = area_join_procedure,
289
SORT1 = <<<,
291
-- Since sort operators were given, MERGES is implied.
292
-- LTCMP and GTCMP are assumed to be < and > respectively
299
<title>Compatibility</title>
302
<command>CREATE OPERATOR</command> is a
303
<productname>PostgreSQL</productname> extension. There are no
304
provisions for user-defined operators in the SQL standard.
309
<title>See Also</title>
311
<simplelist type="inline">
312
<member><xref linkend="sql-alteroperator" endterm="sql-alteroperator-title"></member>
313
<member><xref linkend="sql-createopclass" endterm="sql-createopclass-title"></member>
314
<member><xref linkend="sql-dropoperator" endterm="sql-dropoperator-title"></member>
319
<!-- Keep this comment at the end of the file
324
sgml-minimize-attributes:nil
325
sgml-always-quote-attributes:t
328
sgml-parent-document:nil
329
sgml-default-dtd-file:"../reference.ced"
330
sgml-exposed-tags:nil
331
sgml-local-catalogs:"/usr/lib/sgml/catalog"
332
sgml-local-ecat-files:nil