<!--

$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_operator.sgml,v 1.35 2003/04/22 10:08:08 petere Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/ref/create_operator.sgml,v 1.36 2003/08/17 22:09:00 tgl Exp $

PostgreSQL documentation

-->

The operator name is a sequence of up to <symbol>NAMEDATALEN</>-1

(63 by default) characters from the following list:

<literallayout>

+ - * / &lt; &gt; = ~ ! @ # % ^ &amp; | ` ? $

+ - * / &lt; &gt; = ~ ! @ # % ^ &amp; | ` ?

</literallayout>

There are a few restrictions on your choice of name:

<itemizedlist>

<listitem>

<para>

<literal>$</literal> cannot be defined as a single-character operator,

although it can be part of a multicharacter operator name.

</para>

</listitem>

<listitem>

<para>

<literal>--</literal> and <literal>/*</literal> cannot appear anywhere in an operator name,

<literal>-</literal>,

unless the name also contains at least one of these characters:

<literallayout>

~ ! @ # % ^ &amp; | ` ? $

~ ! @ # % ^ &amp; | ` ?

</literallayout>

For example, <literal>@-</literal> is an allowed operator name,

but <literal>*-</literal> is not.

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/ref/select.sgml,v 1.67 2003/05/04 02:23:16 petere Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/ref/select.sgml,v 1.68 2003/08/17 22:09:00 tgl Exp $

PostgreSQL documentation

-->

<literal>ORDER BY</> clause. If not specified, <literal>ASC</> is

assumed by default. Alternatively, a specific ordering operator

name may be specified in the <literal>USING</> clause.

<literal>ASC</> is equivalent to <literal>USING &lt;</> and

<literal>DESC</> is equivalent to <literal>USING &gt;</>.

<literal>ASC</> is usually equivalent to <literal>USING &lt;</> and

<literal>DESC</> is usually equivalent to <literal>USING &gt;</>.

(But the creator of a user-defined datatype can define exactly what the

default sort ordering is, and it might correspond to operators with other

names.)

</para>

<para>

</para>

<para>

Data of character types is sorted according to the locale-specific

Character-string data is sorted according to the locale-specific

collation order that was established when the database cluster

was initialized.

</para>

that he will actually get. To help detect this sort of mistake,

PostgreSQL will warn if the implicit-<literal>FROM</literal>

feature is used in a <command>SELECT</command> statement that also

contains an explicit <literal>FROM</literal> clause.

contains an explicit <literal>FROM</literal> clause. Also, it is

possible to disable the implicit-<literal>FROM</literal> feature

by setting the <varname>ADD_MISSING_FROM</> parameter to false.

</para>

</refsect2>

noise and can be omitted without affecting the meaning. The

<productname>PostgreSQL</productname> parser requires this key

word when renaming output columns because the type extensibility

features lead to parsing ambiguitiesin this context.

features lead to parsing ambiguitieswithout it.

<literal>AS</literal> is optional in <literal>FROM</literal>

items, however.

</para>

<title>Namespace Available to <literal>GROUP BY</literal> and <literal>ORDER BY</literal></title>

<para>

In theSQL standard, an <literal>ORDER BY</literal> clause may

In theSQL92 standard, an <literal>ORDER BY</literal> clause may

only use result column names or numbers, while a <literal>GROUP

BY</literal> clause may only use expressions based on input column

names. <productname>PostgreSQL</productname> extends each of

expression will always be taken as input-column names, not as

result-column names.

</para>

<para>

SQL99 uses a slightly different definition which is not upward compatible

with SQL92. In most cases, however, <productname>PostgreSQL</productname>

will interpret an <literal>ORDER BY</literal> or <literal>GROUP

BY</literal> expression the same way SQL99 does.

</para>

</refsect2>

<refsect2>

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/xindex.sgml,v 1.30 2003/04/1309:57:35 petere Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/xindex.sgml,v 1.31 2003/08/17 22:09:00 tgl Exp $

-->

<sect1 id="xindex">

</programlisting>

It is important to specify the restriction and join selectivity

functions, otherwise the optimizer will be unable to make effective

use of the index. Note thatthere less-than, equal, and

use of the index. Note thatthe less-than, equal, and

greater-than cases should use different selectivity functions.

</para>

</para>

</sect2>

<sect2 id="xindex-opclass-dependencies">

<title>System Dependencies on Operator Classes</title>

<indexterm>

<primary>ordering operator</primary>

</indexterm>

<para>

<productname>PostgreSQL</productname> uses operator classes to infer the

properties of operators in more ways than just whether they can be used

with indexes. Therefore, you might want to create operator classes

even if you have no intention of indexing any columns of your datatype.

</para>

<para>

In particular, there are SQL features such as <literal>ORDER BY</> and

<literal>DISTINCT</> that require comparison and sorting of values.

To implement these features on a user-defined datatype,

<productname>PostgreSQL</productname> looks for the default B-tree operator

class for the datatype. The <quote>equals</> member of this operator

class defines the system's notion of equality of values for

<literal>GROUP BY</> and <literal>DISTINCT</>, and the sort ordering

imposed by the operator class defines the default <literal>ORDER BY</>

ordering.

</para>

<para>

Comparison of arrays of user-defined types also relies on the semantics

defined by the default B-tree operator class.

</para>

<para>

If there is no default B-tree operator class for a datatype, the system

will look for a default hash operator class. But since that kind of

operator class only provides equality, in practice it is only enough

to support array equality.

</para>

<para>

When there is no default operator class for a datatype, you will get

errors like <quote>could not identify an ordering operator</> if you

try to use these SQL features with the datatype.

</para>

<note>

<para>

In <ProductName>PostgreSQL</ProductName> versions before 7.4,

sorting and grouping operations would implicitly use operators named

<literal>=</>, <literal>&lt;</>, and <literal>&gt;</>. The new

behavior of relying on default operator classes avoids having to make

any assumption about the behavior of operators with particular names.

</para>

</note>

</sect2>

<sect2 id="xindex-opclass-features">

<title>Special Features of Operator Classes</title>

<para>

There are two special features of operator classes that we have

not discussed yet, mainly because they are notveryuseful

with thedefault B-treeindexmethod.

not discussed yet, mainly because they are not useful

with themost commonly usedindexmethods.

</para>

<para>

<!--

$Header: /cvsroot/pgsql/doc/src/sgml/xoper.sgml,v 1.24 2003/06/22 22:04:54 tgl Exp $

$Header: /cvsroot/pgsql/doc/src/sgml/xoper.sgml,v 1.25 2003/08/17 22:09:00 tgl Exp $

-->

<sect1 id="xoper">

</para>

</note>

<note>

<para>

<literal>GROUP BY</> and <literal>DISTINCT</> operations require each

datatype being grouped or compared to have a mergejoinable

equality operator named <literal>=</>. The equality operator and its

associated <literal>SORT1</> operator are used to implement these

operations. Also, the associated <literal>SORT1</> operator is the

default ordering operator for <literal>ORDER BY</>.

</para>

</note>

<note>

<para>

In <ProductName>PostgreSQL</ProductName> versions before 7.3,