<!-- $PostgreSQL: pgsql/doc/src/sgml/ref/create_operator.sgml,v 1.44 2005/01/04 00:39:53 tgl 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> <!-- 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: -->