| <!-- |
| $PostgreSQL: pgsql/doc/src/sgml/ref/create_operator.sgml,v 1.45 2006/09/16 00:30:17 momjian Exp $ |
| PostgreSQL documentation |
| --> |
| |
| <refentry id="SQL-CREATEOPERATOR"> |
| <refmeta> |
| <refentrytitle id="sql-createoperator-title">CREATE OPERATOR</refentrytitle> |
| <refmiscinfo>SQL - Language Statements</refmiscinfo> |
| </refmeta> |
| |
| <refnamediv> |
| <refname>CREATE OPERATOR</refname> |
| <refpurpose>define a new operator</refpurpose> |
| </refnamediv> |
| |
| <indexterm zone="sql-createoperator"> |
| <primary>CREATE OPERATOR</primary> |
| </indexterm> |
| |
| <refsynopsisdiv> |
| <synopsis> |
| CREATE OPERATOR <replaceable>name</replaceable> ( |
| PROCEDURE = <replaceable class="parameter">funcname</replaceable> |
| [, LEFTARG = <replaceable class="parameter">lefttype</replaceable> ] [, RIGHTARG = <replaceable class="parameter">righttype</replaceable> ] |
| [, COMMUTATOR = <replaceable class="parameter">com_op</replaceable> ] [, NEGATOR = <replaceable class="parameter">neg_op</replaceable> ] |
| [, RESTRICT = <replaceable class="parameter">res_proc</replaceable> ] [, JOIN = <replaceable class="parameter">join_proc</replaceable> ] |
| [, HASHES ] [, MERGES ] |
| [, SORT1 = <replaceable class="parameter">left_sort_op</replaceable> ] [, SORT2 = <replaceable class="parameter">right_sort_op</replaceable> ] |
| [, LTCMP = <replaceable class="parameter">less_than_op</replaceable> ] [, GTCMP = <replaceable class="parameter">greater_than_op</replaceable> ] |
| ) |
| </synopsis> |
| </refsynopsisdiv> |
| |
| <refsect1> |
| <title>Description</title> |
| |
| <para> |
| <command>CREATE OPERATOR</command> defines a new operator, |
| <replaceable class="parameter">name</replaceable>. The user who |
| defines an operator becomes its owner. If a schema name is given |
| then the operator is created in the specified schema. Otherwise it |
| is created in the current schema. |
| </para> |
| |
| <para> |
| The operator name is a sequence of up to <symbol>NAMEDATALEN</>-1 |
| (63 by default) characters from the following list: |
| <literallayout> |
| + - * / < > = ~ ! @ # % ^ & | ` ? |
| </literallayout> |
| |
| There are a few restrictions on your choice of name: |
| <itemizedlist> |
| <listitem> |
| <para> |
| <literal>--</literal> and <literal>/*</literal> cannot appear anywhere in an operator name, |
| since they will be taken as the start of a comment. |
| </para> |
| </listitem> |
| <listitem> |
| <para> |
| A multicharacter operator name cannot end in <literal>+</literal> or |
| <literal>-</literal>, |
| unless the name also contains at least one of these characters: |
| <literallayout> |
| ~ ! @ # % ^ & | ` ? |
| </literallayout> |
| For example, <literal>@-</literal> is an allowed operator name, |
| but <literal>*-</literal> is not. |
| This restriction allows <productname>PostgreSQL</productname> to |
| parse SQL-compliant commands without requiring spaces between tokens. |
| </para> |
| </listitem> |
| </itemizedlist> |
| </para> |
| |
| <para> |
| The operator <literal>!=</literal> is mapped to |
| <literal><></literal> on input, so these two names are always |
| equivalent. |
| </para> |
| |
| <para> |
| At least one of <literal>LEFTARG</> and <literal>RIGHTARG</> must be defined. For |
| binary operators, both must be defined. For right unary |
| operators, only <literal>LEFTARG</> should be defined, while for left |
| unary operators only <literal>RIGHTARG</> should be defined. |
| </para> |
| |
| <para> |
| The <replaceable class="parameter">funcname</replaceable> |
| procedure must have been previously defined using <command>CREATE |
| FUNCTION</command> and must be defined to accept the correct number |
| of arguments (either one or two) of the indicated types. |
| </para> |
| |
| <para> |
| The other clauses specify optional operator optimization clauses. |
| Their meaning is detailed in <xref linkend="xoper-optimization">. |
| </para> |
| </refsect1> |
| |
| <refsect1> |
| <title>Parameters</title> |
| |
| <variablelist> |
| <varlistentry> |
| <term><replaceable class="parameter">name</replaceable></term> |
| <listitem> |
| <para> |
| The name of the operator to be defined. See above for allowable |
| characters. The name may be schema-qualified, for example |
| <literal>CREATE OPERATOR myschema.+ (...)</>. If not, then |
| the operator is created in the current schema. Two operators |
| in the same schema can have the same name if they operate on |
| different data types. This is called |
| <firstterm>overloading</>. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="parameter">funcname</replaceable></term> |
| <listitem> |
| <para> |
| The function used to implement this operator. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="parameter">lefttype</replaceable></term> |
| <listitem> |
| <para> |
| The data type of the operator's left operand, if any. |
| This option would be omitted for a left-unary operator. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="parameter">righttype</replaceable></term> |
| <listitem> |
| <para> |
| The data type of the operator's right operand, if any. |
| This option would be omitted for a right-unary operator. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="parameter">com_op</replaceable></term> |
| <listitem> |
| <para> |
| The commutator of this operator. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="parameter">neg_op</replaceable></term> |
| <listitem> |
| <para> |
| The negator of this operator. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="parameter">res_proc</replaceable></term> |
| <listitem> |
| <para> |
| The restriction selectivity estimator function for this operator. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="parameter">join_proc</replaceable></term> |
| <listitem> |
| <para> |
| The join selectivity estimator function for this operator. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>HASHES</literal></term> |
| <listitem> |
| <para> |
| Indicates this operator can support a hash join. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><literal>MERGES</literal></term> |
| <listitem> |
| <para> |
| Indicates this operator can support a merge join. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="parameter">left_sort_op</replaceable></term> |
| <listitem> |
| <para> |
| If this operator can support a merge join, the less-than |
| operator that sorts the left-hand data type of this operator. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="parameter">right_sort_op</replaceable></term> |
| <listitem> |
| <para> |
| If this operator can support a merge join, the less-than |
| operator that sorts the right-hand data type of this operator. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="parameter">less_than_op</replaceable></term> |
| <listitem> |
| <para> |
| If this operator can support a merge join, the less-than |
| operator that compares the input data types of this operator. |
| </para> |
| </listitem> |
| </varlistentry> |
| |
| <varlistentry> |
| <term><replaceable class="parameter">greater_than_op</replaceable></term> |
| <listitem> |
| <para> |
| If this operator can support a merge join, the greater-than |
| operator that compares the input data types of this operator. |
| </para> |
| </listitem> |
| </varlistentry> |
| </variablelist> |
| |
| <para> |
| To give a schema-qualified operator name in <replaceable |
| class="parameter">com_op</replaceable> or the other optional |
| arguments, use the <literal>OPERATOR()</> syntax, for example |
| <programlisting> |
| COMMUTATOR = OPERATOR(myschema.===) , |
| </programlisting> |
| </para> |
| </refsect1> |
| |
| <refsect1> |
| <title>Notes</title> |
| |
| <para> |
| Refer to <xref linkend="xoper"> for further information. |
| </para> |
| |
| <para> |
| Use <xref linkend="sql-dropoperator" |
| endterm="sql-dropoperator-title"> to delete user-defined operators |
| from a database. Use <xref linkend="sql-alteroperator" |
| endterm="sql-alteroperator-title"> to modify operators in a |
| database. |
| </para> |
| </refsect1> |
| |
| <refsect1> |
| <title>Examples</title> |
| |
| <para> |
| The following command defines a new operator, area-equality, for |
| the data type <type>box</type>: |
| <programlisting> |
| CREATE OPERATOR === ( |
| LEFTARG = box, |
| RIGHTARG = box, |
| PROCEDURE = area_equal_procedure, |
| COMMUTATOR = ===, |
| NEGATOR = !==, |
| RESTRICT = area_restriction_procedure, |
| JOIN = area_join_procedure, |
| HASHES, |
| SORT1 = <<<, |
| SORT2 = <<< |
| -- Since sort operators were given, MERGES is implied. |
| -- LTCMP and GTCMP are assumed to be < and > respectively |
| ); |
| </programlisting> |
| </para> |
| </refsect1> |
| |
| <refsect1> |
| <title>Compatibility</title> |
| |
| <para> |
| <command>CREATE OPERATOR</command> is a |
| <productname>PostgreSQL</productname> extension. There are no |
| provisions for user-defined operators in the SQL standard. |
| </para> |
| </refsect1> |
| |
| <refsect1> |
| <title>See Also</title> |
| |
| <simplelist type="inline"> |
| <member><xref linkend="sql-alteroperator" endterm="sql-alteroperator-title"></member> |
| <member><xref linkend="sql-createopclass" endterm="sql-createopclass-title"></member> |
| <member><xref linkend="sql-dropoperator" endterm="sql-dropoperator-title"></member> |
| </simplelist> |
| </refsect1> |
| </refentry> |