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