Add aggsortop column to pg_aggregate, so that MIN/MAX optimization can

be supported for all datatypes.  Add CREATE AGGREGATE and pg_dump support
too.  Add specialized min/max aggregates for bpchar, instead of depending
on text's min/max, because otherwise the possible use of bpchar indexes
cannot be recognized.
initdb forced because of catalog changes.

2 files changed, 47 insertions, 4 deletions

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index 11d774410d3..5a171e94969 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -1,6 +1,6 @@
 <!--
  Documentation of the system catalogs, directed toward PostgreSQL developers
- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.99 2005/03/29 19:44:22 tgl Exp $
+ $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.100 2005/04/12 04:26:13 tgl Exp $
  -->
 
 <chapter id="catalogs">
@@ -251,10 +251,16 @@
       <entry>Final function (zero if none)</entry>
      </row>
      <row>
+      <entry><structfield>aggsortop</structfield></entry>
+      <entry><type>oid</type></entry>
+      <entry><literal><link linkend="catalog-pg-operator"><structname>pg_operator</structname></link>.oid</literal></entry>
+      <entry>Associated sort operator (zero if none)</entry>
+     </row>
+     <row>
       <entry><structfield>aggtranstype</structfield></entry>
       <entry><type>oid</type></entry>
       <entry><literal><link linkend="catalog-pg-type"><structname>pg_type</structname></link>.oid</literal></entry>
-      <entry>The type of the aggregate function's internal transition (state) data</entry>
+      <entry>Data type of the aggregate function's internal transition (state) data</entry>
      </row>
      <row>
       <entry><structfield>agginitval</structfield></entry>
@@ -263,7 +269,7 @@
       <entry>
        The initial value of the transition state.  This is a text
        field containing the initial value in its external string
-       representation.  If the value is null, the transition state
+       representation.  If this field is null, the transition state
        value starts out null.
       </entry>
      </row>
diff --git a/doc/src/sgml/ref/create_aggregate.sgml b/doc/src/sgml/ref/create_aggregate.sgml
index 4ae1d1dd8cd..24e233f5894 100644
--- a/doc/src/sgml/ref/create_aggregate.sgml
+++ b/doc/src/sgml/ref/create_aggregate.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/create_aggregate.sgml,v 1.31 2005/01/04 00:39:53 tgl Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/create_aggregate.sgml,v 1.32 2005/04/12 04:26:15 tgl Exp $
 PostgreSQL documentation
 -->
 
@@ -26,6 +26,7 @@ CREATE AGGREGATE <replaceable class="PARAMETER">name</replaceable> (
     STYPE = <replaceable class="PARAMETER">state_data_type</replaceable>
     [ , FINALFUNC = <replaceable class="PARAMETER">ffunc</replaceable> ]
     [ , INITCOND = <replaceable class="PARAMETER">initial_condition</replaceable> ]
+    [ , SORTOP = <replaceable class="PARAMETER">sort_operator</replaceable> ]
 )
 </synopsis>
  </refsynopsisdiv>
@@ -125,6 +126,29 @@ CREATE AGGREGATE <replaceable class="PARAMETER">name</replaceable> (
    <function>avg</function> returns null when it sees there were zero
    input rows.
   </para>
+  
+  <para>
+   Aggregates that behave like <function>MIN</> or <function>MAX</> can
+   sometimes be optimized by looking into an index instead of scanning every
+   input row.  If this aggregate can be so optimized, indicate it by
+   specifying a <firstterm>sort operator</>.  The basic requirement is that
+   the aggregate must yield the first element in the sort ordering induced by
+   the operator; in other words
+<programlisting>
+SELECT agg(col) FROM tab;
+</programlisting>
+   must be equivalent to
+<programlisting>
+SELECT col FROM tab ORDER BY col USING sortop LIMIT 1;
+</programlisting>
+   Further assumptions are that the aggregate ignores null inputs, and that
+   it delivers a null result if and only if there were no non-null inputs.
+   Ordinarily, a datatype's <literal>&lt;</> operator is the proper sort
+   operator for <function>MIN</>, and <literal>&gt;</> is the proper sort
+   operator for <function>MAX</>.  Note that the optimization will never
+   actually take effect unless the specified operator is the LessThan or
+   GreaterThan strategy member of a btree index opclass.
+  </para>
  </refsect1>
 
  <refsect1>
@@ -211,6 +235,19 @@ CREATE AGGREGATE <replaceable class="PARAMETER">name</replaceable> (
      </para>
     </listitem>
    </varlistentry>
+
+   <varlistentry>
+    <term><replaceable class="PARAMETER">sort_operator</replaceable></term>
+    <listitem>
+     <para>
+      The associated sort operator for a <function>MIN</>- or
+      <function>MAX</>-like aggregate.
+      This is just an operator name (possibly schema-qualified).
+      The operator is assumed to have the same input datatypes as
+      the aggregate.
+     </para>
+    </listitem>
+   </varlistentry>
   </variablelist>
 
   <para>