Debian Codegraph @

doc/src/sgml/ref/alter_operator.sgml

raw
1 <!--
2 doc/src/sgml/ref/alter_operator.sgml
3 PostgreSQL documentation
4 -->
5
6 <refentry id="sql-alteroperator">
7 <indexterm zone="sql-alteroperator">
8 <primary>ALTER OPERATOR</primary>
9 </indexterm>
10
11 <refmeta>
12 <refentrytitle>ALTER OPERATOR</refentrytitle>
13 <manvolnum>7</manvolnum>
14 <refmiscinfo>SQL - Language Statements</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18 <refname>ALTER OPERATOR</refname>
19 <refpurpose>change the definition of an operator</refpurpose>
20 </refnamediv>
21
22 <refsynopsisdiv>
23 <synopsis>
24 ALTER OPERATOR <replaceable>name</replaceable> ( { <replaceable>left_type</replaceable> | NONE } , <replaceable>right_type</replaceable> )
25 OWNER TO { <replaceable>new_owner</replaceable> | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
26
27 ALTER OPERATOR <replaceable>name</replaceable> ( { <replaceable>left_type</replaceable> | NONE } , <replaceable>right_type</replaceable> )
28 SET SCHEMA <replaceable>new_schema</replaceable>
29
30 ALTER OPERATOR <replaceable>name</replaceable> ( { <replaceable>left_type</replaceable> | NONE } , <replaceable>right_type</replaceable> )
31 SET ( { RESTRICT = { <replaceable class="parameter">res_proc</replaceable> | NONE }
32 | JOIN = { <replaceable class="parameter">join_proc</replaceable> | NONE }
33 | COMMUTATOR = <replaceable class="parameter">com_op</replaceable>
34 | NEGATOR = <replaceable class="parameter">neg_op</replaceable>
35 | HASHES
36 | MERGES
37 } [, ... ] )
38 </synopsis>
39 </refsynopsisdiv>
40
41 <refsect1>
42 <title>Description</title>
43
44 <para>
45 <command>ALTER OPERATOR</command> changes the definition of
46 an operator.
47 </para>
48
49 <para>
50 You must own the operator to use <command>ALTER OPERATOR</command>.
51 To alter the owner, you must be able to <literal>SET ROLE</literal> to the
52 new owning role, and that role must have <literal>CREATE</literal>
53 privilege on the operator's schema.
54 (These restrictions enforce that altering the owner
55 doesn't do anything you couldn't do by dropping and recreating the operator.
56 However, a superuser can alter ownership of any operator anyway.)
57 </para>
58 </refsect1>
59
60 <refsect1>
61 <title>Parameters</title>
62
63 <variablelist>
64 <varlistentry>
65 <term><replaceable class="parameter">name</replaceable></term>
66 <listitem>
67 <para>
68 The name (optionally schema-qualified) of an existing operator.
69 </para>
70 </listitem>
71 </varlistentry>
72
73 <varlistentry>
74 <term><replaceable class="parameter">left_type</replaceable></term>
75 <listitem>
76 <para>
77 The data type of the operator's left operand; write
78 <literal>NONE</literal> if the operator has no left operand.
79 </para>
80 </listitem>
81 </varlistentry>
82
83 <varlistentry>
84 <term><replaceable class="parameter">right_type</replaceable></term>
85 <listitem>
86 <para>
87 The data type of the operator's right operand.
88 </para>
89 </listitem>
90 </varlistentry>
91
92 <varlistentry>
93 <term><replaceable class="parameter">new_owner</replaceable></term>
94 <listitem>
95 <para>
96 The new owner of the operator.
97 </para>
98 </listitem>
99 </varlistentry>
100
101 <varlistentry>
102 <term><replaceable class="parameter">new_schema</replaceable></term>
103 <listitem>
104 <para>
105 The new schema for the operator.
106 </para>
107 </listitem>
108 </varlistentry>
109
110 <varlistentry>
111 <term><replaceable class="parameter">res_proc</replaceable></term>
112 <listitem>
113 <para>
114 The restriction selectivity estimator function for this operator; write NONE to remove existing selectivity estimator.
115 </para>
116 </listitem>
117 </varlistentry>
118
119 <varlistentry>
120 <term><replaceable class="parameter">join_proc</replaceable></term>
121 <listitem>
122 <para>
123 The join selectivity estimator function for this operator; write NONE to remove existing selectivity estimator.
124 </para>
125 </listitem>
126 </varlistentry>
127
128 <varlistentry>
129 <term><replaceable class="parameter">com_op</replaceable></term>
130 <listitem>
131 <para>
132 The commutator of this operator. Can only be changed if the operator
133 does not have an existing commutator.
134 </para>
135 </listitem>
136 </varlistentry>
137
138 <varlistentry>
139 <term><replaceable class="parameter">neg_op</replaceable></term>
140 <listitem>
141 <para>
142 The negator of this operator. Can only be changed if the operator does
143 not have an existing negator.
144 </para>
145 </listitem>
146 </varlistentry>
147
148 <varlistentry>
149 <term><literal>HASHES</literal></term>
150 <listitem>
151 <para>
152 Indicates this operator can support a hash join. Can only be enabled and
153 not disabled.
154 </para>
155 </listitem>
156 </varlistentry>
157
158 <varlistentry>
159 <term><literal>MERGES</literal></term>
160 <listitem>
161 <para>
162 Indicates this operator can support a merge join. Can only be enabled
163 and not disabled.
164 </para>
165 </listitem>
166 </varlistentry>
167
168 </variablelist>
169 </refsect1>
170
171 <refsect1>
172 <title>Notes</title>
173
174 <para>
175 Refer to <xref linkend="xoper"/> and
176 <xref linkend="xoper-optimization"/> for further information.
177 </para>
178
179 <para>
180 Since commutators come in pairs that are commutators of each other,
181 <literal>ALTER OPERATOR SET COMMUTATOR</literal> will also set the
182 commutator of the <replaceable class="parameter">com_op</replaceable>
183 to be the target operator. Likewise, <literal>ALTER OPERATOR SET
184 NEGATOR</literal> will also set the negator of
185 the <replaceable class="parameter">neg_op</replaceable> to be the
186 target operator. Therefore, you must own the commutator or negator
187 operator as well as the target operator.
188 </para>
189 </refsect1>
190
191 <refsect1>
192 <title>Examples</title>
193
194 <para>
195 Change the owner of a custom operator <literal>a @@ b</literal> for type <type>text</type>:
196 <programlisting>
197 ALTER OPERATOR @@ (text, text) OWNER TO joe;
198 </programlisting>
199 </para>
200
201 <para>
202 Change the restriction and join selectivity estimator functions of a
203 custom operator <literal>a &amp;&amp; b</literal> for
204 type <type>int[]</type>:
205 <programlisting>
206 ALTER OPERATOR &amp;&amp; (int[], int[]) SET (RESTRICT = _int_contsel, JOIN = _int_contjoinsel);
207 </programlisting>
208 </para>
209
210 <para>
211 Mark the <literal>&amp;&amp;</literal> operator as being its own
212 commutator:
213 <programlisting>
214 ALTER OPERATOR &amp;&amp; (int[], int[]) SET (COMMUTATOR = &amp;&amp;);
215 </programlisting>
216 </para>
217
218 </refsect1>
219
220 <refsect1>
221 <title>Compatibility</title>
222
223 <para>
224 There is no <command>ALTER OPERATOR</command> statement in
225 the SQL standard.
226 </para>
227 </refsect1>
228
229 <refsect1>
230 <title>See Also</title>
231
232 <simplelist type="inline">
233 <member><xref linkend="sql-createoperator"/></member>
234 <member><xref linkend="sql-dropoperator"/></member>
235 </simplelist>
236 </refsect1>
237 </refentry>