codekingpro/portable-devtools
115k
1<?xml version="1.0" encoding="UTF-8" standalone="no"?>2<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"><html xmlns="http://www.w3.org/1999/xhtml"><head><meta http-equiv="Content-Type" content="text/html; charset=UTF-8" /><title>CREATE OPERATOR</title><link rel="stylesheet" type="text/css" href="stylesheet.css" /><link rev="made" href="pgsql-docs@lists.postgresql.org" /><meta name="generator" content="DocBook XSL Stylesheets Vsnapshot" /><link rel="prev" href="sql-creatematerializedview.html" title="CREATE MATERIALIZED VIEW" /><link rel="next" href="sql-createopclass.html" title="CREATE OPERATOR CLASS" /></head><body id="docContent" class="container-fluid col-10"><div class="navheader"><table width="100%" summary="Navigation header"><tr><th colspan="5" align="center">CREATE OPERATOR</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-creatematerializedview.html" title="CREATE MATERIALIZED VIEW">Prev</a> </td><td width="10%" align="left"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><th width="60%" align="center">SQL Commands</th><td width="10%" align="right"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-createopclass.html" title="CREATE OPERATOR CLASS">Next</a></td></tr></table><hr /></div><div class="refentry" id="SQL-CREATEOPERATOR"><div class="titlepage"></div><a id="id-1.9.3.72.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">CREATE OPERATOR</span></h2><p>CREATE OPERATOR — define a new operator</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis">3CREATE OPERATOR <em class="replaceable"><code>name</code></em> (4 {FUNCTION|PROCEDURE} = <em class="replaceable"><code>function_name</code></em>5 [, LEFTARG = <em class="replaceable"><code>left_type</code></em> ] [, RIGHTARG = <em class="replaceable"><code>right_type</code></em> ]6 [, COMMUTATOR = <em class="replaceable"><code>com_op</code></em> ] [, NEGATOR = <em class="replaceable"><code>neg_op</code></em> ]7 [, RESTRICT = <em class="replaceable"><code>res_proc</code></em> ] [, JOIN = <em class="replaceable"><code>join_proc</code></em> ]8 [, HASHES ] [, MERGES ]9)10</pre></div><div class="refsect1" id="id-1.9.3.72.5"><h2>Description</h2><p>11 <code class="command">CREATE OPERATOR</code> defines a new operator,12 <em class="replaceable"><code>name</code></em>. The user who13 defines an operator becomes its owner. If a schema name is given14 then the operator is created in the specified schema. Otherwise it15 is created in the current schema.16 </p><p>17 The operator name is a sequence of up to <code class="symbol">NAMEDATALEN</code>-118 (63 by default) characters from the following list:19</p><div class="literallayout"><p><br />20+ - * / < > = ~ ! @ # % ^ & | ` ?<br />21</p></div><p>22 23 There are a few restrictions on your choice of name:24 </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p>25 <code class="literal">--</code> and <code class="literal">/*</code> cannot appear anywhere in an operator name,26 since they will be taken as the start of a comment.27 </p></li><li class="listitem"><p>28 A multicharacter operator name cannot end in <code class="literal">+</code> or29 <code class="literal">-</code>,30 unless the name also contains at least one of these characters:31</p><div class="literallayout"><p><br />32~ ! @ # % ^ & | ` ?<br />33</p></div><p>34 For example, <code class="literal">@-</code> is an allowed operator name,35 but <code class="literal">*-</code> is not.36 This restriction allows <span class="productname">PostgreSQL</span> to37 parse SQL-compliant commands without requiring spaces between tokens.38 </p></li><li class="listitem"><p>39 The symbol <code class="literal">=></code> is reserved by the SQL grammar,40 so it cannot be used as an operator name.41 </p></li></ul></div><p>42 </p><p>43 The operator <code class="literal">!=</code> is mapped to44 <code class="literal"><></code> on input, so these two names are always45 equivalent.46 </p><p>47 For binary operators, both <code class="literal">LEFTARG</code> and48 <code class="literal">RIGHTARG</code> must be defined. For prefix operators only49 <code class="literal">RIGHTARG</code> should be defined.50 The <em class="replaceable"><code>function_name</code></em>51 function must have been previously defined using <code class="command">CREATE52 FUNCTION</code> and must be defined to accept the correct number53 of arguments (either one or two) of the indicated types.54 </p><p>55 In the syntax of <code class="literal">CREATE OPERATOR</code>, the keywords56 <code class="literal">FUNCTION</code> and <code class="literal">PROCEDURE</code> are57 equivalent, but the referenced function must in any case be a function, not58 a procedure. The use of the keyword <code class="literal">PROCEDURE</code> here is59 historical and deprecated.60 </p><p>61 The other clauses specify optional operator optimization clauses.62 Their meaning is detailed in <a class="xref" href="xoper-optimization.html" title="38.15. Operator Optimization Information">Section 38.15</a>.63 </p><p>64 To be able to create an operator, you must have <code class="literal">USAGE</code>65 privilege on the argument types and the return type, as well66 as <code class="literal">EXECUTE</code> privilege on the underlying function. If a67 commutator or negator operator is specified, you must own these operators.68 </p></div><div class="refsect1" id="id-1.9.3.72.6"><h2>Parameters</h2><div class="variablelist"><dl class="variablelist"><dt><span class="term"><em class="replaceable"><code>name</code></em></span></dt><dd><p>69 The name of the operator to be defined. See above for allowable70 characters. The name can be schema-qualified, for example71 <code class="literal">CREATE OPERATOR myschema.+ (...)</code>. If not, then72 the operator is created in the current schema. Two operators73 in the same schema can have the same name if they operate on74 different data types. This is called75 <em class="firstterm">overloading</em>.76 </p></dd><dt><span class="term"><em class="replaceable"><code>function_name</code></em></span></dt><dd><p>77 The function used to implement this operator.78 </p></dd><dt><span class="term"><em class="replaceable"><code>left_type</code></em></span></dt><dd><p>79 The data type of the operator's left operand, if any.80 This option would be omitted for a prefix operator.81 </p></dd><dt><span class="term"><em class="replaceable"><code>right_type</code></em></span></dt><dd><p>82 The data type of the operator's right operand.83 </p></dd><dt><span class="term"><em class="replaceable"><code>com_op</code></em></span></dt><dd><p>84 The commutator of this operator.85 </p></dd><dt><span class="term"><em class="replaceable"><code>neg_op</code></em></span></dt><dd><p>86 The negator of this operator.87 </p></dd><dt><span class="term"><em class="replaceable"><code>res_proc</code></em></span></dt><dd><p>88 The restriction selectivity estimator function for this operator.89 </p></dd><dt><span class="term"><em class="replaceable"><code>join_proc</code></em></span></dt><dd><p>90 The join selectivity estimator function for this operator.91 </p></dd><dt><span class="term"><code class="literal">HASHES</code></span></dt><dd><p>92 Indicates this operator can support a hash join.93 </p></dd><dt><span class="term"><code class="literal">MERGES</code></span></dt><dd><p>94 Indicates this operator can support a merge join.95 </p></dd></dl></div><p>96 To give a schema-qualified operator name in <em class="replaceable"><code>com_op</code></em> or the other optional97 arguments, use the <code class="literal">OPERATOR()</code> syntax, for example:98</p><pre class="programlisting">99COMMUTATOR = OPERATOR(myschema.===) ,100</pre></div><div class="refsect1" id="id-1.9.3.72.7"><h2>Notes</h2><p>101 Refer to <a class="xref" href="xoper.html" title="38.14. User-Defined Operators">Section 38.14</a> for further information.102 </p><p>103 It is not possible to specify an operator's lexical precedence in104 <code class="command">CREATE OPERATOR</code>, because the parser's precedence behavior105 is hard-wired. See <a class="xref" href="sql-syntax-lexical.html#SQL-PRECEDENCE" title="4.1.6. Operator Precedence">Section 4.1.6</a> for precedence details.106 </p><p>107 The obsolete options <code class="literal">SORT1</code>, <code class="literal">SORT2</code>,108 <code class="literal">LTCMP</code>, and <code class="literal">GTCMP</code> were formerly used to109 specify the names of sort operators associated with a merge-joinable110 operator. This is no longer necessary, since information about111 associated operators is found by looking at B-tree operator families112 instead. If one of these options is given, it is ignored except113 for implicitly setting <code class="literal">MERGES</code> true.114 </p><p>115 Use <a class="link" href="sql-dropoperator.html" title="DROP OPERATOR"><code class="command">DROP OPERATOR</code></a> to delete user-defined operators116 from a database. Use <a class="link" href="sql-alteroperator.html" title="ALTER OPERATOR"><code class="command">ALTER OPERATOR</code></a> to modify operators in a117 database.118 </p></div><div class="refsect1" id="id-1.9.3.72.8"><h2>Examples</h2><p>119 The following command defines a new operator, area-equality, for120 the data type <code class="type">box</code>:121</p><pre class="programlisting">122CREATE OPERATOR === (123 LEFTARG = box,124 RIGHTARG = box,125 FUNCTION = area_equal_function,126 COMMUTATOR = ===,127 NEGATOR = !==,128 RESTRICT = area_restriction_function,129 JOIN = area_join_function,130 HASHES, MERGES131);132</pre></div><div class="refsect1" id="id-1.9.3.72.9"><h2>Compatibility</h2><p>133 <code class="command">CREATE OPERATOR</code> is a134 <span class="productname">PostgreSQL</span> extension. There are no135 provisions for user-defined operators in the SQL standard.136 </p></div><div class="refsect1" id="id-1.9.3.72.10"><h2>See Also</h2><span class="simplelist"><a class="xref" href="sql-alteroperator.html" title="ALTER OPERATOR"><span class="refentrytitle">ALTER OPERATOR</span></a>, <a class="xref" href="sql-createopclass.html" title="CREATE OPERATOR CLASS"><span class="refentrytitle">CREATE OPERATOR CLASS</span></a>, <a class="xref" href="sql-dropoperator.html" title="DROP OPERATOR"><span class="refentrytitle">DROP OPERATOR</span></a></span></div></div><div class="navfooter"><hr /><table width="100%" summary="Navigation footer"><tr><td width="40%" align="left"><a accesskey="p" href="sql-creatematerializedview.html" title="CREATE MATERIALIZED VIEW">Prev</a> </td><td width="20%" align="center"><a accesskey="u" href="sql-commands.html" title="SQL Commands">Up</a></td><td width="40%" align="right"> <a accesskey="n" href="sql-createopclass.html" title="CREATE OPERATOR CLASS">Next</a></td></tr><tr><td width="40%" align="left" valign="top">CREATE MATERIALIZED VIEW </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 16.3 Documentation">Home</a></td><td width="40%" align="right" valign="top"> CREATE OPERATOR CLASS</td></tr></table></div></body></html>