| <!-- |
| $PostgreSQL: pgsql/doc/src/sgml/ref/create_opclass.sgml,v 1.18 2006/10/16 17:28:03 momjian 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</refpurpose> |
| </refnamediv> |
| |
| <indexterm zone="sql-createopclass"> |
| <primary>CREATE OPERATOR CLASS</primary> |
| </indexterm> |
| |
| <refsynopsisdiv> |
| <synopsis> |
| CREATE OPERATOR CLASS <replaceable class="parameter">name</replaceable> [ DEFAULT ] FOR TYPE <replaceable class="parameter">data_type</replaceable> USING <replaceable class="parameter">index_method</replaceable> AS |
| { 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> ) ] [ RECHECK ] |
| | FUNCTION <replaceable class="parameter">support_number</replaceable> <replaceable class="parameter">funcname</replaceable> ( <replaceable class="parameter">argument_type</replaceable> [, ...] ) |
| | STORAGE <replaceable class="parameter">storage_type</replaceable> |
| } [, ... ] |
| </synopsis> |
| </refsynopsisdiv> |
| |
| <refsect1> |
| <title>Description</title> |
| |
| <para> |
| <command>CREATE OPERATOR CLASS</command> creates a new operator class. |
| An operator class defines how a particular data type can be used with |
| an index. The operator class specifies that certain operators will fill |
| particular roles or <quote>strategies</> for this data type and this |
| index method. The operator class also specifies the support procedures to |
| be used by |
| the index 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. |
| Two operator classes in the same schema can have the same name only if they |
| are for different index methods. |
| </para> |
| |
| <para> |
| The user who defines an operator class becomes its owner. Presently, |
| the creating user must be a superuser. (This restriction is made because |
| an erroneous operator class definition could confuse or even crash the |
| server.) |
| </para> |
| |
| <para> |
| <command>CREATE OPERATOR CLASS</command> does not presently check |
| whether the operator class 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 class. |
| </para> |
| |
| <para> |
| Refer to <xref linkend="xindex"> for further information. |
| </para> |
| </refsect1> |
| |
| <refsect1> |
| <title>Parameters</title> |
| |
| <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 |
| operator class for its data type. At most one operator class |
| can be the default for a specific data type and index method. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="parameter">data_type</replaceable></term> |
| <listitem> |
| <para> |
| The column data type that this operator class is for. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="parameter">index_method</replaceable></term> |
| <listitem> |
| <para> |
| The name of the index method this operator class is for. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="parameter">strategy_number</replaceable></term> |
| <listitem> |
| <para> |
| The index method's strategy number for an operator |
| associated with the operator class. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="parameter">operator_name</replaceable></term> |
| <listitem> |
| <para> |
| The name (optionally schema-qualified) of an operator associated |
| with the operator class. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="parameter">op_type</replaceable></term> |
| <listitem> |
| <para> |
| The operand data type(s) of an operator, or <literal>NONE</> to |
| signify a left-unary or right-unary operator. The operand data |
| types may be omitted in the normal case where they are the same |
| as the operator class's data type. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>RECHECK</></term> |
| <listitem> |
| <para> |
| If present, the index is <quote>lossy</> for this operator, and |
| so the rows 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 method's support procedure number for a |
| function associated with the operator class. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="parameter">funcname</replaceable></term> |
| <listitem> |
| <para> |
| The name (optionally schema-qualified) of a function that is an |
| index method support procedure for the operator class. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="parameter">argument_types</replaceable></term> |
| <listitem> |
| <para> |
| The parameter data type(s) of the function. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="parameter">storage_type</replaceable></term> |
| <listitem> |
| <para> |
| The data type actually stored in the index. Normally this is |
| the same as the column data type, but some index methods |
| (GIN and GiST for now) allow it to be different. The |
| <literal>STORAGE</> clause must be omitted unless the index |
| method allows a different type to be used. |
| </para> |
| </listitem> |
| </varlistentry> |
| </variablelist> |
| |
| <para> |
| The <literal>OPERATOR</>, <literal>FUNCTION</>, and <literal>STORAGE</> |
| clauses may appear in any order. |
| </para> |
| </refsect1> |
| |
| <refsect1> |
| <title>Notes</title> |
| |
| <para> |
| Because the index machinery does not check access permissions on functions |
| before using them, including a function or operator in an operator class |
| 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 |
| class. |
| </para> |
| |
| <para> |
| The operators should not be defined by SQL functions. A 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 defines a GiST index operator class |
| for the data type <literal>_int4</> (array of <type>int4</type>). 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 (internal, _int4, int4), |
| FUNCTION 2 g_int_union (bytea, internal), |
| FUNCTION 3 g_int_compress (internal), |
| FUNCTION 4 g_int_decompress (internal), |
| FUNCTION 5 g_int_penalty (internal, internal, internal), |
| FUNCTION 6 g_int_picksplit (internal, internal), |
| FUNCTION 7 g_int_same (_int4, _int4, internal); |
| </programlisting> |
| </refsect1> |
| |
| <refsect1> |
| <title>Compatibility</title> |
| |
| <para> |
| <command>CREATE OPERATOR CLASS</command> is a |
| <productname>PostgreSQL</productname> extension. There is no |
| <command>CREATE OPERATOR CLASS</command> statement in the SQL |
| standard. |
| </para> |
| </refsect1> |
| |
| <refsect1> |
| <title>See Also</title> |
| |
| <simplelist type="inline"> |
| <member><xref linkend="sql-alteropclass" endterm="sql-alteropclass-title"></member> |
| <member><xref linkend="sql-dropopclass" endterm="sql-dropopclass-title"></member> |
| </simplelist> |
| </refsect1> |
| </refentry> |