Debian Codegraph @

doc/src/sgml/ref/alter_opfamily.sgml

raw
1 <!--
2 doc/src/sgml/ref/alter_opfamily.sgml
3 PostgreSQL documentation
4 -->
5
6 <refentry id="sql-alteropfamily">
7 <indexterm zone="sql-alteropfamily">
8 <primary>ALTER OPERATOR FAMILY</primary>
9 </indexterm>
10
11 <refmeta>
12 <refentrytitle>ALTER OPERATOR FAMILY</refentrytitle>
13 <manvolnum>7</manvolnum>
14 <refmiscinfo>SQL - Language Statements</refmiscinfo>
15 </refmeta>
16
17 <refnamediv>
18 <refname>ALTER OPERATOR FAMILY</refname>
19 <refpurpose>change the definition of an operator family</refpurpose>
20 </refnamediv>
21
22 <refsynopsisdiv>
23 <synopsis>
24 ALTER OPERATOR FAMILY <replaceable>name</replaceable> USING <replaceable class="parameter">index_method</replaceable> ADD
25 { OPERATOR <replaceable class="parameter">strategy_number</replaceable> <replaceable class="parameter">operator_name</replaceable> ( <replaceable class="parameter">op_type</replaceable>, <replaceable class="parameter">op_type</replaceable> )
26 [ FOR SEARCH | FOR ORDER BY <replaceable class="parameter">sort_family_name</replaceable> ]
27 | FUNCTION <replaceable class="parameter">support_number</replaceable> [ ( <replaceable class="parameter">op_type</replaceable> [ , <replaceable class="parameter">op_type</replaceable> ] ) ]
28 <replaceable class="parameter">function_name</replaceable> [ ( <replaceable class="parameter">argument_type</replaceable> [, ...] ) ]
29 } [, ... ]
30
31 ALTER OPERATOR FAMILY <replaceable>name</replaceable> USING <replaceable class="parameter">index_method</replaceable> DROP
32 { OPERATOR <replaceable class="parameter">strategy_number</replaceable> ( <replaceable class="parameter">op_type</replaceable> [ , <replaceable class="parameter">op_type</replaceable> ] )
33 | FUNCTION <replaceable class="parameter">support_number</replaceable> ( <replaceable class="parameter">op_type</replaceable> [ , <replaceable class="parameter">op_type</replaceable> ] )
34 } [, ... ]
35
36 ALTER OPERATOR FAMILY <replaceable>name</replaceable> USING <replaceable class="parameter">index_method</replaceable>
37 RENAME TO <replaceable>new_name</replaceable>
38
39 ALTER OPERATOR FAMILY <replaceable>name</replaceable> USING <replaceable class="parameter">index_method</replaceable>
40 OWNER TO { <replaceable>new_owner</replaceable> | CURRENT_ROLE | CURRENT_USER | SESSION_USER }
41
42 ALTER OPERATOR FAMILY <replaceable>name</replaceable> USING <replaceable class="parameter">index_method</replaceable>
43 SET SCHEMA <replaceable>new_schema</replaceable>
44 </synopsis>
45 </refsynopsisdiv>
46
47 <refsect1>
48 <title>Description</title>
49
50 <para>
51 <command>ALTER OPERATOR FAMILY</command> changes the definition of
52 an operator family. You can add operators and support functions
53 to the family, remove them from the family,
54 or change the family's name or owner.
55 </para>
56
57 <para>
58 When operators and support functions are added to a family with
59 <command>ALTER OPERATOR FAMILY</command>, they are not part of any
60 specific operator class within the family, but are just <quote>loose</quote>
61 within the family. This indicates that these operators and functions
62 are compatible with the family's semantics, but are not required for
63 correct functioning of any specific index. (Operators and functions
64 that are so required should be declared as part of an operator class,
65 instead; see <xref linkend="sql-createopclass"/>.)
66 <productname>PostgreSQL</productname> will allow loose members of a
67 family to be dropped from the family at any time, but members of an
68 operator class cannot be dropped without dropping the whole class and
69 any indexes that depend on it.
70 Typically, single-data-type operators
71 and functions are part of operator classes because they are needed to
72 support an index on that specific data type, while cross-data-type
73 operators and functions are made loose members of the family.
74 </para>
75
76 <para>
77 You must be a superuser to use <command>ALTER OPERATOR FAMILY</command>.
78 (This restriction is made because an erroneous operator family definition
79 could confuse or even crash the server.)
80 </para>
81
82 <para>
83 <command>ALTER OPERATOR FAMILY</command> does not presently check
84 whether the operator family definition includes all the operators and
85 functions required by the index method, nor whether the operators and
86 functions form a self-consistent set. It is the user's
87 responsibility to define a valid operator family.
88 </para>
89
90 <para>
91 Refer to <xref linkend="xindex"/> for further information.
92 </para>
93 </refsect1>
94
95 <refsect1>
96 <title>Parameters</title>
97
98 <variablelist>
99 <varlistentry>
100 <term><replaceable class="parameter">name</replaceable></term>
101 <listitem>
102 <para>
103 The name (optionally schema-qualified) of an existing operator
104 family.
105 </para>
106 </listitem>
107 </varlistentry>
108
109 <varlistentry>
110 <term><replaceable class="parameter">index_method</replaceable></term>
111 <listitem>
112 <para>
113 The name of the index method this operator family is for.
114 </para>
115 </listitem>
116 </varlistentry>
117
118 <varlistentry>
119 <term><replaceable class="parameter">strategy_number</replaceable></term>
120 <listitem>
121 <para>
122 The index method's strategy number for an operator
123 associated with the operator family.
124 </para>
125 </listitem>
126 </varlistentry>
127
128 <varlistentry>
129 <term><replaceable class="parameter">operator_name</replaceable></term>
130 <listitem>
131 <para>
132 The name (optionally schema-qualified) of an operator associated
133 with the operator family.
134 </para>
135 </listitem>
136 </varlistentry>
137
138 <varlistentry>
139 <term><replaceable class="parameter">op_type</replaceable></term>
140 <listitem>
141 <para>
142 In an <literal>OPERATOR</literal> clause,
143 the operand data type(s) of the operator, or <literal>NONE</literal> to
144 signify a prefix operator. Unlike the comparable
145 syntax in <command>CREATE OPERATOR CLASS</command>, the operand data types
146 must always be specified.
147 </para>
148
149 <para>
150 In an <literal>ADD FUNCTION</literal> clause, the operand data type(s) the
151 function is intended to support, if different from
152 the input data type(s) of the function. For B-tree comparison functions
153 and hash functions it is not necessary to specify <replaceable
154 class="parameter">op_type</replaceable> since the function's input
155 data type(s) are always the correct ones to use. For B-tree sort
156 support functions, B-Tree equal image functions, and all
157 functions in GiST, SP-GiST and GIN operator classes, it is
158 necessary to specify the operand data type(s) the function is to
159 be used with.
160 </para>
161
162 <para>
163 In a <literal>DROP FUNCTION</literal> clause, the operand data type(s) the
164 function is intended to support must be specified.
165 </para>
166 </listitem>
167 </varlistentry>
168
169 <varlistentry>
170 <term><replaceable class="parameter">sort_family_name</replaceable></term>
171 <listitem>
172 <para>
173 The name (optionally schema-qualified) of an existing <literal>btree</literal> operator
174 family that describes the sort ordering associated with an ordering
175 operator.
176 </para>
177
178 <para>
179 If neither <literal>FOR SEARCH</literal> nor <literal>FOR ORDER BY</literal> is
180 specified, <literal>FOR SEARCH</literal> is the default.
181 </para>
182 </listitem>
183 </varlistentry>
184
185 <varlistentry>
186 <term><replaceable class="parameter">support_number</replaceable></term>
187 <listitem>
188 <para>
189 The index method's support function number for a
190 function associated with the operator family.
191 </para>
192 </listitem>
193 </varlistentry>
194
195 <varlistentry>
196 <term><replaceable class="parameter">function_name</replaceable></term>
197 <listitem>
198 <para>
199 The name (optionally schema-qualified) of a function that is an index
200 method support function for the operator family. If no argument list
201 is specified, the name must be unique in its schema.
202 </para>
203 </listitem>
204 </varlistentry>
205
206 <varlistentry>
207 <term><replaceable class="parameter">argument_type</replaceable></term>
208 <listitem>
209 <para>
210 The parameter data type(s) of the function.
211 </para>
212 </listitem>
213 </varlistentry>
214
215 <varlistentry>
216 <term><replaceable class="parameter">new_name</replaceable></term>
217 <listitem>
218 <para>
219 The new name of the operator family.
220 </para>
221 </listitem>
222 </varlistentry>
223
224 <varlistentry>
225 <term><replaceable class="parameter">new_owner</replaceable></term>
226 <listitem>
227 <para>
228 The new owner of the operator family.
229 </para>
230 </listitem>
231 </varlistentry>
232
233 <varlistentry>
234 <term><replaceable class="parameter">new_schema</replaceable></term>
235 <listitem>
236 <para>
237 The new schema for the operator family.
238 </para>
239 </listitem>
240 </varlistentry>
241 </variablelist>
242
243 <para>
244 The <literal>OPERATOR</literal> and <literal>FUNCTION</literal>
245 clauses can appear in any order.
246 </para>
247
248 </refsect1>
249
250 <refsect1>
251 <title>Notes</title>
252
253 <para>
254 Notice that the <literal>DROP</literal> syntax only specifies the <quote>slot</quote>
255 in the operator family, by strategy or support number and input data
256 type(s). The name of the operator or function occupying the slot is not
257 mentioned. Also, for <literal>DROP FUNCTION</literal> the type(s) to specify
258 are the input data type(s) the function is intended to support; for
259 GiST, SP-GiST and GIN indexes this might have nothing to do with the actual
260 input argument types of the function.
261 </para>
262
263 <para>
264 Because the index machinery does not check access permissions on functions
265 before using them, including a function or operator in an operator family
266 is tantamount to granting public execute permission on it. This is usually
267 not an issue for the sorts of functions that are useful in an operator
268 family.
269 </para>
270
271 <para>
272 The operators should not be defined by SQL functions. An SQL function
273 is likely to be inlined into the calling query, which will prevent
274 the optimizer from recognizing that the query matches an index.
275 </para>
276 </refsect1>
277
278 <refsect1>
279 <title>Examples</title>
280
281 <para>
282 The following example command adds cross-data-type operators and
283 support functions to an operator family that already contains B-tree
284 operator classes for data types <type>int4</type> and <type>int2</type>.
285 </para>
286
287 <programlisting>
288 ALTER OPERATOR FAMILY integer_ops USING btree ADD
289
290 -- int4 vs int2
291 OPERATOR 1 &lt; (int4, int2) ,
292 OPERATOR 2 &lt;= (int4, int2) ,
293 OPERATOR 3 = (int4, int2) ,
294 OPERATOR 4 &gt;= (int4, int2) ,
295 OPERATOR 5 &gt; (int4, int2) ,
296 FUNCTION 1 btint42cmp(int4, int2) ,
297
298 -- int2 vs int4
299 OPERATOR 1 &lt; (int2, int4) ,
300 OPERATOR 2 &lt;= (int2, int4) ,
301 OPERATOR 3 = (int2, int4) ,
302 OPERATOR 4 &gt;= (int2, int4) ,
303 OPERATOR 5 &gt; (int2, int4) ,
304 FUNCTION 1 btint24cmp(int2, int4) ;
305 </programlisting>
306
307 <para>
308 To remove these entries again:
309 </para>
310
311 <programlisting>
312 ALTER OPERATOR FAMILY integer_ops USING btree DROP
313
314 -- int4 vs int2
315 OPERATOR 1 (int4, int2) ,
316 OPERATOR 2 (int4, int2) ,
317 OPERATOR 3 (int4, int2) ,
318 OPERATOR 4 (int4, int2) ,
319 OPERATOR 5 (int4, int2) ,
320 FUNCTION 1 (int4, int2) ,
321
322 -- int2 vs int4
323 OPERATOR 1 (int2, int4) ,
324 OPERATOR 2 (int2, int4) ,
325 OPERATOR 3 (int2, int4) ,
326 OPERATOR 4 (int2, int4) ,
327 OPERATOR 5 (int2, int4) ,
328 FUNCTION 1 (int2, int4) ;
329 </programlisting>
330 </refsect1>
331
332 <refsect1>
333 <title>Compatibility</title>
334
335 <para>
336 There is no <command>ALTER OPERATOR FAMILY</command> statement in
337 the SQL standard.
338 </para>
339 </refsect1>
340
341 <refsect1>
342 <title>See Also</title>
343
344 <simplelist type="inline">
345 <member><xref linkend="sql-createopfamily"/></member>
346 <member><xref linkend="sql-dropopfamily"/></member>
347 <member><xref linkend="sql-createopclass"/></member>
348 <member><xref linkend="sql-alteropclass"/></member>
349 <member><xref linkend="sql-dropopclass"/></member>
350 </simplelist>
351 </refsect1>
352 </refentry>