-<REFENTRY ID="SQL-CREATEOPERATOR">
- <REFMETA>
- <REFENTRYTITLE>
- CREATE OPERATOR
- </REFENTRYTITLE>
- <REFMISCINFO>SQL - Language Statements</REFMISCINFO>
- </REFMETA>
- <REFNAMEDIV>
- <REFNAME>
- CREATE OPERATOR
- </REFNAME>
- <REFPURPOSE>
- Defines a new user operator
- </REFPURPOSE>
- </refnamediv>
- <REFSYNOPSISDIV>
- <REFSYNOPSISDIVINFO>
- <DATE>1998-09-09</DATE>
- </REFSYNOPSISDIVINFO>
- <SYNOPSIS>
+<!--
+$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_operator.sgml,v 1.39 2003/09/22 00:16:57 petere 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">func_name</replaceable>
- [, LEFTARG = <replaceable class="parameter">type1</replaceable> ]
- [, RIGHTARG = <replaceable class="parameter">type2</replaceable> ]
- [, COMMUTATOR = <replaceable class="parameter">com_op</replaceable> ]
- [, NEGATOR = <replaceable class="parameter">neg_op</replaceable> ]
- [, RESTRICT = <replaceable class="parameter">res_proc</replaceable> ]
- [, HASHES ]
- [, JOIN = <replaceable class="parameter">join_proc</replaceable> ]
- [, SORT = <replaceable class="parameter">sort_op</replaceable> [, ...] ]
- )
- </SYNOPSIS>
-
- <REFSECT2 ID="R2-SQL-CREATEOPERATOR-1">
- <REFSECT2INFO>
- <DATE>1998-09-09</DATE>
- </REFSECT2INFO>
- <TITLE>
- Inputs
- </TITLE>
- <PARA>
- </PARA>
- <VARIABLELIST>
- <VARLISTENTRY>
- <TERM>
- <replaceable class="parameter">name</replaceable>
- </TERM>
- <LISTITEM>
- <PARA>
- The operator to be defined. See below for allowable characters.
- </PARA>
- </LISTITEM>
- </VARLISTENTRY>
- <VARLISTENTRY>
- <TERM>
- <replaceable class="parameter">func_name</replaceable>
- </TERM>
- <LISTITEM>
- <PARA>
-The function used to implement this operator.
- </PARA>
- </LISTITEM>
- </VARLISTENTRY>
- <VARLISTENTRY>
- <TERM>
- <replaceable class="parameter">type1</replaceable>
- </TERM>
- <LISTITEM>
- <PARA>
-The type for the left-hand side of the operator, if any. This option would be
-omitted for a right-unary operator.
- </PARA>
- </LISTITEM>
- </VARLISTENTRY>
- <VARLISTENTRY>
- <TERM>
- <replaceable class="parameter">type2</replaceable>
- </TERM>
- <LISTITEM>
- <PARA>
-The type for the right-hand side of the operator, if any. This option would be
-omitted for a left-unary operator.
- </PARA>
- </LISTITEM>
- </VARLISTENTRY>
- <VARLISTENTRY>
- <TERM>
- <replaceable class="parameter">com_op</replaceable>
- </TERM>
- <LISTITEM>
- <PARA>
-The corresponding commutative operator.
- </PARA>
- </LISTITEM>
- </VARLISTENTRY>
- <VARLISTENTRY>
- <TERM>
- <replaceable class="parameter">neg_op</replaceable>
- </TERM>
- <LISTITEM>
- <PARA>
-The corresponding negation operator.
- </PARA>
- </LISTITEM>
- </VARLISTENTRY>
- <VARLISTENTRY>
- <TERM>
- <replaceable class="parameter">res_proc</replaceable>
- </TERM>
- <LISTITEM>
- <PARA>
-The corresponding restriction operator.
- </PARA>
- </LISTITEM>
- </VARLISTENTRY>
- <VARLISTENTRY>
- <TERM>
-HASHES
- </TERM>
- <LISTITEM>
- <PARA>
-This operator can support a hash-join algorithm.
- </PARA>
- </LISTITEM>
- </VARLISTENTRY>
- <VARLISTENTRY>
- <TERM>
- <replaceable class="parameter">join_proc</replaceable>
- </TERM>
- <LISTITEM>
- <PARA>
-Procedure supporting table joins.
- </PARA>
- </LISTITEM>
- </VARLISTENTRY>
- <VARLISTENTRY>
- <TERM>
- <replaceable class="parameter">sort_op</replaceable>
- </TERM>
- <LISTITEM>
- <PARA>
-Operator to use for sorting.
- </PARA>
- </LISTITEM>
- </VARLISTENTRY>
- </variablelist>
-
- </REFSECT2>
-
- <REFSECT2 ID="R2-SQL-CREATEOPERATOR-2">
- <REFSECT2INFO>
- <DATE>1998-09-09</DATE>
- </REFSECT2INFO>
- <TITLE>
- Outputs
- </TITLE>
- <PARA>
- <VARIABLELIST>
- <VARLISTENTRY>
- <TERM>
- <ReturnValue>CREATE</ReturnValue>
- </TERM>
- <LISTITEM>
- <PARA>
- Message returned if the operator is successfully created.
- </para>
- </listitem>
- </varlistentry>
- </VARIABLELIST>
- </para>
- </REFSECT2>
- </REFSYNOPSISDIV>
-
- <REFSECT1 ID="R1-SQL-CREATEOPERATOR-1">
- <REFSECT1INFO>
- <DATE>1998-09-09</DATE>
- </REFSECT1INFO>
- <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.
- </para>
- <para>
- The operator <replaceable class="parameter">name</replaceable>
- is a sequence of up to thirty two (32) characters in any combination
- from the following:
- <literallayout>
- + - * / < > = ~ ! @ # % ^ & | ` ? $ :
- </literallayout>
- <note>
- <para>
- No alphabetic characters are allowed in an operator name.
- This enables <productname>Postgres</productname> to parse SQL input
- into tokens without requiring spaces between each token.
- </para>
- </note>
- </para>
- <para>
- The operator "!=" is mapped to "<>" on input, so they are
- therefore equivalent.
- </para>
- <para>
- At least one of LEFTARG and RIGHTARG must be defined. For
- binary operators, both should be defined. For right unary
- operators, only LEFTARG should be defined, while for left
- unary operators only RIGHTARG should be defined.
- </para>
+ 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>
- Also, the
- <replaceable class="parameter">func_name</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).
+ <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 commutator operator is present so that
- <productname>Postgres</productname> can
- reverse the order of the operands if it wishes.
- For example, the operator area-less-than, <<<,
- would have a commutator
- operator, area-greater-than, >>>.
- Hence, the query optimizer could freely convert:
- <programlisting>
- "0,0,1,1"::box >>> MYBOXES.description
- </programlisting>
- to
- <programlisting>
- MYBOXES.description <<< "0,0,1,1"::box</programlisting>
+ 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>
- This allows the execution code to always use the latter
- representation and simplifies the query optimizer some
- what.
+ The operator <literal>!=</literal> is mapped to
+ <literal><></literal> on input, so these two names are always
+ equivalent.
</para>
+
<para>
- Suppose that an
- operator, area-equal, ===, exists, as well as an area not
- equal, !==.
- The negator operator allows the query optimizer to convert
- <programlisting>
- NOT MYBOXES.description === "0,0,1,1"::box
- </programlisting>
- to
- <programlisting>
- MYBOXES.description !== "0,0,1,1"::box
- </programlisting>
+ 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>
- If a commutator operator name is supplied,
- <productname>Postgres</productname>
- searches for it in the catalog. If it is found and it
- does not yet have a commutator itself, then the commutator's
- entry is updated to have the current (new) operator
- as its commutator. This applies to the negator, as well.
+ 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>
- This is to allow the definition of two operators that are
- the commutators or the negators of each other. The first
- operator should be defined without a commutator or negator
- (as appropriate). When the second operator is defined,
- name the first as the commutator or negator. The first
- will be updated as a side effect.
+ The other clauses specify optional operator optimization clauses.
+ Their meaning is detailed in <xref linkend="xoper">.
</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 type of the left-hand argument of the operator, 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 type of the right-hand argument of the operator, 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>
- The next two specifications are present to support the
- query optimizer in performing joins.
- <productname>Postgres</productname> can always
- evaluate a join (i.e., processing a clause with two tuple
- variables separated by an operator that returns a boolean)
- by iterative substitution [WONG76].
- In addition, <productname>Postgres</productname>
- is planning on implementing a hash-join algorithm along
- the lines of [SHAP86]; however, it must know whether this
- strategy is applicable.
- For example, a hash-join
- algorithm is usable for a clause of the form:
- <programlisting>
- MYBOXES.description === MYBOXES2.description
- </programlisting>
- but not for a clause of the form:
- <programlisting>
- MYBOXES.description <<< MYBOXES2.description.
- </programlisting>
- The HASHES flag gives the needed information to the query
- optimizer concerning whether a hash join strategy is
- usable for the operator in question.</para>
- <para>
- Similarly, the two sort operators indicate to the query
- optimizer whether merge-sort is a usable join strategy and
- what operators should be used to sort the two operand
- classes. For the === clause above, the optimizer must
- sort both relations using the operator, <<<. On the other
- hand, merge-sort is not usable with the clause:
- <programlisting>
- MYBOXES.description <<< MYBOXES2.description
- </programlisting>
+ 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>
- If other join strategies are found to be practical,
- <productname>Postgres</productname>
- will change the optimizer and run-time system to use
- them and will require additional specification when an
- operator is defined. Fortunately, the research community
- invents new join strategies infrequently, and the added
- generality of user-defined join strategies was not felt to
- be worth the complexity involved.
+ Refer to <xref linkend="xoper"> for further information.
</para>
+
<para>
- The last two pieces of the specification are present so
- the query optimizer can estimate result sizes. If a
- clause of the form:
- <programlisting>
- MYBOXES.description <<< "0,0,1,1"::box
- </programlisting>
- is present in the qualification,
- then <productname>Postgres</productname> may have to
- estimate the fraction of the instances in MYBOXES that
- satisfy the clause. The function
- <replaceable class="parameter">res_proc</replaceable>
- must be a registered function (meaning it is already defined using
- define function(l)) which accepts one argument of the correct
- data type and returns a floating point number. The
- query optimizer simply calls this function, passing the
- parameter "0,0,1,1" and multiplies the result by the relation
- size to get the desired expected number of instances.
+ Use <command>DROP OPERATOR</command> to delete user-defined
+ operators from a database.
</para>
+ </refsect1>
+
+ <refsect1>
+ <title>Examples</title>
+
<para>
- Similarly, when the operands of the operator both contain
- instance variables, the query optimizer must estimate the
- size of the resulting join. The function join_proc will
- return another floating point number which will be multiplied
- by the cardinalities of the two classes involved to
- compute the desired expected result size.
+ 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>
- <para>
- The difference between the function
- <programlisting>
- my_procedure_1 (MYBOXES.description, "0,0,1,1"::box)
- </programlisting>
- and the operator
- <programlisting>
- MYBOXES.description === "0,0,1,1"::box
- </programlisting>
- is that <productname>Postgres</productname>
- attempts to optimize operators and can
- decide to use an index to restrict the search space when
- operators are involved. However, there is no attempt to
- optimize functions, and they are performed by brute force.
- Moreover, functions can have any number of arguments while
- operators are restricted to one or two.
- </PARA>
-
- <REFSECT2 ID="R2-SQL-CREATEOPERATOR-3">
- <REFSECT2INFO>
- <DATE>1998-09-09</DATE>
- </REFSECT2INFO>
- <TITLE>
- Notes
- </TITLE>
- <PARA>
- Refer to the chapter on operators in the
- <citetitle>PostgreSQL User's Guide</citetitle>
- for further information.
- Refer to <command>DROP OPERATOR</command> to delete
- user-defined operators from a database.
- </para>
- </REFSECT2>
</refsect1>
-
- <REFSECT1 ID="R1-SQL-CREATEOPERATOR-2">
- <TITLE>
- Usage
- </TITLE>
- <PARA>The following command defines a new operator,
- area-equality, for the BOX data type.
- </PARA>
- <ProgramListing>
- CREATE OPERATOR === (
- LEFTARG = box,
- RIGHTARG = box,
- PROCEDURE = area_equal_procedure,
- COMMUTATOR = ===,
- NEGATOR = !==,
- RESTRICT = area_restriction_procedure,
- HASHES,
- JOIN = area-join-procedure,
- SORT = <<<, <<<)
- </ProgramListing>
- </REFSECT1>
- <REFSECT1 ID="R1-SQL-CREATEOPERATOR-3">
- <TITLE>
- Compatibility
- </TITLE>
- <PARA>
- CREATE OPERATOR is a <productname>Postgres</productname> extension.
- </PARA>
-
- <REFSECT2 ID="R2-SQL-CREATEOPERATOR-4">
- <REFSECT2INFO>
- <DATE>1998-09-09</DATE>
- </REFSECT2INFO>
- <TITLE>
- SQL92
- </TITLE>
- <PARA>
- There is no CREATE OPERATOR statement in <acronym>SQL92</acronym>.
- </PARA>
- </refsect2>
+ <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>
-</REFENTRY>
+</refentry>
<!-- Keep this comment at the end of the file
Local variables:
mode: sgml
-sgml-omittag:t
+sgml-omittag:nil
sgml-shorttag:t
sgml-minimize-attributes:nil
sgml-always-quote-attributes:t
projects / postgresql / blobdiff