Implement CREATE/DROP OPERATOR CLASS. Work still remains: need more

documentation (xindex.sgml should be rewritten), need to teach pg_dump
about it, need to update contrib modules that currently build pg_opclass
entries by hand.  Original patch by Bill Studenmund, grammar adjustments
and general update for 7.3 by Tom Lane.

6 files changed, 509 insertions, 8 deletions

diff --git a/doc/src/sgml/ref/allfiles.sgml b/doc/src/sgml/ref/allfiles.sgml
index a37d58ab7d9..1ee6f2f2004 100644
--- a/doc/src/sgml/ref/allfiles.sgml
+++ b/doc/src/sgml/ref/allfiles.sgml
@@ -1,5 +1,5 @@
 <!--
-$Header: /cvsroot/pgsql/doc/src/sgml/ref/allfiles.sgml,v 1.42 2002/07/22 08:57:15 ishii Exp $
+$Header: /cvsroot/pgsql/doc/src/sgml/ref/allfiles.sgml,v 1.43 2002/07/29 22:14:10 tgl Exp $
 PostgreSQL documentation
 Complete list of usable sgml source files in this directory.
 -->
@@ -61,6 +61,7 @@ Complete list of usable sgml source files in this directory.
 <!entity createIndex        system "create_index.sgml">
 <!entity createLanguage     system "create_language.sgml">
 <!entity createOperator     system "create_operator.sgml">
+<!entity createOperatorClass system "create_opclass.sgml">
 <!entity createRule         system "create_rule.sgml">
 <!entity createSchema       system "create_schema.sgml">
 <!entity createSequence     system "create_sequence.sgml">
@@ -82,6 +83,7 @@ Complete list of usable sgml source files in this directory.
 <!entity dropIndex          system "drop_index.sgml">
 <!entity dropLanguage       system "drop_language.sgml">
 <!entity dropOperator       system "drop_operator.sgml">
+<!entity dropOperatorClass  system "drop_opclass.sgml">
 <!entity dropRule           system "drop_rule.sgml">
 <!entity dropSchema         system "drop_schema.sgml">
 <!entity dropSequence       system "drop_sequence.sgml">
diff --git a/doc/src/sgml/ref/create_opclass.sgml b/doc/src/sgml/ref/create_opclass.sgml
new file mode 100644
index 00000000000..6d00f081f81
--- /dev/null
+++ b/doc/src/sgml/ref/create_opclass.sgml
@@ -0,0 +1,316 @@
+<!--
+$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_opclass.sgml,v 1.1 2002/07/29 22:14:10 tgl Exp $
+PostgreSQL documentation
+-->
+
+<refentry id="SQL-CREATEOPCLASS">
+ <refmeta>
+  <refentrytitle id="sql-createopclass-title">CREATE OPERATOR CLASS</refentrytitle>
+  <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+ <refnamediv>
+  <refname>
+   CREATE OPERATOR CLASS
+  </refname>
+  <refpurpose>
+   define a new operator class for indexes
+  </refpurpose>
+  </refnamediv>
+ <refsynopsisdiv>
+  <refsynopsisdivinfo>
+   <date>2002-07-28</date>
+  </refsynopsisdivinfo>
+  <synopsis>
+CREATE OPERATOR CLASS <replaceable class="parameter">name</replaceable> [ DEFAULT ] FOR TYPE <replaceable class="parameter">data_type</replaceable> USING <replaceable class="parameter">access_method</replaceable> AS
+  {  OPERATOR <replaceable class="parameter">strategy_number</replaceable> <replaceable class="parameter">operator_id</replaceable> [ ( <replaceable class="parameter">type</replaceable>, <replaceable class="parameter">type</replaceable> ) ] [ RECHECK ]
+   | FUNCTION <replaceable class="parameter">support_number</replaceable> <replaceable class="parameter">func_name</replaceable> ( <replaceable class="parameter">parameter_types</replaceable> )
+   | STORAGE <replaceable class="parameter">storage_type</replaceable>
+  } [, ... ]
+  </synopsis>
+  
+  <refsect2 id="R2-SQL-CREATEOPCLASS-1">
+   <refsect2info>
+    <date>2002-07-28</date>
+   </refsect2info>
+   <title>
+    Inputs
+   </title>
+   <para>
+
+    <variablelist>
+     <varlistentry>
+      <term><replaceable class="parameter">name</replaceable></term>
+      <listitem>
+       <para>
+	The name of the operator class to be created.
+	The name may be schema-qualified.
+       </para>
+      </listitem>
+     </varlistentry>
+     <varlistentry>
+      <term><literal>DEFAULT</></term>
+      <listitem>
+       <para>
+        If present, the operator class will become the default index
+	operator class for its datatype.  At most one operator class
+	can be the default for a specific datatype and access method.
+       </para>
+      </listitem>
+     </varlistentry>
+     <varlistentry>
+      <term><replaceable class="parameter">data_type</replaceable></term>
+      <listitem>
+       <para>
+        The column datatype that this operator class is for.
+       </para>
+      </listitem>
+     </varlistentry>
+     <varlistentry>
+      <term><replaceable class="parameter">access_method</replaceable></term>
+      <listitem>
+       <para>
+	The name of the index access method this operator class is for.
+       </para>
+      </listitem>
+     </varlistentry>
+     <varlistentry>
+      <term><replaceable class="parameter">strategy_number</replaceable></term>
+      <listitem>
+       <para>
+        The index access method's strategy number for an operator associated
+	with the operator class.
+       </para>
+      </listitem>
+     </varlistentry>
+     <varlistentry>
+      <term><replaceable class="parameter">operator_id</replaceable></term>
+      <listitem>
+       <para>
+	The identifier (optionally schema-qualified) of an operator associated
+	with the operator class.
+       </para>
+      </listitem>
+     </varlistentry>
+     <varlistentry>
+      <term><replaceable class="parameter">type</replaceable></term>
+      <listitem>
+       <para>
+        The input datatype(s) of an operator, or <literal>NONE</> to
+	signify a left-unary or right-unary operator.  The input datatypes
+	may be omitted in the normal case where they are the same as the
+	operator class's datatype.
+       </para>
+      </listitem>
+     </varlistentry>
+     <varlistentry>
+      <term><literal>RECHECK</></term>
+      <listitem>
+       <para>
+        If present, the index is <quote>lossy</> for this operator,
+	and so the tuples retrieved using the index must be rechecked
+	to verify that they actually satisfy the qualification clause
+	involving this operator.
+       </para>
+      </listitem>
+     </varlistentry>
+     <varlistentry>
+      <term><replaceable class="parameter">support_number</replaceable></term>
+      <listitem>
+       <para>
+        The index access method's support procedure number for a function
+	associated with the operator class.
+       </para>
+      </listitem>
+     </varlistentry>
+     <varlistentry>
+      <term><replaceable class="parameter">func_name</replaceable></term>
+      <listitem>
+       <para>
+        The name (optionally schema-qualified) of a function that is
+	an index access method support procedure for the operator class.
+       </para>
+      </listitem>
+     </varlistentry>
+     <varlistentry>
+      <term><replaceable class="parameter">parameter_types</replaceable></term>
+      <listitem>
+       <para>
+        The parameter datatype(s) of the function.
+       </para>
+      </listitem>
+     </varlistentry>
+     <varlistentry>
+      <term><replaceable class="parameter">storage_type</replaceable></term>
+      <listitem>
+       <para>
+        The datatype actually stored in the index.  Normally this is the
+	same as the column datatype, but some index access methods (only
+	GIST at this writing) allow it to be different.  The
+	<literal>STORAGE</> clause must be omitted unless the index access
+	method allows a different type to be used.
+       </para>
+      </listitem>
+     </varlistentry>
+    </variablelist>
+   </para>
+  </refsect2>
+
+  <refsect2 id="R2-SQL-CREATEOPCLASS-2">
+   <refsect2info>
+    <date>2002-07-28</date>
+   </refsect2info>
+   <title>
+    Outputs
+   </title>
+   <para>
+    <variablelist>
+     <varlistentry>
+      <term><computeroutput>
+CREATE OPERATOR CLASS
+       </computeroutput></term>
+      <listitem>
+       <para>
+	Message returned if the operator class is successfully created.
+       </para>
+      </listitem>
+     </varlistentry>
+    </variablelist>
+   </para>
+  </refsect2>
+ </refsynopsisdiv>
+
+ <refsect1 id="R1-SQL-CREATEOPCLASS-1">
+  <refsect1info>
+   <date>2002-07-28</date>
+  </refsect1info>
+  <title>
+   Description
+  </title>
+  <para>
+   <command>CREATE OPERATOR CLASS</command> defines a new operator class,
+   <replaceable class="parameter">name</replaceable>.
+  </para>
+  <para>
+   An operator class defines how a particular datatype can be used with
+   an index.  The operator class specifies that certain operators will fill
+   particular roles or <quote>strategies</> for this datatype and this
+   access method.  The operator class also specifies the support procedures to
+   be used by 
+   the index access method when the operator class is selected for an
+   index column.  All the operators and functions used by an operator
+   class must be defined before the operator class is created.
+  </para>
+
+  <para>
+   If a schema name is given then the operator class is created in the
+   specified schema.  Otherwise it is created in the current schema (the one
+   at the front of the search path; see <literal>CURRENT_SCHEMA()</>).
+   Two operator classes in the same schema can have the same name only if they
+   are for different index access methods.
+  </para>
+  <para>
+   The user who defines an operator class becomes its owner.  The user
+   must own the datatype for which the operator class is being defined,
+   and must have execute permission for all referenced operators and functions.
+  </para>
+
+  <para>
+   <command>CREATE OPERATOR CLASS</command> does not presently check
+   whether the class definition includes all the operators and functions
+   required by the index access method.  It is the user's
+   responsibility to define a valid operator class.
+  </para>
+
+  <para>
+    Refer to the chapter on interfacing extensions to indexes in the
+    <citetitle>PostgreSQL Programmer's Guide</citetitle>
+    for further information.
+  </para>
+  
+  <refsect2 id="R2-SQL-CREATEOPCLASS-3">
+   <refsect2info>
+    <date>2002-07-28</date>
+   </refsect2info>
+   <title>
+    Notes
+   </title>
+   <para>
+    Refer to
+    <xref linkend="sql-dropopclass" endterm="sql-dropopclass-title">
+    to delete user-defined operator classes from a database.
+   </para>
+  </refsect2>
+ </refsect1>
+  
+ <refsect1 id="R1-SQL-CREATEOPCLASS-2">
+  <title>
+   Usage
+  </title>
+  <para>
+   The following example command defines a GiST index operator class
+   for datatype <literal>_int4</> (array of int4).  See
+   <filename>contrib/intarray/</> for the complete example.
+  </para>
+
+  <programlisting>
+CREATE OPERATOR CLASS gist__int_ops
+    DEFAULT FOR TYPE _int4 USING gist AS
+        OPERATOR        3       &&,
+        OPERATOR        6       =       RECHECK,
+        OPERATOR        7       @,
+        OPERATOR        8       ~,
+        OPERATOR        20      @@ (_int4, query_int),
+        FUNCTION        1       g_int_consistent (opaque, _int4, int4),
+        FUNCTION        2       g_int_union (bytea, opaque),
+        FUNCTION        3       g_int_compress (opaque),
+        FUNCTION        4       g_int_decompress (opaque),
+        FUNCTION        5       g_int_penalty (opaque, opaque, opaque),
+        FUNCTION        6       g_int_picksplit (opaque, opaque),
+        FUNCTION        7       g_int_same (_int4, _int4, opaque);
+  </programlisting>  
+
+  <para>
+   The <literal>OPERATOR</>, <literal>FUNCTION</>, and <literal>STORAGE</>
+   clauses may appear in any order.
+  </para>
+ </refsect1>
+ 
+ <refsect1 id="R1-SQL-CREATEOPCLASS-3">
+  <title>
+   Compatibility
+  </title>
+  
+  <refsect2 id="R2-SQL-CREATEOPCLASS-4">
+   <refsect2info>
+    <date>2002-07-28</date>
+   </refsect2info>
+   <title>
+    SQL92
+   </title>
+
+   <para>
+    <command>CREATE OPERATOR CLASS</command>
+    is a <productname>PostgreSQL</productname> extension.
+    There is no <command>CREATE OPERATOR CLASS</command>
+    statement in <acronym>SQL92</acronym>.
+   </para>
+  </refsect2>
+ </refsect1>
+</refentry>
+
+<!-- Keep this comment at the end of the file
+Local variables:
+mode: sgml
+sgml-omittag:nil
+sgml-shorttag:t
+sgml-minimize-attributes:nil
+sgml-always-quote-attributes:t
+sgml-indent-step:1
+sgml-indent-data:t
+sgml-parent-document:nil
+sgml-default-dtd-file:"../reference.ced"
+sgml-exposed-tags:nil
+sgml-local-catalogs:"/usr/lib/sgml/catalog"
+sgml-local-ecat-files:nil
+End:
+-->
diff --git a/doc/src/sgml/ref/drop_opclass.sgml b/doc/src/sgml/ref/drop_opclass.sgml
new file mode 100644
index 00000000000..631a45aaeba
--- /dev/null
+++ b/doc/src/sgml/ref/drop_opclass.sgml
@@ -0,0 +1,184 @@
+<!--
+$Header: /cvsroot/pgsql/doc/src/sgml/ref/drop_opclass.sgml,v 1.1 2002/07/29 22:14:10 tgl Exp $
+PostgreSQL documentation
+-->
+
+<refentry id="SQL-DROPOPCLASS">
+ <refmeta>
+  <refentrytitle id="SQL-DROPOPCLASS-TITLE">DROP OPERATOR CLASS</refentrytitle>
+  <refmiscinfo>SQL - Language Statements</refmiscinfo>
+ </refmeta>
+ <refnamediv>
+  <refname>
+   DROP OPERATOR CLASS
+  </refname>
+  <refpurpose>
+   remove a user-defined operator class
+  </refpurpose>
+ </refnamediv>
+  
+ <refsynopsisdiv>
+  <refsynopsisdivinfo>
+   <date>2002-07-28</date>
+  </refsynopsisdivinfo>
+  <synopsis>
+DROP OPERATOR CLASS <replaceable class="PARAMETER">name</replaceable> USING <replaceable class="PARAMETER">access_method</replaceable> [ CASCADE | RESTRICT ]
+  </synopsis>
+  
+  <refsect2 id="R2-SQL-DROPOPCLASS-1">
+   <refsect2info>
+    <date>2002-07-28</date>
+   </refsect2info>
+   <title>
+    Inputs
+   </title>
+   <para>
+    <variablelist>
+     <varlistentry>
+      <term><replaceable class="parameter">name</replaceable></term>
+      <listitem>
+       <para>
+	The name (optionally schema-qualified) of an existing operator class.
+       </para>
+      </listitem>
+     </varlistentry>
+     <varlistentry>
+      <term><replaceable class="parameter">access_method</replaceable></term>
+      <listitem>
+       <para>
+	The name of the index access method the operator class is for.
+       </para>
+      </listitem>
+     </varlistentry>
+     <varlistentry>
+      <term>CASCADE</term>
+      <listitem>
+       <para>
+        Automatically drop objects that depend on the operator class.
+       </para>
+      </listitem>
+     </varlistentry>
+     <varlistentry>
+      <term>RESTRICT</term>
+      <listitem>
+       <para>
+        Refuse to drop the operator class if there are any dependent objects.
+	This is the default.
+       </para>
+      </listitem>
+     </varlistentry>
+    </variablelist>
+   </para>
+  </refsect2>
+
+  <refsect2 id="R2-SQL-DROPOPCLASS-2">
+   <refsect2info>
+    <date>2002-07-28</date>
+   </refsect2info>
+   <title>
+    Outputs
+   </title>
+   <para>
+
+    <variablelist>
+     <varlistentry>
+      <term><computeroutput>
+DROP OPERATOR CLASS
+       </computeroutput></term>
+      <listitem>
+       <para>
+	The message returned if the command is successful.
+       </para>
+      </listitem>
+     </varlistentry>
+    </variablelist>
+   </para>
+  </refsect2>
+ </refsynopsisdiv>
+
+ <refsect1 id="R1-SQL-DROPOPCLASS-1">
+  <refsect1info>
+   <date>2002-07-28</date>
+  </refsect1info>
+  <title>
+   Description
+  </title>
+  <para>
+   <command>DROP OPERATOR CLASS</command> drops an existing operator class
+   from the database.
+   To execute this command you must be the owner of the operator class.
+  </para>
+
+  <refsect2 id="R2-SQL-DROPOPCLASS-3">
+   <refsect2info>
+    <date>2002-07-28</date>
+   </refsect2info>
+   <title>
+    Notes
+   </title>
+   <para>
+    The <command>DROP OPERATOR CLASS</command> statement is a 
+    <productname>PostgreSQL</productname>
+    language extension.
+   </para>
+   <para>
+    Refer to
+    <xref linkend="sql-createopclass" endterm="sql-createopclass-title">
+    for information on how to create operator classes.
+   </para>
+  </refsect2>
+ </refsect1>
+
+ <refsect1 id="R1-SQL-DROPOPCLASS-2">
+  <title>
+   Usage
+  </title>
+  <para>
+   Remove btree operator class <literal>widget_ops</literal>:
+
+   <programlisting>
+DROP OPERATOR CLASS widget_ops USING btree;
+   </programlisting>
+
+   This command will not execute if there are any existing indexes
+   that use the operator class.  Add <literal>CASCADE</> to drop
+   such indexes along with the operator class.
+  </para>
+ </refsect1>
+ 
+ <refsect1 id="R1-SQL-DROPOPCLASS-3">
+  <title>
+   Compatibility
+  </title>
+
+  <refsect2 id="R2-SQL-DROPOPCLASS-4">
+   <refsect2info>
+    <date>2002-07-28</date>
+   </refsect2info>
+   <title>
+    SQL92
+   </title>
+   <para>
+    There is no <command>DROP OPERATOR CLASS</command> in
+    <acronym>SQL92</acronym>.
+   </para>
+  </refsect2>
+ </refsect1>
+</refentry>
+
+<!-- Keep this comment at the end of the file
+Local variables:
+mode: sgml
+sgml-omittag:nil
+sgml-shorttag:t
+sgml-minimize-attributes:nil
+sgml-always-quote-attributes:t
+sgml-indent-step:1
+sgml-indent-data:t
+sgml-parent-document:nil
+sgml-default-dtd-file:"../reference.ced"
+sgml-exposed-tags:nil
+sgml-local-catalogs:"/usr/lib/sgml/catalog"
+sgml-local-ecat-files:nil
+End:
+-->
diff --git a/doc/src/sgml/ref/drop_operator.sgml b/doc/src/sgml/ref/drop_operator.sgml
index 1cfb824090c..edd99bd75e3 100644
--- a/doc/src/sgml/ref/drop_operator.sgml
+++ b/doc/src/sgml/ref/drop_operator.sgml
@@ -1,5 +1,5 @@
 <!--
-$Header: /cvsroot/pgsql/doc/src/sgml/ref/drop_operator.sgml,v 1.17 2002/07/12 18:43:13 tgl Exp $
+$Header: /cvsroot/pgsql/doc/src/sgml/ref/drop_operator.sgml,v 1.18 2002/07/29 22:14:10 tgl Exp $
 PostgreSQL documentation
 -->
 
@@ -172,10 +172,6 @@ ERROR: RemoveOperator: right unary operator '<replaceable class="PARAMETER">oper
     <xref linkend="sql-createoperator" endterm="sql-createoperator-title">
     for information on how to create operators.
    </para>
-   <para>
-    It is the user's responsibility to remove any access method
-    operator classes that rely on the deleted operator.
-   </para>
   </refsect2>
  </refsect1>
 
diff --git a/doc/src/sgml/reference.sgml b/doc/src/sgml/reference.sgml
index e2491f54082..2101dfe8a1b 100644
--- a/doc/src/sgml/reference.sgml
+++ b/doc/src/sgml/reference.sgml
@@ -1,5 +1,5 @@
 <!-- reference.sgml
-$Header: /cvsroot/pgsql/doc/src/sgml/reference.sgml,v 1.31 2002/07/22 08:57:15 ishii Exp $
+$Header: /cvsroot/pgsql/doc/src/sgml/reference.sgml,v 1.32 2002/07/29 22:14:10 tgl Exp $
 
 PostgreSQL Reference Manual
 -->
@@ -70,6 +70,7 @@ PostgreSQL Reference Manual
    &createIndex;
    &createLanguage;
    &createOperator;
+   &createOperatorClass;
    &createRule;
    &createSchema;
    &createSequence;
@@ -91,6 +92,7 @@ PostgreSQL Reference Manual
    &dropIndex;
    &dropLanguage;
    &dropOperator;
+   &dropOperatorClass;
    &dropRule;
    &dropSchema;
    &dropSequence;
diff --git a/doc/src/sgml/release.sgml b/doc/src/sgml/release.sgml
index 47f36858f92..af82154d8c7 100644
--- a/doc/src/sgml/release.sgml
+++ b/doc/src/sgml/release.sgml
@@ -1,5 +1,5 @@
 <!--
-$Header: /cvsroot/pgsql/doc/src/sgml/release.sgml,v 1.143 2002/07/20 15:12:55 tgl Exp $
+$Header: /cvsroot/pgsql/doc/src/sgml/release.sgml,v 1.144 2002/07/29 22:14:10 tgl Exp $
 -->
 
 <appendix id="release">
@@ -24,6 +24,7 @@ CDATA means the content is "SGML-free", so you can write without
 worries about funny characters.
 -->
 <literallayout><![CDATA[
+CREATE OPERATOR CLASS/DROP OPERATOR CLASS
 CREATE CAST/DROP CAST
 Sequences created by SERIAL column definitions now auto-drop with the column
 Most forms of DROP now support RESTRICT and CASCADE options