codekingpro/portable-devtools
114k
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 FAMILY</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-alteropclass.html" title="ALTER OPERATOR CLASS" /><link rel="next" href="sql-alterpolicy.html" title="ALTER POLICY" /></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 FAMILY</th></tr><tr><td width="10%" align="left"><a accesskey="p" href="sql-alteropclass.html" title="ALTER OPERATOR CLASS">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-alterpolicy.html" title="ALTER POLICY">Next</a></td></tr></table><hr /></div><div class="refentry" id="SQL-ALTEROPFAMILY"><div class="titlepage"></div><a id="id-1.9.3.22.1" class="indexterm"></a><div class="refnamediv"><h2><span class="refentrytitle">ALTER OPERATOR FAMILY</span></h2><p>ALTER OPERATOR FAMILY — change the definition of an operator family</p></div><div class="refsynopsisdiv"><h2>Synopsis</h2><pre class="synopsis">3ALTER OPERATOR FAMILY <em class="replaceable"><code>name</code></em> USING <em class="replaceable"><code>index_method</code></em> ADD4 { OPERATOR <em class="replaceable"><code>strategy_number</code></em> <em class="replaceable"><code>operator_name</code></em> ( <em class="replaceable"><code>op_type</code></em>, <em class="replaceable"><code>op_type</code></em> )5 [ FOR SEARCH | FOR ORDER BY <em class="replaceable"><code>sort_family_name</code></em> ]6 | FUNCTION <em class="replaceable"><code>support_number</code></em> [ ( <em class="replaceable"><code>op_type</code></em> [ , <em class="replaceable"><code>op_type</code></em> ] ) ]7 <em class="replaceable"><code>function_name</code></em> [ ( <em class="replaceable"><code>argument_type</code></em> [, ...] ) ]8 } [, ... ]9 10ALTER OPERATOR FAMILY <em class="replaceable"><code>name</code></em> USING <em class="replaceable"><code>index_method</code></em> DROP11 { OPERATOR <em class="replaceable"><code>strategy_number</code></em> ( <em class="replaceable"><code>op_type</code></em> [ , <em class="replaceable"><code>op_type</code></em> ] )12 | FUNCTION <em class="replaceable"><code>support_number</code></em> ( <em class="replaceable"><code>op_type</code></em> [ , <em class="replaceable"><code>op_type</code></em> ] )13 } [, ... ]14 15ALTER OPERATOR FAMILY <em class="replaceable"><code>name</code></em> USING <em class="replaceable"><code>index_method</code></em>16 RENAME TO <em class="replaceable"><code>new_name</code></em>17 18ALTER OPERATOR FAMILY <em class="replaceable"><code>name</code></em> USING <em class="replaceable"><code>index_method</code></em>19 OWNER TO { <em class="replaceable"><code>new_owner</code></em> | CURRENT_ROLE | CURRENT_USER | SESSION_USER }20 21ALTER OPERATOR FAMILY <em class="replaceable"><code>name</code></em> USING <em class="replaceable"><code>index_method</code></em>22 SET SCHEMA <em class="replaceable"><code>new_schema</code></em>23</pre></div><div class="refsect1" id="id-1.9.3.22.5"><h2>Description</h2><p>24 <code class="command">ALTER OPERATOR FAMILY</code> changes the definition of25 an operator family. You can add operators and support functions26 to the family, remove them from the family,27 or change the family's name or owner.28 </p><p>29 When operators and support functions are added to a family with30 <code class="command">ALTER OPERATOR FAMILY</code>, they are not part of any31 specific operator class within the family, but are just <span class="quote">“<span class="quote">loose</span>”</span>32 within the family. This indicates that these operators and functions33 are compatible with the family's semantics, but are not required for34 correct functioning of any specific index. (Operators and functions35 that are so required should be declared as part of an operator class,36 instead; see <a class="xref" href="sql-createopclass.html" title="CREATE OPERATOR CLASS"><span class="refentrytitle">CREATE OPERATOR CLASS</span></a>.)37 <span class="productname">PostgreSQL</span> will allow loose members of a38 family to be dropped from the family at any time, but members of an39 operator class cannot be dropped without dropping the whole class and40 any indexes that depend on it.41 Typically, single-data-type operators42 and functions are part of operator classes because they are needed to43 support an index on that specific data type, while cross-data-type44 operators and functions are made loose members of the family.45 </p><p>46 You must be a superuser to use <code class="command">ALTER OPERATOR FAMILY</code>.47 (This restriction is made because an erroneous operator family definition48 could confuse or even crash the server.)49 </p><p>50 <code class="command">ALTER OPERATOR FAMILY</code> does not presently check51 whether the operator family definition includes all the operators and52 functions required by the index method, nor whether the operators and53 functions form a self-consistent set. It is the user's54 responsibility to define a valid operator family.55 </p><p>56 Refer to <a class="xref" href="xindex.html" title="38.16. Interfacing Extensions to Indexes">Section 38.16</a> for further information.57 </p></div><div class="refsect1" id="id-1.9.3.22.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>58 The name (optionally schema-qualified) of an existing operator59 family.60 </p></dd><dt><span class="term"><em class="replaceable"><code>index_method</code></em></span></dt><dd><p>61 The name of the index method this operator family is for.62 </p></dd><dt><span class="term"><em class="replaceable"><code>strategy_number</code></em></span></dt><dd><p>63 The index method's strategy number for an operator64 associated with the operator family.65 </p></dd><dt><span class="term"><em class="replaceable"><code>operator_name</code></em></span></dt><dd><p>66 The name (optionally schema-qualified) of an operator associated67 with the operator family.68 </p></dd><dt><span class="term"><em class="replaceable"><code>op_type</code></em></span></dt><dd><p>69 In an <code class="literal">OPERATOR</code> clause,70 the operand data type(s) of the operator, or <code class="literal">NONE</code> to71 signify a prefix operator. Unlike the comparable72 syntax in <code class="command">CREATE OPERATOR CLASS</code>, the operand data types73 must always be specified.74 </p><p>75 In an <code class="literal">ADD FUNCTION</code> clause, the operand data type(s) the76 function is intended to support, if different from77 the input data type(s) of the function. For B-tree comparison functions78 and hash functions it is not necessary to specify <em class="replaceable"><code>op_type</code></em> since the function's input79 data type(s) are always the correct ones to use. For B-tree sort80 support functions, B-Tree equal image functions, and all81 functions in GiST, SP-GiST and GIN operator classes, it is82 necessary to specify the operand data type(s) the function is to83 be used with.84 </p><p>85 In a <code class="literal">DROP FUNCTION</code> clause, the operand data type(s) the86 function is intended to support must be specified.87 </p></dd><dt><span class="term"><em class="replaceable"><code>sort_family_name</code></em></span></dt><dd><p>88 The name (optionally schema-qualified) of an existing <code class="literal">btree</code> operator89 family that describes the sort ordering associated with an ordering90 operator.91 </p><p>92 If neither <code class="literal">FOR SEARCH</code> nor <code class="literal">FOR ORDER BY</code> is93 specified, <code class="literal">FOR SEARCH</code> is the default.94 </p></dd><dt><span class="term"><em class="replaceable"><code>support_number</code></em></span></dt><dd><p>95 The index method's support function number for a96 function associated with the operator family.97 </p></dd><dt><span class="term"><em class="replaceable"><code>function_name</code></em></span></dt><dd><p>98 The name (optionally schema-qualified) of a function that is an index99 method support function for the operator family. If no argument list100 is specified, the name must be unique in its schema.101 </p></dd><dt><span class="term"><em class="replaceable"><code>argument_type</code></em></span></dt><dd><p>102 The parameter data type(s) of the function.103 </p></dd><dt><span class="term"><em class="replaceable"><code>new_name</code></em></span></dt><dd><p>104 The new name of the operator family.105 </p></dd><dt><span class="term"><em class="replaceable"><code>new_owner</code></em></span></dt><dd><p>106 The new owner of the operator family.107 </p></dd><dt><span class="term"><em class="replaceable"><code>new_schema</code></em></span></dt><dd><p>108 The new schema for the operator family.109 </p></dd></dl></div><p>110 The <code class="literal">OPERATOR</code> and <code class="literal">FUNCTION</code>111 clauses can appear in any order.112 </p></div><div class="refsect1" id="id-1.9.3.22.7"><h2>Notes</h2><p>113 Notice that the <code class="literal">DROP</code> syntax only specifies the <span class="quote">“<span class="quote">slot</span>”</span>114 in the operator family, by strategy or support number and input data115 type(s). The name of the operator or function occupying the slot is not116 mentioned. Also, for <code class="literal">DROP FUNCTION</code> the type(s) to specify117 are the input data type(s) the function is intended to support; for118 GiST, SP-GiST and GIN indexes this might have nothing to do with the actual119 input argument types of the function.120 </p><p>121 Because the index machinery does not check access permissions on functions122 before using them, including a function or operator in an operator family123 is tantamount to granting public execute permission on it. This is usually124 not an issue for the sorts of functions that are useful in an operator125 family.126 </p><p>127 The operators should not be defined by SQL functions. An SQL function128 is likely to be inlined into the calling query, which will prevent129 the optimizer from recognizing that the query matches an index.130 </p><p>131 Before <span class="productname">PostgreSQL</span> 8.4, the <code class="literal">OPERATOR</code>132 clause could include a <code class="literal">RECHECK</code> option. This is no longer133 supported because whether an index operator is <span class="quote">“<span class="quote">lossy</span>”</span> is now134 determined on-the-fly at run time. This allows efficient handling of135 cases where an operator might or might not be lossy.136 </p></div><div class="refsect1" id="id-1.9.3.22.8"><h2>Examples</h2><p>137 The following example command adds cross-data-type operators and138 support functions to an operator family that already contains B-tree139 operator classes for data types <code class="type">int4</code> and <code class="type">int2</code>.140 </p><pre class="programlisting">141ALTER OPERATOR FAMILY integer_ops USING btree ADD142 143 -- int4 vs int2144 OPERATOR 1 < (int4, int2) ,145 OPERATOR 2 <= (int4, int2) ,146 OPERATOR 3 = (int4, int2) ,147 OPERATOR 4 >= (int4, int2) ,148 OPERATOR 5 > (int4, int2) ,149 FUNCTION 1 btint42cmp(int4, int2) ,150 151 -- int2 vs int4152 OPERATOR 1 < (int2, int4) ,153 OPERATOR 2 <= (int2, int4) ,154 OPERATOR 3 = (int2, int4) ,155 OPERATOR 4 >= (int2, int4) ,156 OPERATOR 5 > (int2, int4) ,157 FUNCTION 1 btint24cmp(int2, int4) ;158</pre><p>159 To remove these entries again:160 </p><pre class="programlisting">161ALTER OPERATOR FAMILY integer_ops USING btree DROP162 163 -- int4 vs int2164 OPERATOR 1 (int4, int2) ,165 OPERATOR 2 (int4, int2) ,166 OPERATOR 3 (int4, int2) ,167 OPERATOR 4 (int4, int2) ,168 OPERATOR 5 (int4, int2) ,169 FUNCTION 1 (int4, int2) ,170 171 -- int2 vs int4172 OPERATOR 1 (int2, int4) ,173 OPERATOR 2 (int2, int4) ,174 OPERATOR 3 (int2, int4) ,175 OPERATOR 4 (int2, int4) ,176 OPERATOR 5 (int2, int4) ,177 FUNCTION 1 (int2, int4) ;178</pre></div><div class="refsect1" id="id-1.9.3.22.9"><h2>Compatibility</h2><p>179 There is no <code class="command">ALTER OPERATOR FAMILY</code> statement in180 the SQL standard.181 </p></div><div class="refsect1" id="id-1.9.3.22.10"><h2>See Also</h2><span class="simplelist"><a class="xref" href="sql-createopfamily.html" title="CREATE OPERATOR FAMILY"><span class="refentrytitle">CREATE OPERATOR FAMILY</span></a>, <a class="xref" href="sql-dropopfamily.html" title="DROP OPERATOR FAMILY"><span class="refentrytitle">DROP OPERATOR FAMILY</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-alteropclass.html" title="ALTER OPERATOR CLASS"><span class="refentrytitle">ALTER OPERATOR CLASS</span></a>, <a class="xref" href="sql-dropopclass.html" title="DROP OPERATOR CLASS"><span class="refentrytitle">DROP OPERATOR CLASS</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-alteropclass.html" title="ALTER OPERATOR CLASS">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-alterpolicy.html" title="ALTER POLICY">Next</a></td></tr><tr><td width="40%" align="left" valign="top">ALTER OPERATOR CLASS </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"> ALTER POLICY</td></tr></table></div></body></html>