<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"> <HTML ><HEAD ><TITLE >CREATE OPERATOR</TITLE ><META NAME="GENERATOR" CONTENT="Modular DocBook HTML Stylesheet Version 1.79"><LINK REV="MADE" HREF="mailto:pgsql-docs@postgresql.org"><LINK REL="HOME" TITLE="PostgreSQL 8.0.11 Documentation" HREF="index.html"><LINK REL="UP" TITLE="SQL Commands" HREF="sql-commands.html"><LINK REL="PREVIOUS" TITLE="CREATE LANGUAGE" HREF="sql-createlanguage.html"><LINK REL="NEXT" TITLE="CREATE OPERATOR CLASS" HREF="sql-createopclass.html"><LINK REL="STYLESHEET" TYPE="text/css" HREF="stylesheet.css"><META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=ISO-8859-1"><META NAME="creation" CONTENT="2007-02-02T03:57:22"></HEAD ><BODY CLASS="REFENTRY" ><DIV CLASS="NAVHEADER" ><TABLE SUMMARY="Header navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TH COLSPAN="5" ALIGN="center" VALIGN="bottom" >PostgreSQL 8.0.11 Documentation</TH ></TR ><TR ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="sql-createlanguage.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="10%" ALIGN="left" VALIGN="top" ><A HREF="sql-createlanguage.html" >Fast Backward</A ></TD ><TD WIDTH="60%" ALIGN="center" VALIGN="bottom" ></TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A HREF="sql-createopclass.html" >Fast Forward</A ></TD ><TD WIDTH="10%" ALIGN="right" VALIGN="top" ><A HREF="sql-createopclass.html" ACCESSKEY="N" >Next</A ></TD ></TR ></TABLE ><HR ALIGN="LEFT" WIDTH="100%"></DIV ><H1 ><A NAME="SQL-CREATEOPERATOR" ></A >CREATE OPERATOR</H1 ><DIV CLASS="REFNAMEDIV" ><A NAME="AEN39711" ></A ><H2 >Name</H2 >CREATE OPERATOR -- define a new operator</DIV ><A NAME="AEN39714" ></A ><DIV CLASS="REFSYNOPSISDIV" ><A NAME="AEN39716" ></A ><H2 >Synopsis</H2 ><PRE CLASS="SYNOPSIS" >CREATE OPERATOR <TT CLASS="REPLACEABLE" ><I >name</I ></TT > ( PROCEDURE = <TT CLASS="REPLACEABLE" ><I >funcname</I ></TT > [, LEFTARG = <TT CLASS="REPLACEABLE" ><I >lefttype</I ></TT > ] [, RIGHTARG = <TT CLASS="REPLACEABLE" ><I >righttype</I ></TT > ] [, COMMUTATOR = <TT CLASS="REPLACEABLE" ><I >com_op</I ></TT > ] [, NEGATOR = <TT CLASS="REPLACEABLE" ><I >neg_op</I ></TT > ] [, RESTRICT = <TT CLASS="REPLACEABLE" ><I >res_proc</I ></TT > ] [, JOIN = <TT CLASS="REPLACEABLE" ><I >join_proc</I ></TT > ] [, HASHES ] [, MERGES ] [, SORT1 = <TT CLASS="REPLACEABLE" ><I >left_sort_op</I ></TT > ] [, SORT2 = <TT CLASS="REPLACEABLE" ><I >right_sort_op</I ></TT > ] [, LTCMP = <TT CLASS="REPLACEABLE" ><I >less_than_op</I ></TT > ] [, GTCMP = <TT CLASS="REPLACEABLE" ><I >greater_than_op</I ></TT > ] )</PRE ></DIV ><DIV CLASS="REFSECT1" ><A NAME="AEN39730" ></A ><H2 >Description</H2 ><P > <TT CLASS="COMMAND" >CREATE OPERATOR</TT > defines a new operator, <TT CLASS="REPLACEABLE" ><I >name</I ></TT >. 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. </P ><P > The operator name is a sequence of up to <TT CLASS="SYMBOL" >NAMEDATALEN</TT >-1 (63 by default) characters from the following list: <P CLASS="LITERALLAYOUT" >+ - * / < > = ~ ! @ # % ^ & | ` ?</P > There are a few restrictions on your choice of name: <P ></P ></P><UL ><LI ><P > <TT CLASS="LITERAL" >--</TT > and <TT CLASS="LITERAL" >/*</TT > cannot appear anywhere in an operator name, since they will be taken as the start of a comment. </P ></LI ><LI ><P > A multicharacter operator name cannot end in <TT CLASS="LITERAL" >+</TT > or <TT CLASS="LITERAL" >-</TT >, unless the name also contains at least one of these characters: <P CLASS="LITERALLAYOUT" >~ ! @ # % ^ & | ` ?</P > For example, <TT CLASS="LITERAL" >@-</TT > is an allowed operator name, but <TT CLASS="LITERAL" >*-</TT > is not. This restriction allows <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > to parse SQL-compliant commands without requiring spaces between tokens. </P ></LI ></UL ><P> </P ><P > The operator <TT CLASS="LITERAL" >!=</TT > is mapped to <TT CLASS="LITERAL" ><></TT > on input, so these two names are always equivalent. </P ><P > At least one of <TT CLASS="LITERAL" >LEFTARG</TT > and <TT CLASS="LITERAL" >RIGHTARG</TT > must be defined. For binary operators, both must be defined. For right unary operators, only <TT CLASS="LITERAL" >LEFTARG</TT > should be defined, while for left unary operators only <TT CLASS="LITERAL" >RIGHTARG</TT > should be defined. </P ><P > The <TT CLASS="REPLACEABLE" ><I >funcname</I ></TT > procedure must have been previously defined using <TT CLASS="COMMAND" >CREATE FUNCTION</TT > and must be defined to accept the correct number of arguments (either one or two) of the indicated types. </P ><P > The other clauses specify optional operator optimization clauses. Their meaning is detailed in <A HREF="xoper-optimization.html" >Section 31.13</A >. </P ></DIV ><DIV CLASS="REFSECT1" ><A NAME="AEN39764" ></A ><H2 >Parameters</H2 ><P ></P ><DIV CLASS="VARIABLELIST" ><DL ><DT ><TT CLASS="REPLACEABLE" ><I >name</I ></TT ></DT ><DD ><P > The name of the operator to be defined. See above for allowable characters. The name may be schema-qualified, for example <TT CLASS="LITERAL" >CREATE OPERATOR myschema.+ (...)</TT >. 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 <I CLASS="FIRSTTERM" >overloading</I >. </P ></DD ><DT ><TT CLASS="REPLACEABLE" ><I >funcname</I ></TT ></DT ><DD ><P > The function used to implement this operator. </P ></DD ><DT ><TT CLASS="REPLACEABLE" ><I >lefttype</I ></TT ></DT ><DD ><P > The data type of the operator's left operand, if any. This option would be omitted for a left-unary operator. </P ></DD ><DT ><TT CLASS="REPLACEABLE" ><I >righttype</I ></TT ></DT ><DD ><P > The data type of the operator's right operand, if any. This option would be omitted for a right-unary operator. </P ></DD ><DT ><TT CLASS="REPLACEABLE" ><I >com_op</I ></TT ></DT ><DD ><P > The commutator of this operator. </P ></DD ><DT ><TT CLASS="REPLACEABLE" ><I >neg_op</I ></TT ></DT ><DD ><P > The negator of this operator. </P ></DD ><DT ><TT CLASS="REPLACEABLE" ><I >res_proc</I ></TT ></DT ><DD ><P > The restriction selectivity estimator function for this operator. </P ></DD ><DT ><TT CLASS="REPLACEABLE" ><I >join_proc</I ></TT ></DT ><DD ><P > The join selectivity estimator function for this operator. </P ></DD ><DT ><TT CLASS="LITERAL" >HASHES</TT ></DT ><DD ><P > Indicates this operator can support a hash join. </P ></DD ><DT ><TT CLASS="LITERAL" >MERGES</TT ></DT ><DD ><P > Indicates this operator can support a merge join. </P ></DD ><DT ><TT CLASS="REPLACEABLE" ><I >left_sort_op</I ></TT ></DT ><DD ><P > If this operator can support a merge join, the less-than operator that sorts the left-hand data type of this operator. </P ></DD ><DT ><TT CLASS="REPLACEABLE" ><I >right_sort_op</I ></TT ></DT ><DD ><P > If this operator can support a merge join, the less-than operator that sorts the right-hand data type of this operator. </P ></DD ><DT ><TT CLASS="REPLACEABLE" ><I >less_than_op</I ></TT ></DT ><DD ><P > If this operator can support a merge join, the less-than operator that compares the input data types of this operator. </P ></DD ><DT ><TT CLASS="REPLACEABLE" ><I >greater_than_op</I ></TT ></DT ><DD ><P > If this operator can support a merge join, the greater-than operator that compares the input data types of this operator. </P ></DD ></DL ></DIV ><P > To give a schema-qualified operator name in <TT CLASS="REPLACEABLE" ><I >com_op</I ></TT > or the other optional arguments, use the <TT CLASS="LITERAL" >OPERATOR()</TT > syntax, for example </P><PRE CLASS="PROGRAMLISTING" >COMMUTATOR = OPERATOR(myschema.===) ,</PRE ><P> </P ></DIV ><DIV CLASS="REFSECT1" ><A NAME="AEN39843" ></A ><H2 >Notes</H2 ><P > Refer to <A HREF="xoper.html" >Section 31.12</A > for further information. </P ><P > Use <A HREF="sql-dropoperator.html" ><I >DROP OPERATOR</I ></A > to delete user-defined operators from a database. Use <A HREF="sql-alteroperator.html" ><I >ALTER OPERATOR</I ></A > to modify operators in a database. </P ></DIV ><DIV CLASS="REFSECT1" ><A NAME="AEN39850" ></A ><H2 >Examples</H2 ><P > The following command defines a new operator, area-equality, for the data type <TT CLASS="TYPE" >box</TT >: </P><PRE CLASS="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 );</PRE ><P> </P ></DIV ><DIV CLASS="REFSECT1" ><A NAME="AEN39855" ></A ><H2 >Compatibility</H2 ><P > <TT CLASS="COMMAND" >CREATE OPERATOR</TT > is a <SPAN CLASS="PRODUCTNAME" >PostgreSQL</SPAN > extension. There are no provisions for user-defined operators in the SQL standard. </P ></DIV ><DIV CLASS="REFSECT1" ><A NAME="AEN39860" ></A ><H2 >See Also</H2 ><A HREF="sql-alteroperator.html" ><I >ALTER OPERATOR</I ></A >, <A HREF="sql-createopclass.html" ><I >CREATE OPERATOR CLASS</I ></A >, <A HREF="sql-dropoperator.html" ><I >DROP OPERATOR</I ></A ></DIV ><DIV CLASS="NAVFOOTER" ><HR ALIGN="LEFT" WIDTH="100%"><TABLE SUMMARY="Footer navigation table" WIDTH="100%" BORDER="0" CELLPADDING="0" CELLSPACING="0" ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" ><A HREF="sql-createlanguage.html" ACCESSKEY="P" >Prev</A ></TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="index.html" ACCESSKEY="H" >Home</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" ><A HREF="sql-createopclass.html" ACCESSKEY="N" >Next</A ></TD ></TR ><TR ><TD WIDTH="33%" ALIGN="left" VALIGN="top" >CREATE LANGUAGE</TD ><TD WIDTH="34%" ALIGN="center" VALIGN="top" ><A HREF="sql-commands.html" ACCESSKEY="U" >Up</A ></TD ><TD WIDTH="33%" ALIGN="right" VALIGN="top" >CREATE OPERATOR CLASS</TD ></TR ></TABLE ></DIV ></BODY ></HTML >