</refnamediv>

<REFSYNOPSISDIV>

<REFSYNOPSISDIVINFO>

<DATE>1998-09-09</DATE>

<DATE>1999-04-14</DATE>

</REFSYNOPSISDIVINFO>

<SYNOPSIS>

CREATE OPERATOR <replaceable>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> [, ...] ]

[, HASHES ]

[, SORT1 = <replaceable class="parameter">left_sort_op</replaceable> ]

[, SORT2 = <replaceable class="parameter">right_sort_op</replaceable> ]

)

</SYNOPSIS>

<REFSECT2 ID="R2-SQL-CREATEOPERATOR-1">

<REFSECT2INFO>

<DATE>1998-09-09</DATE>

<DATE>1999-04-14</DATE>

</REFSECT2INFO>

<TITLE>

Inputs

</TERM>

<LISTITEM>

<PARA>

Thecorresponding commutative operator.

Thecommutator for this operator.

</PARA>

</LISTITEM>

</VARLISTENTRY>

</TERM>

<LISTITEM>

<PARA>

Thecorresponding negation operator.

Thenegator of this operator.

</PARA>

</LISTITEM>

</VARLISTENTRY>

</TERM>

<LISTITEM>

<PARA>

The corresponding restriction operator.

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>

</TERM>

<LISTITEM>

<PARA>

This operator can support a hash-join algorithm.

Indicates this operator can support a hash-join algorithm.

</PARA>

</LISTITEM>

</VARLISTENTRY>

<VARLISTENTRY>

<TERM>

<replaceable class="parameter">join_proc</replaceable>

<replaceable class="parameter">left_sort_op</replaceable>

</TERM>

<LISTITEM>

<PARA>

Procedure supporting table joins.

Operator that sorts the left-hand data type of this operator.

</PARA>

</LISTITEM>

</VARLISTENTRY>

<VARLISTENTRY>

<TERM>

<replaceable class="parameter">sort_op</replaceable>

<replaceable class="parameter">right_sort_op</replaceable>

</TERM>

<LISTITEM>

<PARA>

Operatorto use for sorting.

Operatorthat sorts the right-hand data type of this operator.

</PARA>

</LISTITEM>

</VARLISTENTRY>

<REFSECT2 ID="R2-SQL-CREATEOPERATOR-2">

<REFSECT2INFO>

<DATE>1998-09-09</DATE>

<DATE>1999-04-14</DATE>

</REFSECT2INFO>

<TITLE>

Outputs

<REFSECT1 ID="R1-SQL-CREATEOPERATOR-1">

<REFSECT1INFO>

<DATE>1998-09-09</DATE>

<DATE>1999-04-14</DATE>

</REFSECT1INFO>

<TITLE>

Description

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, thenthe commutator's

entry is updated to have thecurrent (new) operator

as itscommutator. This applies to the negator, as well.

does not yet have a commutator itself, then the commutator's

entry is updated to have thenewly created 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.

will be updated as a side effect. (As of Postgres 6.5,

it also works to just have both operators refer to each other.)

</para>

<para>

The nexttwo specifications are present to support the

The nextthree 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

can use 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 &lt;&lt;&lt; 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>

strategy is applicable. The current hash-join algorithm

is only correct for operators that represent equality tests;

furthermore, equality of the datatype must mean bitwise equality

of the representation of the type. (For example, a datatype that

contains unused bits that don't matter for equality tests could

not be hashjoined.)

The HASHES flag indicates to the query optimizer that a hash join

may safely be used with this operator.</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, &lt;&lt;&lt;. On the other

hand, merge-sort is not usable with the clause:

<programlisting>

MYBOXES.description &lt;&lt;&lt; MYBOXES2.description

</programlisting>

which operators should be used to sort the two operand

classes. Sort operators should only be provided for an equality

operator, and they should refer to less-than operators for the

left and right side data types respectively.

</para>

<para>

If other join strategies are found to be practical,

<REFSECT2 ID="R2-SQL-CREATEOPERATOR-3">

<REFSECT2INFO>

<DATE>1998-09-09</DATE>

<DATE>1999-04-14</DATE>

</REFSECT2INFO>

<TITLE>

Notes

COMMUTATOR = ===,

NEGATOR = !==,

RESTRICT = area_restriction_procedure,

JOIN = area_join_procedure,

HASHES,

JOIN =area-join-procedure,

SORT = <<<, <<<)

SORT1 =<<<,

SORT2 = <<<)

</ProgramListing>

</REFSECT1>

<REFSECT2 ID="R2-SQL-CREATEOPERATOR-4">

<REFSECT2INFO>

<DATE>1998-09-09</DATE>

<DATE>1999-04-14</DATE>

</REFSECT2INFO>

<TITLE>

SQL92

create operator - define a new user operator

[\fB,commutator=\fR com_op ]

[\fB,negator=\fR neg_op ]

[\fB,restrict=\fR res_proc ]

[\fB,hashes\fR]

[\fB,join=\fR join_proc ]

[\fB,sort=\fR sor_op1 {\fB,\fR sor_op2 } ]

[\fB,hashes\fR]

[\fB,sort1=\fR left_sort_op ]

[\fB,sort2=\fR right_sort_op ]

The commutator operator is present so that Postgres 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, <<<. Suppose that an operator, area-equal, ===,

exists, as well as an area not equal, !==. Hence, the query optimizer

area-greater-than, <<<. Hence, the query optimizer

could freely convert:

.nf

This allows the execution code to always use the latter representation

and simplifies the query optimizer somewhat.

.PP

Suppose that an operator, area-equal, ===,

exists, as well as an area not equal, !==.

The negator operator allows the query optimizer to convert

.nf

.fi

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.

itself, then the commutator's entry is updated to have the newly created

operator as its commutator. This applies to the negator, as well.

.PP

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.

negator. The first will be updated as a side effect. (As of Postgres 6.5,

it also works to just have both operators refer to each other.)

.PP

The nexttwo specifications are present to support the query optimizer

The nextthree 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, Postgresis planning on implementing a hash-join algorithm

addition, Postgrescan use 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:

.nf

.ce1

MYBOXES.description === MYBOXES2.description

.fi

but not for a clause of the form:

.nf

.ce1

MYBOXES.description <<< MYBOXES2.description.

.fi

strategy is applicable.

The current hash-join algorithm

is only correct for operators that represent equality tests;

furthermore, equality of the datatype must mean bitwise equality

of the representation of the type. (For example, a datatype that

contains unused bits that don't matter for equality tests could

not be hashjoined.)

The

.BR hashes

flaggives the needed informationto the query optimizerconcerning

whether a hash join strategy is usable for theoperator in question.

flagindicatesto the query optimizerthat a hash join may safely be

used with thisoperator.

.PP

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:

.nf

.ce1

MYBOXES.description <<< MYBOXES2.description

.fi

whether merge-sort is a usable join strategy and which operators should

be used to sort the two operand classes.

Sort operators should only be provided for an equality

operator, and they should refer to less-than operators for the

left and right side data types respectively.

.PP

If other join strategies are found to be practical, Postgres will change

the optimizer and run-time system to use them and will require

additional specification when an operator is defined. Fortunately,

commutator = ===,

negator = !==,

restrict = area_restriction_procedure,

join = area_join_procedure,

hashes,

join =area-join-procedure,

sort = <<<, <<<)

sort1 =<<<,

sort2 = <<<)

.fi

Operator names cannot be composed of alphabetic characters in

Postgres.

.PP

If an operator is defined before its commuting operator has been defined

fields will be placedin the system catalogs. Thismay interfere with

the definition of later operators.

If an operator is defined before its commuting operator has been defined,

in the system catalogs. Thisentry will be overridden when the commutator

is eventually defined.