1<!-- 2doc/src/sgml/ref/create_operator.sgml 3PostgreSQL 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> 24CREATE OPERATOR <replaceable>name</replaceable> ( 25 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</>-1 47 (63 by default) characters from the following list: 48<literallayout> 49+ - * / < > = ~ ! @ # % ^ & | ` ? 50</literallayout> 51 52 There are a few restrictions on your choice of name: 53 <itemizedlist> 54 <listitem> 55 <para><literal>--</literal> and <literal>/*</literal> cannot appear anywhere in an operator name, 56 since they will be taken as the start of a comment. 57 </para> 58 </listitem> 59 <listitem> 60 <para> 61 A multicharacter operator name cannot end in <literal>+</literal> or 62 <literal>-</literal>, 63 unless the name also contains at least one of these characters: 64<literallayout> 65~ ! @ # % ^ & | ` ? 66</literallayout> 67 For example, <literal>@-</literal> is an allowed operator name, 68 but <literal>*-</literal> is not. 69 This restriction allows <productname>PostgreSQL</productname> to 70 parse SQL-compliant commands without requiring spaces between tokens. 71 </para> 72 </listitem> 73 <listitem> 74 <para> 75 The use of <literal>=></> as an operator name is deprecated. It may 76 be disallowed altogether in a future release. 77 </para> 78 </listitem> 79 </itemizedlist> 80 </para> 81 82 <para> 83 The operator <literal>!=</literal> is mapped to 84 <literal><></literal> on input, so these two names are always 85 equivalent. 86 </para> 87 88 <para> 89 At least one of <literal>LEFTARG</> and <literal>RIGHTARG</> must be defined. For 90 binary operators, both must be defined. For right unary 91 operators, only <literal>LEFTARG</> should be defined, while for left 92 unary operators only <literal>RIGHTARG</> should be defined. 93 </para> 94 95 <note> 96 <para> 97 Right unary, also called postfix, operators are deprecated and will be 98 removed in <productname>PostgreSQL</productname> version 14. 99 </para> 100 </note> 101 102 <para> 103 The <replaceable class="parameter">function_name</replaceable> 104 procedure must have been previously defined using <command>CREATE 105 FUNCTION</command> and must be defined to accept the correct number 106 of arguments (either one or two) of the indicated types. 107 </para> 108 109 <para> 110 The other clauses specify optional operator optimization clauses. 111 Their meaning is detailed in <xref linkend="xoper-optimization">. 112 </para> 113 114 <para> 115 To be able to create an operator, you must have <literal>USAGE</literal> 116 privilege on the argument types and the return type, as well 117 as <literal>EXECUTE</literal> privilege on the underlying function. If a 118 commutator or negator operator is specified, you must own these operators. 119 </para> 120 </refsect1> 121 122 <refsect1> 123 <title>Parameters</title> 124 125 <variablelist> 126 <varlistentry> 127 <term><replaceable class="parameter">name</replaceable></term> 128 <listitem> 129 <para> 130 The name of the operator to be defined. See above for allowable 131 characters. The name can be schema-qualified, for example 132 <literal>CREATE OPERATOR myschema.+ (...)</>. If not, then 133 the operator is created in the current schema. Two operators 134 in the same schema can have the same name if they operate on 135 different data types. This is called 136 <firstterm>overloading</>. 137 </para> 138 </listitem> 139 </varlistentry> 140 141 <varlistentry> 142 <term><replaceable class="parameter">function_name</replaceable></term> 143 <listitem> 144 <para> 145 The function used to implement this operator. 146 </para> 147 </listitem> 148 </varlistentry> 149 150 <varlistentry> 151 <term><replaceable class="parameter">left_type</replaceable></term> 152 <listitem> 153 <para> 154 The data type of the operator's left operand, if any. 155 This option would be omitted for a left-unary operator. 156 </para> 157 </listitem> 158 </varlistentry> 159 160 <varlistentry> 161 <term><replaceable class="parameter">right_type</replaceable></term> 162 <listitem> 163 <para> 164 The data type of the operator's right operand, if any. 165 This option would be omitted for a right-unary operator. 166 </para> 167 </listitem> 168 </varlistentry> 169 170 <varlistentry> 171 <term><replaceable class="parameter">com_op</replaceable></term> 172 <listitem> 173 <para> 174 The commutator of this operator. 175 </para> 176 </listitem> 177 </varlistentry> 178 179 <varlistentry> 180 <term><replaceable class="parameter">neg_op</replaceable></term> 181 <listitem> 182 <para> 183 The negator of this operator. 184 </para> 185 </listitem> 186 </varlistentry> 187 188 <varlistentry> 189 <term><replaceable class="parameter">res_proc</replaceable></term> 190 <listitem> 191 <para> 192 The restriction selectivity estimator function for this operator. 193 </para> 194 </listitem> 195 </varlistentry> 196 197 <varlistentry> 198 <term><replaceable class="parameter">join_proc</replaceable></term> 199 <listitem> 200 <para> 201 The join selectivity estimator function for this operator. 202 </para> 203 </listitem> 204 </varlistentry> 205 206 <varlistentry> 207 <term><literal>HASHES</literal></term> 208 <listitem> 209 <para> 210 Indicates this operator can support a hash join. 211 </para> 212 </listitem> 213 </varlistentry> 214 215 <varlistentry> 216 <term><literal>MERGES</literal></term> 217 <listitem> 218 <para> 219 Indicates this operator can support a merge join. 220 </para> 221 </listitem> 222 </varlistentry> 223 </variablelist> 224 225 <para> 226 To give a schema-qualified operator name in <replaceable 227 class="parameter">com_op</replaceable> or the other optional 228 arguments, use the <literal>OPERATOR()</> syntax, for example: 229<programlisting> 230COMMUTATOR = OPERATOR(myschema.===) , 231</programlisting></para> 232 </refsect1> 233 234 <refsect1> 235 <title>Notes</title> 236 237 <para> 238 Refer to <xref linkend="xoper"> for further information. 239 </para> 240 241 <para> 242 It is not possible to specify an operator's lexical precedence in 243 <command>CREATE OPERATOR</>, because the parser's precedence behavior 244 is hard-wired. See <xref linkend="sql-precedence"> for precedence details. 245 </para> 246 247 <para> 248 The obsolete options <literal>SORT1</>, <literal>SORT2</>, 249 <literal>LTCMP</>, and <literal>GTCMP</> were formerly used to 250 specify the names of sort operators associated with a merge-joinable 251 operator. This is no longer necessary, since information about 252 associated operators is found by looking at B-tree operator families 253 instead. If one of these options is given, it is ignored except 254 for implicitly setting <literal>MERGES</> true. 255 </para> 256 257 <para> 258 Use <xref linkend="sql-dropoperator"> to delete user-defined operators 259 from a database. Use <xref linkend="sql-alteroperator"> to modify operators in a 260 database. 261 </para> 262 </refsect1> 263 264 <refsect1> 265 <title>Examples</title> 266 267 <para> 268 The following command defines a new operator, area-equality, for 269 the data type <type>box</type>: 270<programlisting> 271CREATE OPERATOR === ( 272 LEFTARG = box, 273 RIGHTARG = box, 274 PROCEDURE = area_equal_procedure, 275 COMMUTATOR = ===, 276 NEGATOR = !==, 277 RESTRICT = area_restriction_procedure, 278 JOIN = area_join_procedure, 279 HASHES, MERGES 280); 281</programlisting></para> 282 </refsect1> 283 284 <refsect1> 285 <title>Compatibility</title> 286 287 <para> 288 <command>CREATE OPERATOR</command> is a 289 <productname>PostgreSQL</productname> extension. There are no 290 provisions for user-defined operators in the SQL standard. 291 </para> 292 </refsect1> 293 294 <refsect1> 295 <title>See Also</title> 296 297 <simplelist type="inline"> 298 <member><xref linkend="sql-alteroperator"></member> 299 <member><xref linkend="sql-createopclass"></member> 300 <member><xref linkend="sql-dropoperator"></member> 301 </simplelist> 302 </refsect1> 303</refentry> 304