<!--

Documentation of the system catalogs, directed toward PostgreSQL developers

$Header: /cvsroot/pgsql/doc/src/sgml/catalogs.sgml,v 2.41 2002/04/11 19:59:54 tgl Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/catalogs.sgml,v 2.42 2002/04/16 23:08:09 tgl Exp $

-->

<chapter id="catalogs">

<entry>Name of the operator</entry>

</row>

<row>

<entry>oprnamespace</entry>

<entry><type>oid</type></entry>

<entry>pg_namespace.oid</entry>

<entry>

The OID of the namespace that contains this operator

</entry>

</row>

<row>

<entry>oprowner</entry>

<entry><type>int4</type></entry>

<entry>oprprec</entry>

<entry><type>int2</type></entry>

<entry></entry>

<entry>unused</entry>

<entry>precedence (currently unused, as precedences are hard-wired

in the grammar)</entry>

</row>

<row>

<entry>oprisleft</entry>

<entry><type>bool</type></entry>

<entry></entry>

<entry>unused</entry>

<entry>left-associativity (currently unused, as this is hard-wired

in the grammar)</entry>

</row>

<row>

<entry>pg_operator.oid</entry>

<entry>

If this operator supports merge joins, the operator that sorts

the type of the left-hand operand

the type of the left-hand operand (<literal>L&lt;L</>)

</entry>

</row>

<entry>pg_operator.oid</entry>

<entry>

If this operator supports merge joins, the operator that sorts

the type of the right-hand operand

the type of the right-hand operand (<literal>R&lt;R</>)

</entry>

</row>

<row>

<entry>oprltcmpop</entry>

<entry><type>oid</type></entry>

<entry>pg_operator.oid</entry>

<entry>

If this operator supports merge joins, the less-than operator that

compares the left and right operand types (<literal>L&lt;R</>)

</entry>

</row>

<row>

<entry>oprgtcmpop</entry>

<entry><type>oid</type></entry>

<entry>pg_operator.oid</entry>

<entry>

If this operator supports merge joins, the greater-than operator that

compares the left and right operand types (<literal>L&gt;R</>)

</entry>

</row>

<row>

<entry>oprcode</entry>

<entry><type>regproc</type></entry>

<entry></entry>

<entry>pg_proc.oid</entry>

<entry>Function that implements this operator</entry>

</row>

<row>

<entry>oprrest</entry>

<entry><type>regproc</type></entry>

<entry></entry>

<entry>pg_proc.oid</entry>

<entry>Restriction selectivity estimation function for this operator</entry>

</row>

<row>

<entry>oprjoin</entry>

<entry><type>regproc</type></entry>

<entry></entry>

<entry>pg_proc.oid</entry>

<entry>Join selectivity estimation function for this operator</entry>

</row>

</tbody>

<row>

<entry>typinput</entry>

<entry><type>regproc</type></entry>

<entry></entry>

<entry>pg_proc.oid</entry>

<entry>Input function</entry>

</row>

<row>

<entry>typoutput</entry>

<entry><type>regproc</type></entry>

<entry></entry>

<entry>pg_proc.oid</entry>

<entry>Output function</entry>

</row>

<row>

<entry>typreceive</entry>

<entry><type>regproc</type></entry>

<entry></entry>

<entry>pg_proc.oid</entry>

<entry>unused</entry>

</row>

<row>

<entry>typsend</entry>

<entry><type>regproc</type></entry>

<entry></entry>

<entry>pg_proc.oid</entry>

<entry>unused</entry>

</row>

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_operator.sgml,v 1.25 2002/03/22 19:20:39 petere Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_operator.sgml,v 1.26 2002/04/16 23:08:10 tgl Exp $

PostgreSQL documentation

-->

] [, 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 ] [, SORT1 = <replaceable class="parameter">left_sort_op</replaceable> ] [, SORT2 = <replaceable class="parameter">right_sort_op</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>

<refsect2 id="R2-SQL-CREATEOPERATOR-1">

</para>

</listitem>

</varlistentry>

<varlistentry>

<term>MERGES</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

If this operator can support a merge join, the less-than

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

</para>

</listitem>

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

<listitem>

<para>

If this operator can support a merge join, the

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>

</refsect2>

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

</para>

<para>

The HASHES, SORT1, and SORT2 options are present to support the

query optimizer in performing joins.

<productname>PostgreSQL</productname> can always

evaluate a join (i.e., processing a clause with two tuple

variables separated by an operator that returns a <type>boolean</type>)

by iterative substitution [WONG76].

In addition, <productname>PostgreSQL</productname>

can use a hash-join algorithm along

the lines of [SHAP86]; however, it must know whether this

strategy is applicable. The current hash-join algorithm

is only correct for operators that represent equality tests;

furthermore, equality of the data type must mean bitwise equality

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

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

not be hash-joined.)

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

may safely be used with this operator.</para>

The HASHES, MERGES, SORT1, SORT2, LTCMP, and GTCMP options are present to

support the query optimizer in performing joins.

<productname>PostgreSQL</productname> can always evaluate a join (i.e.,

processing a clause with two tuple variables separated by an operator that

returns a <type>boolean</type>) by iterative substitution [WONG76]. In

addition, <productname>PostgreSQL</productname> can use a hash-join

algorithm along the lines of [SHAP86]; however, it must know whether this

strategy is applicable. The current hash-join algorithm is only correct

for operators that represent equality tests; furthermore, equality of the

data type must mean bitwise equality of the representation of the type.

(For example, a data type that contains unused bits that don't matter for

equality tests could not be hash-joined.) 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

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.

Similarly, the MERGES flag indicates whether merge-sort is a usable join

strategy for this operator. A merge join requires that the two input

datatypes have consistent orderings, and that the mergejoin operator

behave like equality with respect to that ordering. For example, it is

possible to merge-join equality between an integer and a float variable by

sorting both inputs in ordinary

numeric order. Execution of a merge join requires that the system be

able to identify four operators related to the mergejoin equality operator:

less-than comparison for the left input datatype,

less-than comparison for the right input datatype,

less-than comparison between the two datatypes, and

greater-than comparison between the two datatypes. It is possible to

specify these by name, as the SORT1, SORT2, LTCMP, and GTCMP options

respectively. The system will fill in the default names <literal>&lt;</>,

<literal>&lt;</>, <literal>&lt;</>, <literal>&gt;</> respectively if

any of these are omitted when MERGES is specified. Also, MERGES will

be assumed to be implied if any of these four operator options appear.

</para>

<para>

If other join strategies are found to be practical,

RESTRICT = area_restriction_procedure,

JOIN = area_join_procedure,

HASHES,

SORT1 = <<<,

SORT2 = <<<

SORT1 = &lt;&lt;&lt;,

SORT2 = &lt;&lt;&lt;

-- Since sort operators were given, MERGES is implied.

-- LTCMP and GTCMP are assumed to be &lt; and &gt; respectively

);

</programlisting>

</refsect1>

char*Name_pg_opclass_indices[Num_pg_opclass_indices]=

{OpclassAmNameIndex,OpclassOidIndex};

char*Name_pg_operator_indices[Num_pg_operator_indices]=

{OperatorOidIndex,OperatorNameIndex};

{OperatorOidIndex,OperatorNameNspIndex};

char*Name_pg_proc_indices[Num_pg_proc_indices]=

{ProcedureOidIndex,ProcedureNameNspIndex};

char*Name_pg_relcheck_indices[Num_pg_relcheck_indices]=