diff options
Diffstat (limited to 'doc/src/sgml/ref/create_operator.sgml')
| -rw-r--r-- | doc/src/sgml/ref/create_operator.sgml | 416 |
1 files changed, 416 insertions, 0 deletions
diff --git a/doc/src/sgml/ref/create_operator.sgml b/doc/src/sgml/ref/create_operator.sgml new file mode 100644 index 00000000000..a9c25a02692 --- /dev/null +++ b/doc/src/sgml/ref/create_operator.sgml @@ -0,0 +1,416 @@ +<REFENTRY ID="SQL-CREATEOPERATOR-1"> + <REFMETA> + <REFENTRYTITLE> + CREATE OPERATOR + </REFENTRYTITLE> + <REFMISCINFO>SQL - Language Statements</REFMISCINFO> + </REFMETA> + <REFNAMEDIV> + <REFNAME> + CREATE OPERATOR + </REFNAME> + <REFPURPOSE> + Defines a new user operator. + </REFPURPOSE> + + <REFSYNOPSISDIV> + <REFSYNOPSISDIVINFO> + <DATE>1998-04-15</DATE> + </REFSYNOPSISDIVINFO> + <SYNOPSIS> + CREATE OPERATOR <replaceable>name</replaceable> + ([ LEFTARG = <replaceable class="parameter">type1</replaceable> ] + [, RIGHTARG = <replaceable class="parameter">type2</replaceable> ] + , PROCEDURE = <replaceable class="parameter">func_name</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-04-15</DATE> + </REFSECT2INFO> + <TITLE> + Inputs + </TITLE> + <PARA> + </PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + </TERM> + <LISTITEM> + <PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + <replaceable class="parameter">name</replaceable> + </TERM> + <LISTITEM> + <PARA> + The name of an existing aggregate function. + </PARA> + </LISTITEM> + </VARLISTENTRY> + <VARLISTENTRY> + <TERM> + <replaceable class="parameter">type1</replaceable> + </TERM> + <LISTITEM> + <PARA> + </PARA> + </LISTITEM> + </VARLISTENTRY> + <VARLISTENTRY> + <TERM> + <replaceable class="parameter">type2</replaceable> + </TERM> + <LISTITEM> + <PARA> + </PARA> + </LISTITEM> + </VARLISTENTRY> + <VARLISTENTRY> + <TERM> + <replaceable class="parameter">func_name</replaceable> + </TERM> + <LISTITEM> + <PARA> + </PARA> + </LISTITEM> + </VARLISTENTRY> + <VARLISTENTRY> + <TERM> + <replaceable class="parameter">com_op</replaceable> + </TERM> + <LISTITEM> + <PARA> + </PARA> + </LISTITEM> + </VARLISTENTRY> + <VARLISTENTRY> + <TERM> + <replaceable class="parameter">neg_op</replaceable> + </TERM> + <LISTITEM> + <PARA> + </PARA> + </LISTITEM> + </VARLISTENTRY> + <VARLISTENTRY> + <TERM> + <replaceable class="parameter">res_proc</replaceable> + </TERM> + <LISTITEM> + <PARA> + </PARA> + </LISTITEM> + </VARLISTENTRY> + <VARLISTENTRY> + <TERM> + <replaceable class="parameter">join_proc</replaceable> + </TERM> + <LISTITEM> + <PARA> + </PARA> + </LISTITEM> + </VARLISTENTRY> + <VARLISTENTRY> + <TERM> + <replaceable class="parameter">sort_op</replaceable> + </TERM> + <LISTITEM> + <PARA> + </PARA> + </LISTITEM> + </VARLISTENTRY> + </variablelist> + </LISTITEM> + </VARLISTENTRY> + </VARIABLELIST> + </REFSECT2> + + <REFSECT2 ID="R2-SQL-CREATEOPERATOR-2"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + Outputs + </TITLE> + <PARA> + </PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + </TERM> + <LISTITEM> + <PARA> + <VARIABLELIST> + <VARLISTENTRY> + <TERM> + <ReturnValue>CREATE</ReturnValue> + </TERM> + <LISTITEM> + <PARA> + Message returned if the operator is successfully created. + </PARA> + </LISTITEM> + </VARLISTENTRY> + </variablelist> + </LISTITEM> + </VARLISTENTRY> + </VARIABLELIST> + </REFSECT2> + </REFSYNOPSISDIV> + + <REFSECT1 ID="R1-SQL-CREATEOPERATOR-1"> + <REFSECT1INFO> + <DATE>1998-04-15</DATE> + </REFSECT1INFO> + <TITLE> + Description + </TITLE> + <PARA> + This command defines a new user operator, operator_name. + The user who defines an operator becomes its owner. + </para> + <para> + The operator_name is a sequence of up to sixteen punctua + tion characters. The following characters are valid for + single-character operator names:<literallayout> + + ~ ! @ # % ^ & ` ? </literallayout> + </para> + <para> + If the operator name is more than one character long, it + may consist of any combination of the above characters or + the following additional characters:<literallayout> + + | $ : + - * / < > =</literallayout> + </para> + <para> + The operator "!=" is mapped to "<>" on input, and 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 arg1 should be defined, while for left + unary operators only arg2 should be defined. + </para> + <para> + The name of the operator, operator_name, can be composed + of symbols only. Also, the func_name procedure must have + been previously defined using create function(l) and must + have one or two arguments. + </para> + <para> + The commutator operator is present so that Postgres can + reverse the order of the operands if it wishes. For exam + ple, the operator area-less-than, >>>, would have a commu + tator operator, area-greater-than, <<<. Suppose that an + operator, area-equal, ===, exists, as well as an area not + equal, !==. Hence, the query optimizer could freely con + vert: + <programlisting> + "0,0,1,1"::box >>> MYBOXES.description + </programlisting> + to + <programlisting> + MYBOXES.description <<< "0,0,1,1"::box</programlisting> + </para> + <para> + This allows the execution code to always use the latter + representation and simplifies the query optimizer some + what. + </para> + <para> + 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> + </para> + <para> + If a commutator operator name is supplied, Postgres + 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. + </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. + </para> + <para> + The next two specifications are present to support the + query optimizer in performing joins. Postgres 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, Postgres + 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> + </para> + <para> + If other join strategies are found to be practical, Post + gres 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. + </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 Postgres may have to + estimate the fraction of the instances in MYBOXES that + satisfy the clause. The function res_proc must be a reg + istered 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. + </para> + <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. + </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 Postgres 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-04-15</DATE> + </REFSECT2INFO> + <TITLE> + Notes + </TITLE> + <PARA> + Refer to <citetitle>PostgreSQL User's Guide</citetitle> chapter 5 + <comment> + This reference must be corrected. + </comment> + for further information. + Refer to DROP OPERATOR statement to drop operators. + + </REFSECT2> + + <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 PostgreSQL extension of SQL. + </PARA> + + <REFSECT2 ID="R2-SQL-CREATEOPERATOR-4"> + <REFSECT2INFO> + <DATE>1998-04-15</DATE> + </REFSECT2INFO> + <TITLE> + SQL92 + </TITLE> + <PARA> + There is no CREATE OPERATOR statement on SQL92. + </PARA> + </refsect2> + </refsect1> +</REFENTRY> + +<!-- Keep this comment at the end of the file +Local variables: +mode: sgml +sgml-omittag:t +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: +--> |