diff options
Diffstat (limited to 'doc/src/sgml/html/sql-alteropfamily.html')
| -rw-r--r-- | doc/src/sgml/html/sql-alteropfamily.html | 181 |
1 files changed, 181 insertions, 0 deletions
diff --git a/doc/src/sgml/html/sql-alteropfamily.html b/doc/src/sgml/html/sql-alteropfamily.html new file mode 100644 index 0000000..eb35b94 --- /dev/null +++ b/doc/src/sgml/html/sql-alteropfamily.html @@ -0,0 +1,181 @@ +<?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>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 xmlns="http://www.w3.org/TR/xhtml1/transitional" 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 14.5 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></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"> +ALTER OPERATOR FAMILY <em class="replaceable"><code>name</code></em> USING <em class="replaceable"><code>index_method</code></em> ADD + { 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> ) + [ FOR SEARCH | FOR ORDER BY <em class="replaceable"><code>sort_family_name</code></em> ] + | 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> ] ) ] + <em class="replaceable"><code>function_name</code></em> [ ( <em class="replaceable"><code>argument_type</code></em> [, ...] ) ] + } [, ... ] + +ALTER OPERATOR FAMILY <em class="replaceable"><code>name</code></em> USING <em class="replaceable"><code>index_method</code></em> DROP + { 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> ] ) + | 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> ] ) + } [, ... ] + +ALTER OPERATOR FAMILY <em class="replaceable"><code>name</code></em> USING <em class="replaceable"><code>index_method</code></em> + RENAME TO <em class="replaceable"><code>new_name</code></em> + +ALTER OPERATOR FAMILY <em class="replaceable"><code>name</code></em> USING <em class="replaceable"><code>index_method</code></em> + OWNER TO { <em class="replaceable"><code>new_owner</code></em> | CURRENT_ROLE | CURRENT_USER | SESSION_USER } + +ALTER OPERATOR FAMILY <em class="replaceable"><code>name</code></em> USING <em class="replaceable"><code>index_method</code></em> + SET SCHEMA <em class="replaceable"><code>new_schema</code></em> +</pre></div><div class="refsect1" id="id-1.9.3.22.5"><h2>Description</h2><p> + <code class="command">ALTER OPERATOR FAMILY</code> changes the definition of + an operator family. You can add operators and support functions + to the family, remove them from the family, + or change the family's name or owner. + </p><p> + When operators and support functions are added to a family with + <code class="command">ALTER OPERATOR FAMILY</code>, they are not part of any + specific operator class within the family, but are just <span class="quote">“<span class="quote">loose</span>”</span> + within the family. This indicates that these operators and functions + are compatible with the family's semantics, but are not required for + correct functioning of any specific index. (Operators and functions + that are so required should be declared as part of an operator class, + instead; see <a class="xref" href="sql-createopclass.html" title="CREATE OPERATOR CLASS"><span class="refentrytitle">CREATE OPERATOR CLASS</span></a>.) + <span class="productname">PostgreSQL</span> will allow loose members of a + family to be dropped from the family at any time, but members of an + operator class cannot be dropped without dropping the whole class and + any indexes that depend on it. + Typically, single-data-type operators + and functions are part of operator classes because they are needed to + support an index on that specific data type, while cross-data-type + operators and functions are made loose members of the family. + </p><p> + You must be a superuser to use <code class="command">ALTER OPERATOR FAMILY</code>. + (This restriction is made because an erroneous operator family definition + could confuse or even crash the server.) + </p><p> + <code class="command">ALTER OPERATOR FAMILY</code> does not presently check + whether the operator family definition includes all the operators and + functions required by the index method, nor whether the operators and + functions form a self-consistent set. It is the user's + responsibility to define a valid operator family. + </p><p> + Refer to <a class="xref" href="xindex.html" title="38.16. Interfacing Extensions to Indexes">Section 38.16</a> for further information. + </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> + The name (optionally schema-qualified) of an existing operator + family. + </p></dd><dt><span class="term"><em class="replaceable"><code>index_method</code></em></span></dt><dd><p> + The name of the index method this operator family is for. + </p></dd><dt><span class="term"><em class="replaceable"><code>strategy_number</code></em></span></dt><dd><p> + The index method's strategy number for an operator + associated with the operator family. + </p></dd><dt><span class="term"><em class="replaceable"><code>operator_name</code></em></span></dt><dd><p> + The name (optionally schema-qualified) of an operator associated + with the operator family. + </p></dd><dt><span class="term"><em class="replaceable"><code>op_type</code></em></span></dt><dd><p> + In an <code class="literal">OPERATOR</code> clause, + the operand data type(s) of the operator, or <code class="literal">NONE</code> to + signify a prefix operator. Unlike the comparable + syntax in <code class="command">CREATE OPERATOR CLASS</code>, the operand data types + must always be specified. + </p><p> + In an <code class="literal">ADD FUNCTION</code> clause, the operand data type(s) the + function is intended to support, if different from + the input data type(s) of the function. For B-tree comparison functions + and hash functions it is not necessary to specify <em class="replaceable"><code>op_type</code></em> since the function's input + data type(s) are always the correct ones to use. For B-tree sort + support functions, B-Tree equal image functions, and all + functions in GiST, SP-GiST and GIN operator classes, it is + necessary to specify the operand data type(s) the function is to + be used with. + </p><p> + In a <code class="literal">DROP FUNCTION</code> clause, the operand data type(s) the + function is intended to support must be specified. + </p></dd><dt><span class="term"><em class="replaceable"><code>sort_family_name</code></em></span></dt><dd><p> + The name (optionally schema-qualified) of an existing <code class="literal">btree</code> operator + family that describes the sort ordering associated with an ordering + operator. + </p><p> + If neither <code class="literal">FOR SEARCH</code> nor <code class="literal">FOR ORDER BY</code> is + specified, <code class="literal">FOR SEARCH</code> is the default. + </p></dd><dt><span class="term"><em class="replaceable"><code>support_number</code></em></span></dt><dd><p> + The index method's support function number for a + function associated with the operator family. + </p></dd><dt><span class="term"><em class="replaceable"><code>function_name</code></em></span></dt><dd><p> + The name (optionally schema-qualified) of a function that is an index + method support function for the operator family. If no argument list + is specified, the name must be unique in its schema. + </p></dd><dt><span class="term"><em class="replaceable"><code>argument_type</code></em></span></dt><dd><p> + The parameter data type(s) of the function. + </p></dd><dt><span class="term"><em class="replaceable"><code>new_name</code></em></span></dt><dd><p> + The new name of the operator family. + </p></dd><dt><span class="term"><em class="replaceable"><code>new_owner</code></em></span></dt><dd><p> + The new owner of the operator family. + </p></dd><dt><span class="term"><em class="replaceable"><code>new_schema</code></em></span></dt><dd><p> + The new schema for the operator family. + </p></dd></dl></div><p> + The <code class="literal">OPERATOR</code> and <code class="literal">FUNCTION</code> + clauses can appear in any order. + </p></div><div class="refsect1" id="id-1.9.3.22.7"><h2>Notes</h2><p> + Notice that the <code class="literal">DROP</code> syntax only specifies the <span class="quote">“<span class="quote">slot</span>”</span> + in the operator family, by strategy or support number and input data + type(s). The name of the operator or function occupying the slot is not + mentioned. Also, for <code class="literal">DROP FUNCTION</code> the type(s) to specify + are the input data type(s) the function is intended to support; for + GiST, SP-GiST and GIN indexes this might have nothing to do with the actual + input argument types of the function. + </p><p> + Because the index machinery does not check access permissions on functions + before using them, including a function or operator in an operator family + is tantamount to granting public execute permission on it. This is usually + not an issue for the sorts of functions that are useful in an operator + family. + </p><p> + The operators should not be defined by SQL functions. An SQL function + is likely to be inlined into the calling query, which will prevent + the optimizer from recognizing that the query matches an index. + </p><p> + Before <span class="productname">PostgreSQL</span> 8.4, the <code class="literal">OPERATOR</code> + clause could include a <code class="literal">RECHECK</code> option. This is no longer + supported because whether an index operator is <span class="quote">“<span class="quote">lossy</span>”</span> is now + determined on-the-fly at run time. This allows efficient handling of + cases where an operator might or might not be lossy. + </p></div><div class="refsect1" id="id-1.9.3.22.8"><h2>Examples</h2><p> + The following example command adds cross-data-type operators and + support functions to an operator family that already contains B-tree + operator classes for data types <code class="type">int4</code> and <code class="type">int2</code>. + </p><pre class="programlisting"> +ALTER OPERATOR FAMILY integer_ops USING btree ADD + + -- int4 vs int2 + OPERATOR 1 < (int4, int2) , + OPERATOR 2 <= (int4, int2) , + OPERATOR 3 = (int4, int2) , + OPERATOR 4 >= (int4, int2) , + OPERATOR 5 > (int4, int2) , + FUNCTION 1 btint42cmp(int4, int2) , + + -- int2 vs int4 + OPERATOR 1 < (int2, int4) , + OPERATOR 2 <= (int2, int4) , + OPERATOR 3 = (int2, int4) , + OPERATOR 4 >= (int2, int4) , + OPERATOR 5 > (int2, int4) , + FUNCTION 1 btint24cmp(int2, int4) ; +</pre><p> + To remove these entries again: + </p><pre class="programlisting"> +ALTER OPERATOR FAMILY integer_ops USING btree DROP + + -- int4 vs int2 + OPERATOR 1 (int4, int2) , + OPERATOR 2 (int4, int2) , + OPERATOR 3 (int4, int2) , + OPERATOR 4 (int4, int2) , + OPERATOR 5 (int4, int2) , + FUNCTION 1 (int4, int2) , + + -- int2 vs int4 + OPERATOR 1 (int2, int4) , + OPERATOR 2 (int2, int4) , + OPERATOR 3 (int2, int4) , + OPERATOR 4 (int2, int4) , + OPERATOR 5 (int2, int4) , + FUNCTION 1 (int2, int4) ; +</pre></div><div class="refsect1" id="id-1.9.3.22.9"><h2>Compatibility</h2><p> + There is no <code class="command">ALTER OPERATOR FAMILY</code> statement in + the SQL standard. + </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 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-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 14.5 Documentation">Home</a></td><td width="40%" align="right" valign="top"> ALTER POLICY</td></tr></table></div></body></html>
\ No newline at end of file |