begriffs open source - ai-pg/blob - full-docs/html/sql-alteroperator.html

   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>ALTER 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-altermaterializedview.html" title="ALTER MATERIALIZED VIEW" /><link rel="next" href="sql-alteropclass.html" title="ALTER 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">ALTER OPERATOR</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-altermaterializedview.html" title="ALTER 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 18.0 Documentation">Home</a></td><td width="10%" align="right"> <a accesskey="n" href="sql-alteropclass.html" title="ALTER OPERATOR CLASS">Next</a></td></tr></table><hr /></div><div class="refentry" id="SQL-ALTEROPERATOR"><div class="titlepage"></div><a id="id-1.9.3.20.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">ALTER OPERATOR</span></h2><p>ALTER OPERATOR — change the definition of an operator</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis">
   3 ALTER OPERATOR <em class="replaceable"><code>name</code></em> ( { <em class="replaceable"><code>left_type</code></em> | NONE } , <em class="replaceable"><code>right_type</code></em> )
   4     OWNER TO { <em class="replaceable"><code>new_owner</code></em> | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
   5
   6 ALTER OPERATOR <em class="replaceable"><code>name</code></em> ( { <em class="replaceable"><code>left_type</code></em> | NONE } , <em class="replaceable"><code>right_type</code></em> )
   7     SET SCHEMA <em class="replaceable"><code>new_schema</code></em>
   8
   9 ALTER OPERATOR <em class="replaceable"><code>name</code></em> ( { <em class="replaceable"><code>left_type</code></em> | NONE } , <em class="replaceable"><code>right_type</code></em> )
  10     SET ( {  RESTRICT = { <em class="replaceable"><code>res_proc</code></em> | NONE }
  11            | JOIN = { <em class="replaceable"><code>join_proc</code></em> | NONE }
  12            | COMMUTATOR = <em class="replaceable"><code>com_op</code></em>
  13            | NEGATOR = <em class="replaceable"><code>neg_op</code></em>
  14            | HASHES
  15            | MERGES
  16           } [, ... ] )
  17 </pre></div><div class="refsect1" id="id-1.9.3.20.5"><h2>Description</h2><p>
  18    <code class="command">ALTER OPERATOR</code> changes the definition of
  19    an operator.
  20   </p><p>
  21    You must own the operator to use <code class="command">ALTER OPERATOR</code>.
  22    To alter the owner, you must be able to <code class="literal">SET ROLE</code> to the
  23    new owning role, and that role must have <code class="literal">CREATE</code>
  24    privilege on the operator's schema.
  25    (These restrictions enforce that altering the owner
  26    doesn't do anything you couldn't do by dropping and recreating the operator.
  27    However, a superuser can alter ownership of any operator anyway.)
  28   </p></div><div class="refsect1" id="id-1.9.3.20.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>
  29       The name (optionally schema-qualified) of an existing operator.
  30      </p></dd><dt><span class="term"><em class="replaceable"><code>left_type</code></em></span></dt><dd><p>
  31       The data type of the operator's left operand; write
  32       <code class="literal">NONE</code> if the operator has no left operand.
  33      </p></dd><dt><span class="term"><em class="replaceable"><code>right_type</code></em></span></dt><dd><p>
  34       The data type of the operator's right operand.
  35      </p></dd><dt><span class="term"><em class="replaceable"><code>new_owner</code></em></span></dt><dd><p>
  36       The new owner of the operator.
  37      </p></dd><dt><span class="term"><em class="replaceable"><code>new_schema</code></em></span></dt><dd><p>
  38       The new schema for the operator.
  39      </p></dd><dt><span class="term"><em class="replaceable"><code>res_proc</code></em></span></dt><dd><p>
  40          The restriction selectivity estimator function for this operator; write NONE to remove existing selectivity estimator.
  41        </p></dd><dt><span class="term"><em class="replaceable"><code>join_proc</code></em></span></dt><dd><p>
  42          The join selectivity estimator function for this operator; write NONE to remove existing selectivity estimator.
  43        </p></dd><dt><span class="term"><em class="replaceable"><code>com_op</code></em></span></dt><dd><p>
  44       The commutator of this operator. Can only be changed if the operator
  45       does not have an existing commutator.
  46      </p></dd><dt><span class="term"><em class="replaceable"><code>neg_op</code></em></span></dt><dd><p>
  47       The negator of this operator. Can only be changed if the operator does
  48       not have an existing negator.
  49      </p></dd><dt><span class="term"><code class="literal">HASHES</code></span></dt><dd><p>
  50       Indicates this operator can support a hash join. Can only be enabled and
  51       not disabled.
  52      </p></dd><dt><span class="term"><code class="literal">MERGES</code></span></dt><dd><p>
  53       Indicates this operator can support a merge join. Can only be enabled
  54       and not disabled.
  55      </p></dd></dl></div></div><div class="refsect1" id="id-1.9.3.20.7"><h2>Notes</h2><p>
  56    Refer to <a class="xref" href="xoper.html" title="36.14. User-Defined Operators">Section 36.14</a> and
  57    <a class="xref" href="xoper-optimization.html" title="36.15. Operator Optimization Information">Section 36.15</a> for further information.
  58   </p><p>
  59    Since commutators come in pairs that are commutators of each other,
  60    <code class="literal">ALTER OPERATOR SET COMMUTATOR</code> will also set the
  61    commutator of the <em class="replaceable"><code>com_op</code></em>
  62    to be the target operator.  Likewise, <code class="literal">ALTER OPERATOR SET
  63    NEGATOR</code> will also set the negator of
  64    the <em class="replaceable"><code>neg_op</code></em> to be the
  65    target operator.  Therefore, you must own the commutator or negator
  66    operator as well as the target operator.
  67   </p></div><div class="refsect1" id="id-1.9.3.20.8"><h2>Examples</h2><p>
  68    Change the owner of a custom operator <code class="literal">a @@ b</code> for type <code class="type">text</code>:
  69 </p><pre class="programlisting">
  70 ALTER OPERATOR @@ (text, text) OWNER TO joe;
  71 </pre><p>
  72   </p><p>
  73    Change the restriction and join selectivity estimator functions of a
  74    custom operator <code class="literal">a &amp;&amp; b</code> for
  75    type <code class="type">int[]</code>:
  76 </p><pre class="programlisting">
  77 ALTER OPERATOR &amp;&amp; (int[], int[]) SET (RESTRICT = _int_contsel, JOIN = _int_contjoinsel);
  78 </pre><p>
  79   </p><p>
  80    Mark the <code class="literal">&amp;&amp;</code> operator as being its own
  81    commutator:
  82 </p><pre class="programlisting">
  83 ALTER OPERATOR &amp;&amp; (int[], int[]) SET (COMMUTATOR = &amp;&amp;);
  84 </pre><p>
  85   </p></div><div class="refsect1" id="id-1.9.3.20.9"><h2>Compatibility</h2><p>
  86    There is no <code class="command">ALTER OPERATOR</code> statement in
  87    the SQL standard.
  88   </p></div><div class="refsect1" id="id-1.9.3.20.10"><h2>See Also</h2><span class="simplelist"><a class="xref" href="sql-createoperator.html" title="CREATE OPERATOR"><span class="refentrytitle">CREATE OPERATOR</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-altermaterializedview.html" title="ALTER 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-alteropclass.html" title="ALTER OPERATOR CLASS">Next</a></td></tr><tr><td width="40%" align="left" valign="top">ALTER MATERIALIZED VIEW </td><td width="20%" align="center"><a accesskey="h" href="index.html" title="PostgreSQL 18.0 Documentation">Home</a></td><td width="40%" align="right" valign="top"> ALTER OPERATOR CLASS</td></tr></table></div></body></html>