Home • Explore • Blog • CI
☰



postgresql
5th chunk of `doc/src/sgml/ref/alter_opfamily.sgml`
c080ce3ff975a2f7cd0fbc5b6525395a6a9273e9df2b9b850000000100000bd8
 <refsect1>
  <title>Notes</title>

  <para>
   Notice that the <literal>DROP</literal> syntax only specifies the <quote>slot</quote>
   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 <literal>DROP FUNCTION</literal> 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.
  </para>

  <para>
   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.
  </para>

  <para>
   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.
  </para>
 </refsect1>

 <refsect1>
  <title>Examples</title>

  <para>
   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 <type>int4</type> and <type>int2</type>.
  </para>

<programlisting>
ALTER OPERATOR FAMILY integer_ops USING btree ADD

  -- int4 vs int2
  OPERATOR 1 &lt; (int4, int2) ,
  OPERATOR 2 &lt;= (int4, int2) ,
  OPERATOR 3 = (int4, int2) ,
  OPERATOR 4 &gt;= (int4, int2) ,
  OPERATOR 5 &gt; (int4, int2) ,
  FUNCTION 1 btint42cmp(int4, int2) ,

  -- int2 vs int4
  OPERATOR 1 &lt; (int2, int4) ,
  OPERATOR 2 &lt;= (int2, int4) ,
  OPERATOR 3 = (int2, int4) ,
  OPERATOR 4 &gt;= (int2, int4) ,
  OPERATOR 5 &gt; (int2, int4) ,
  FUNCTION 1 btint24cmp(int2, int4) ;
</programlisting>

  <para>
   To remove these entries again:
  </para>

<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) ;
</programlisting>
 </refsect1>

 <refsect1>
  <title>Compatibility</title>

  <para>
   There is no <command>ALTER OPERATOR FAMILY</command> statement in
   the SQL standard.
  </para>
 </refsect1>

 <refsect1>
  <title>See Also</title>

  <simplelist type="inline">
   <member><xref linkend="sql-createopfamily"/></member>
   <member><xref linkend="sql-dropopfamily"/></member>
   <member><xref linkend="sql-createopclass"/></member>
   <member><xref linkend="sql-alteropclass"/></member>
   <member><xref linkend="sql-dropopclass"/></member>
  </simplelist>
 </refsect1>
</refentry>
Title: ALTER OPERATOR FAMILY - Examples, Compatibility and See Also
Summary
This section first provides examples of how to add and remove cross-data-type operators and support functions to an operator family using the `ALTER OPERATOR FAMILY` command. It shows how to add operators and functions for `int4` and `int2` data types, and how to remove them using the `DROP` clause. Then it specifies that the command is not part of the SQL standard, and lists related commands like `CREATE OPERATOR FAMILY`, `DROP OPERATOR FAMILY`, `CREATE OPERATOR CLASS`, `ALTER OPERATOR CLASS`, and `DROP OPERATOR CLASS`.