diff options
Diffstat (limited to 'doc/src/sgml/html/sql-createoperator.html')
| -rw-r--r-- | doc/src/sgml/html/sql-createoperator.html | 141 |
1 files changed, 141 insertions, 0 deletions
diff --git a/doc/src/sgml/html/sql-createoperator.html b/doc/src/sgml/html/sql-createoperator.html new file mode 100644 index 0000000..3866f4b --- /dev/null +++ b/doc/src/sgml/html/sql-createoperator.html @@ -0,0 +1,141 @@ +<?xml version="1.0" encoding="UTF-8" standalone="no"?> +<!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 V1.79.1" /><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 xmlns="http://www.w3.org/TR/xhtml1/transitional" 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 13.4 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></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"> +CREATE OPERATOR <em class="replaceable"><code>name</code></em> ( + {FUNCTION|PROCEDURE} = <em class="replaceable"><code>function_name</code></em> + [, LEFTARG = <em class="replaceable"><code>left_type</code></em> ] [, RIGHTARG = <em class="replaceable"><code>right_type</code></em> ] + [, COMMUTATOR = <em class="replaceable"><code>com_op</code></em> ] [, NEGATOR = <em class="replaceable"><code>neg_op</code></em> ] + [, RESTRICT = <em class="replaceable"><code>res_proc</code></em> ] [, JOIN = <em class="replaceable"><code>join_proc</code></em> ] + [, HASHES ] [, MERGES ] +) +</pre></div><div class="refsect1" id="id-1.9.3.72.5"><h2>Description</h2><p> + <code class="command">CREATE OPERATOR</code> defines a new operator, + <em class="replaceable"><code>name</code></em>. 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. + </p><p> + The operator name is a sequence of up to <code class="symbol">NAMEDATALEN</code>-1 + (63 by default) characters from the following list: +</p><div class="literallayout"><p><br /> ++ - * / < > = ~ ! @ # % ^ & | ` ?<br /> +</p></div><p> + + There are a few restrictions on your choice of name: + </p><div class="itemizedlist"><ul class="itemizedlist" style="list-style-type: disc; "><li class="listitem"><p><code class="literal">--</code> and <code class="literal">/*</code> cannot appear anywhere in an operator name, + since they will be taken as the start of a comment. + </p></li><li class="listitem"><p> + A multicharacter operator name cannot end in <code class="literal">+</code> or + <code class="literal">-</code>, + unless the name also contains at least one of these characters: +</p><div class="literallayout"><p><br /> +~ ! @ # % ^ & | ` ?<br /> +</p></div><p> + For example, <code class="literal">@-</code> is an allowed operator name, + but <code class="literal">*-</code> is not. + This restriction allows <span class="productname">PostgreSQL</span> to + parse SQL-compliant commands without requiring spaces between tokens. + </p></li><li class="listitem"><p> + The use of <code class="literal">=></code> as an operator name is deprecated. It may + be disallowed altogether in a future release. + </p></li></ul></div><p> + </p><p> + The operator <code class="literal">!=</code> is mapped to + <code class="literal"><></code> on input, so these two names are always + equivalent. + </p><p> + At least one of <code class="literal">LEFTARG</code> and <code class="literal">RIGHTARG</code> must be defined. For + binary operators, both must be defined. For right unary + operators, only <code class="literal">LEFTARG</code> should be defined, while for left + unary operators only <code class="literal">RIGHTARG</code> should be defined. + </p><div class="note"><h3 class="title">Note</h3><p> + Right unary, also called postfix, operators are deprecated and will be + removed in <span class="productname">PostgreSQL</span> version 14. + </p></div><p> + The <em class="replaceable"><code>function_name</code></em> + function must have been previously defined using <code class="command">CREATE + FUNCTION</code> and must be defined to accept the correct number + of arguments (either one or two) of the indicated types. + </p><p> + In the syntax of <code class="literal">CREATE OPERATOR</code>, the keywords + <code class="literal">FUNCTION</code> and <code class="literal">PROCEDURE</code> are + equivalent, but the referenced function must in any case be a function, not + a procedure. The use of the keyword <code class="literal">PROCEDURE</code> here is + historical and deprecated. + </p><p> + The other clauses specify optional operator optimization clauses. + Their meaning is detailed in <a class="xref" href="xoper-optimization.html" title="37.15. Operator Optimization Information">Section 37.15</a>. + </p><p> + To be able to create an operator, you must have <code class="literal">USAGE</code> + privilege on the argument types and the return type, as well + as <code class="literal">EXECUTE</code> privilege on the underlying function. If a + commutator or negator operator is specified, you must own these operators. + </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> + The name of the operator to be defined. See above for allowable + characters. The name can be schema-qualified, for example + <code class="literal">CREATE OPERATOR myschema.+ (...)</code>. 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 + <em class="firstterm">overloading</em>. + </p></dd><dt><span class="term"><em class="replaceable"><code>function_name</code></em></span></dt><dd><p> + The function used to implement this operator. + </p></dd><dt><span class="term"><em class="replaceable"><code>left_type</code></em></span></dt><dd><p> + The data type of the operator's left operand, if any. + This option would be omitted for a left-unary operator. + </p></dd><dt><span class="term"><em class="replaceable"><code>right_type</code></em></span></dt><dd><p> + The data type of the operator's right operand, if any. + This option would be omitted for a right-unary operator. + </p></dd><dt><span class="term"><em class="replaceable"><code>com_op</code></em></span></dt><dd><p> + The commutator of this operator. + </p></dd><dt><span class="term"><em class="replaceable"><code>neg_op</code></em></span></dt><dd><p> + The negator of this operator. + </p></dd><dt><span class="term"><em class="replaceable"><code>res_proc</code></em></span></dt><dd><p> + The restriction selectivity estimator function for this operator. + </p></dd><dt><span class="term"><em class="replaceable"><code>join_proc</code></em></span></dt><dd><p> + The join selectivity estimator function for this operator. + </p></dd><dt><span class="term"><code class="literal">HASHES</code></span></dt><dd><p> + Indicates this operator can support a hash join. + </p></dd><dt><span class="term"><code class="literal">MERGES</code></span></dt><dd><p> + Indicates this operator can support a merge join. + </p></dd></dl></div><p> + To give a schema-qualified operator name in <em class="replaceable"><code>com_op</code></em> or the other optional + arguments, use the <code class="literal">OPERATOR()</code> syntax, for example: +</p><pre class="programlisting"> +COMMUTATOR = OPERATOR(myschema.===) , +</pre></div><div class="refsect1" id="id-1.9.3.72.7"><h2>Notes</h2><p> + Refer to <a class="xref" href="xoper.html" title="37.14. User-Defined Operators">Section 37.14</a> for further information. + </p><p> + It is not possible to specify an operator's lexical precedence in + <code class="command">CREATE OPERATOR</code>, because the parser's precedence behavior + 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. + </p><p> + The obsolete options <code class="literal">SORT1</code>, <code class="literal">SORT2</code>, + <code class="literal">LTCMP</code>, and <code class="literal">GTCMP</code> were formerly used to + specify the names of sort operators associated with a merge-joinable + operator. This is no longer necessary, since information about + associated operators is found by looking at B-tree operator families + instead. If one of these options is given, it is ignored except + for implicitly setting <code class="literal">MERGES</code> true. + </p><p> + Use <a class="xref" href="sql-dropoperator.html" title="DROP OPERATOR"><span class="refentrytitle">DROP OPERATOR</span></a> to delete user-defined operators + from a database. Use <a class="xref" href="sql-alteroperator.html" title="ALTER OPERATOR"><span class="refentrytitle">ALTER OPERATOR</span></a> to modify operators in a + database. + </p></div><div class="refsect1" id="id-1.9.3.72.8"><h2>Examples</h2><p> + The following command defines a new operator, area-equality, for + the data type <code class="type">box</code>: +</p><pre class="programlisting"> +CREATE OPERATOR === ( + LEFTARG = box, + RIGHTARG = box, + FUNCTION = area_equal_function, + COMMUTATOR = ===, + NEGATOR = !==, + RESTRICT = area_restriction_function, + JOIN = area_join_function, + HASHES, MERGES +); +</pre></div><div class="refsect1" id="id-1.9.3.72.9"><h2>Compatibility</h2><p> + <code class="command">CREATE OPERATOR</code> is a + <span class="productname">PostgreSQL</span> extension. There are no + provisions for user-defined operators in the SQL standard. + </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 xmlns="http://www.w3.org/TR/xhtml1/transitional" class="navfooter"><hr></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 13.4 Documentation">Home</a></td><td width="40%" align="right" valign="top"> CREATE OPERATOR CLASS</td></tr></table></div></body></html>
\ No newline at end of file |