doc/src/sgml/ref/alter_opfamily.sgml

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