Replace the hard-wired type knowledge in TypeCategory() and IsPreferredType()

with system catalog lookups, as was foreseen to be necessary almost since
their creation.  Instead put the information into two new pg_type columns,
typcategory and typispreferred.  Add support for setting these when
creating a user-defined base type.

The category column is just a "char" (i.e. a poor man's enum), allowing
a crude form of user extensibility of the category list: just use an
otherwise-unused character.  This seems sufficient for foreseen uses,
but we could upgrade to having an actual category catalog someday, if
there proves to be a huge demand for custom type categories.

In this patch I have attempted to hew exactly to the behavior of the
previous hardwired logic, except for introducing new type categories for
arrays, composites, and enums.  In particular the default preferred state
for user-defined types remains TRUE.  That seems worth revisiting, but it
should be done as a separate patch from introducing the infrastructure.
Likewise, any adjustment of the standard set of categories should be done
separately.

3 files changed, 195 insertions, 51 deletions

diff --git a/doc/src/sgml/catalogs.sgml b/doc/src/sgml/catalogs.sgml
index a7a3cea9068..f756830a95d 100644
--- a/doc/src/sgml/catalogs.sgml
+++ b/doc/src/sgml/catalogs.sgml
@@ -1,4 +1,4 @@
-<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.171 2008/07/18 03:32:51 tgl Exp $ -->
+<!-- $PostgreSQL: pgsql/doc/src/sgml/catalogs.sgml,v 2.172 2008/07/30 17:05:04 tgl Exp $ -->
 <!--
  Documentation of the system catalogs, directed toward PostgreSQL developers
  -->
@@ -994,7 +994,7 @@
       <entry><type>int4</type></entry>
       <entry></entry>
       <entry>
-       The number of direct ancestors this column has.  A column with a 
+       The number of direct ancestors this column has.  A column with a
        nonzero number of ancestors cannot be dropped nor renamed
       </entry>
      </row>
@@ -1005,7 +1005,7 @@
 
   <para>
    In a dropped column's <structname>pg_attribute</structname> entry,
-   <structfield>atttypid</structfield> is reset to zero, but 
+   <structfield>atttypid</structfield> is reset to zero, but
    <structfield>attlen</structfield> and the other fields copied from
    <structname>pg_type</> are still valid.  This arrangement is needed
    to cope with the situation where the dropped column's data type was
@@ -1118,7 +1118,7 @@
       <entry><structfield>rolconnlimit</structfield></entry>
       <entry><type>int4</type></entry>
       <entry>
-       For roles that can log in, this sets maximum number of concurrent 
+       For roles that can log in, this sets maximum number of concurrent
        connections this role can make.  -1 means no limit
       </entry>
      </row>
@@ -2169,7 +2169,7 @@
       <entry><type>int4</type></entry>
       <entry></entry>
       <entry>
-       Sets maximum number of concurrent connections that can be made 
+       Sets maximum number of concurrent connections that can be made
        to this database.  -1 means no limit
       </entry>
      </row>
@@ -4855,7 +4855,7 @@
 
   <para>
    The catalog <structname>pg_type</structname> stores information about data
-   types.  Base types (scalar types) are created with
+   types.  Base types and enum types (scalar types) are created with
    <xref linkend="sql-createtype" endterm="sql-createtype-title">, and
    domains with
    <xref linkend="sql-createdomain" endterm="sql-createdomain-title">.
@@ -4926,8 +4926,7 @@
        where Datum is 8 bytes).
        Variable-length types are always passed by reference. Note that
        <structfield>typbyval</structfield> can be false even if the
-       length would allow pass-by-value; this is currently true for
-       type <type>float4</type>, for example
+       length would allow pass-by-value
       </entry>
      </row>
 
@@ -4948,6 +4947,28 @@
      </row>
 
      <row>
+      <entry><structfield>typcategory</structfield></entry>
+      <entry><type>char</type></entry>
+      <entry></entry>
+      <entry>
+       <structfield>typcategory</structfield> is an arbitrary classification
+       of data types that is used by the parser to determine which implicit
+       casts should be <quote>preferred</>.
+       See <xref linkend="catalog-typcategory-table">
+      </entry>
+     </row>
+
+     <row>
+      <entry><structfield>typispreferred</structfield></entry>
+      <entry><type>bool</type></entry>
+      <entry></entry>
+      <entry>
+       True if the type is a preferred cast target within its
+       <structfield>typcategory</structfield>
+      </entry>
+     </row>
+
+     <row>
       <entry><structfield>typisdefined</structfield></entry>
       <entry><type>bool</type></entry>
       <entry></entry>
@@ -5217,6 +5238,86 @@
     </tbody>
    </tgroup>
   </table>
+
+  <para>
+   <xref linkend="catalog-typcategory-table"> lists the system-defined values
+   of <structfield>typcategory</>.  Any future additions to this list will
+   also be upper-case ASCII letters.  All other ASCII characters are reserved
+   for user-defined categories.
+  </para>
+
+  <table id="catalog-typcategory-table">
+   <title><structfield>typcategory</> Codes</title>
+
+   <tgroup cols=2>
+    <thead>
+     <row>
+      <entry>Code</entry>
+      <entry>Category</entry>
+     </row>
+    </thead>
+
+    <tbody>
+     <row>
+      <entry><literal>A</literal></entry>
+      <entry>Array types</entry>
+     </row>
+     <row>
+      <entry><literal>B</literal></entry>
+      <entry>Boolean types</entry>
+     </row>
+     <row>
+      <entry><literal>C</literal></entry>
+      <entry>Composite types</entry>
+     </row>
+     <row>
+      <entry><literal>D</literal></entry>
+      <entry>Date/time types</entry>
+     </row>
+     <row>
+      <entry><literal>E</literal></entry>
+      <entry>Enum types</entry>
+     </row>
+     <row>
+      <entry><literal>G</literal></entry>
+      <entry>Geometric types</entry>
+     </row>
+     <row>
+      <entry><literal>I</literal></entry>
+      <entry>Network address types</entry>
+     </row>
+     <row>
+      <entry><literal>N</literal></entry>
+      <entry>Numeric types</entry>
+     </row>
+     <row>
+      <entry><literal>P</literal></entry>
+      <entry>Pseudo-types</entry>
+     </row>
+     <row>
+      <entry><literal>S</literal></entry>
+      <entry>String types</entry>
+     </row>
+     <row>
+      <entry><literal>T</literal></entry>
+      <entry>Timespan types</entry>
+     </row>
+     <row>
+      <entry><literal>U</literal></entry>
+      <entry>User-defined types</entry>
+     </row>
+     <row>
+      <entry><literal>V</literal></entry>
+      <entry>Bit-string types</entry>
+     </row>
+     <row>
+      <entry><literal>X</literal></entry>
+      <entry><type>unknown</> type</entry>
+     </row>
+    </tbody>
+   </tgroup>
+  </table>
+
  </sect1>
 
  <sect1 id="views-overview">
@@ -5787,7 +5888,7 @@
    for another transaction, it does so by attempting to acquire share lock on
    the other transaction ID (either virtual or permanent ID depending on the
    situation). That will succeed only when the other transaction
-   terminates and releases its locks. 
+   terminates and releases its locks.
   </para>
 
   <para>
@@ -6036,7 +6137,7 @@
   <para>
    The view <structname>pg_roles</structname> provides access to
    information about database roles.  This is simply a publicly
-   readable view of 
+   readable view of
    <link linkend="catalog-pg-authid"><structname>pg_authid</structname></link>
    that blanks out the password field.
   </para>
@@ -6121,7 +6222,7 @@
       <entry><type>int4</type></entry>
       <entry></entry>
       <entry>
-       For roles that can log in, this sets maximum number of concurrent 
+       For roles that can log in, this sets maximum number of concurrent
        connections this role can make.  -1 means no limit
       </entry>
      </row>
@@ -6316,7 +6417,7 @@
     </tbody>
    </tgroup>
   </table>
-  
+
   <para>
    The <structname>pg_settings</structname> view cannot be inserted into or
    deleted from, but it can be updated.  An <command>UPDATE</command> applied
@@ -6774,7 +6875,7 @@
   <para>
    The view <structname>pg_user</structname> provides access to
    information about database users.  This is simply a publicly
-   readable view of 
+   readable view of
    <link linkend="view-pg-shadow"><structname>pg_shadow</structname></link>
    that blanks out the password field.
   </para>
diff --git a/doc/src/sgml/ref/create_type.sgml b/doc/src/sgml/ref/create_type.sgml
index e643f098252..8002e2c4b9c 100644
--- a/doc/src/sgml/ref/create_type.sgml
+++ b/doc/src/sgml/ref/create_type.sgml
@@ -1,5 +1,5 @@
 <!--
-$PostgreSQL: pgsql/doc/src/sgml/ref/create_type.sgml,v 1.74 2008/05/27 18:05:13 tgl Exp $
+$PostgreSQL: pgsql/doc/src/sgml/ref/create_type.sgml,v 1.75 2008/07/30 17:05:04 tgl Exp $
 PostgreSQL documentation
 -->
 
@@ -38,6 +38,8 @@ CREATE TYPE <replaceable class="parameter">name</replaceable> (
     [ , PASSEDBYVALUE ]
     [ , ALIGNMENT = <replaceable class="parameter">alignment</replaceable> ]
     [ , STORAGE = <replaceable class="parameter">storage</replaceable> ]
+    [ , CATEGORY = <replaceable class="parameter">category</replaceable> ]
+    [ , PREFERRED = <replaceable class="parameter">preferred</replaceable> ]
     [ , DEFAULT = <replaceable class="parameter">default</replaceable> ]
     [ , ELEMENT = <replaceable class="parameter">element</replaceable> ]
     [ , DELIMITER = <replaceable class="parameter">delimiter</replaceable> ]
@@ -282,6 +284,27 @@ CREATE TYPE <replaceable class="parameter">name</replaceable>
   </para>
 
   <para>
+   The <replaceable class="parameter">category</replaceable> and
+   <replaceable class="parameter">preferred</replaceable> parameters can be
+   used to help control which implicit cast will be applied in ambiguous
+   situations.  Each data type belongs to a category named by a single ASCII
+   character, and each type is either <quote>preferred</> or not within its
+   category.  The parser will prefer casting to preferred types (but only from
+   other types within the same category) when this rule is helpful in
+   resolving overloaded functions or operators.  For more details see <xref
+   linkend="typeconv">.  For types that have no implicit casts to or from any
+   other types, it is sufficient to leave these settings at the defaults.
+   However, for a group of related types that have implicit casts, it is often
+   helpful to mark them all as belonging to a category and select one or two
+   of the <quote>most general</> types as being preferred within the category.
+   The <replaceable class="parameter">category</replaceable> parameter is
+   especially useful when adding a user-defined type to an existing built-in
+   category, such as the numeric or string types.  However, it is also
+   possible to create new entirely-user-defined type categories.  Select any
+   ASCII character other than an upper-case letter to name such a category.
+  </para>
+
+  <para>
    A default value can be specified, in case a user wants columns of the
    data type to default to something other than the null value.
    Specify the default with the <literal>DEFAULT</literal> key word.
@@ -495,6 +518,31 @@ CREATE TYPE <replaceable class="parameter">name</replaceable>
    </varlistentry>
 
    <varlistentry>
+    <term><replaceable class="parameter">category</replaceable></term>
+    <listitem>
+     <para>
+      The category code (a single ASCII character) for this type.
+      The default is <literal>'U'</> for <quote>user-defined type</>.
+      Other standard category codes can be found in
+      <xref linkend="catalog-typcategory-table">.  You may also choose
+      other ASCII characters in order to create custom categories.
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
+    <term><replaceable class="parameter">preferred</replaceable></term>
+    <listitem>
+     <para>
+      True if this type is a preferred type within its type category,
+      else false.  The default is true (which is appropriate for
+      all entries in category <literal>U</>, but is usually not
+      appropriate for new types in other categories &mdash; beware!).
+     </para>
+    </listitem>
+   </varlistentry>
+
+   <varlistentry>
     <term><replaceable class="parameter">default</replaceable></term>
     <listitem>
      <para>
diff --git a/doc/src/sgml/typeconv.sgml b/doc/src/sgml/typeconv.sgml
index 4f04801210b..c9f96323f30 100644
--- a/doc/src/sgml/typeconv.sgml
+++ b/doc/src/sgml/typeconv.sgml
@@ -1,4 +1,4 @@
-<!-- $PostgreSQL: pgsql/doc/src/sgml/typeconv.sgml,v 1.55 2008/07/16 01:30:21 tgl Exp $ -->
+<!-- $PostgreSQL: pgsql/doc/src/sgml/typeconv.sgml,v 1.56 2008/07/30 17:05:04 tgl Exp $ -->
 
 <chapter Id="typeconv">
 <title>Type Conversion</title>
@@ -10,7 +10,7 @@
 
 <para>
 <acronym>SQL</acronym> statements can, intentionally or not, require
-mixing of different data types in the same expression. 
+mixing of different data types in the same expression.
 <productname>PostgreSQL</productname> has extensive facilities for
 evaluating mixed-type expressions.
 </para>
@@ -153,19 +153,32 @@ altered.)
 
 <para>
 An additional heuristic is provided in the parser to allow better guesses
-at proper behavior for <acronym>SQL</acronym> standard types. There are
-several basic <firstterm>type categories</firstterm> defined: <type>boolean</type>,
-<type>numeric</type>, <type>string</type>, <type>bitstring</type>, <type>datetime</type>, <type>timespan</type>, <type>geometric</type>, <type>network</type>,
-and user-defined. Each category, with the exception of user-defined, has
-one or more <firstterm>preferred types</firstterm> which are preferentially
-selected when there is ambiguity.
-In the user-defined category, each type is its own preferred type.
-Ambiguous expressions (those with multiple candidate parsing solutions)
-can therefore often be resolved when there are multiple possible built-in types, but
-they will raise an error when there are multiple choices for user-defined
-types.
+at proper casting behavior among groups of types that have implicit casts.
+Data types are divided into several basic <firstterm>type
+categories</firstterm>, including <type>boolean</type>, <type>numeric</type>,
+<type>string</type>, <type>bitstring</type>, <type>datetime</type>,
+<type>timespan</type>, <type>geometric</type>, <type>network</type>, and
+user-defined.  (For a list see <xref linkend="catalog-typcategory-table">;
+but note it is also possible to create custom type categories.)  Within each
+category there are one or more <firstterm>preferred types</firstterm>, which
+are preferentially selected when there is ambiguity.  With careful selection
+of preferred types and available implicit casts, it is possible to ensure that
+ambiguous expressions (those with multiple candidate parsing solutions) can be
+resolved in a useful way.
 </para>
 
+<note>
+ <para>
+  For what are now historical reasons, types in the <quote>user-defined</>
+  category are normally always marked as <quote>preferred</>.  Since all types
+  in this category are preferred, the heuristic that favors preferred types
+  accomplishes nothing, and thus this situation is equivalent to treating them
+  all as non-preferred.  The <quote>preferred</> marking is useful within the
+  system-defined type categories, and can be useful within custom type
+  categories.
+ </para>
+</note>
+
 <para>
 All type conversion rules are designed with several principles in mind:
 
@@ -178,23 +191,6 @@ Implicit conversions should never have surprising or unpredictable outcomes.
 
 <listitem>
 <para>
-User-defined types, of which the parser has no <foreignphrase>a priori</> knowledge, should be
-<quote>higher</quote> in the type hierarchy. In mixed-type expressions, native types shall always
-be converted to a user-defined type (of course, only if conversion is necessary).
-</para>
-</listitem>
-
-<listitem>
-<para>
-User-defined types are not related. Currently, <productname>PostgreSQL</productname>
-does not have information available to it on relationships between types, other than
-hardcoded heuristics for built-in types and implicit relationships based on
-available functions and casts.
-</para>
-</listitem>
-
-<listitem>
-<para>
 There should be no extra overhead from the parser or executor
 if a query does not need implicit type conversion.
 That is, if a query is well formulated and the types already match up, then the query should proceed
@@ -317,7 +313,7 @@ all the remaining candidates accept the same type category, select that
 category; otherwise fail because the correct choice cannot be deduced
 without more clues.  Now discard
 candidates that do not accept the selected type category.  Furthermore,
-if any candidate accepts a preferred type at a given argument position,
+if any candidate accepts a preferred type in that category,
 discard candidates that accept non-preferred types for that argument.
 </para>
 </step>
@@ -341,7 +337,7 @@ Some examples follow.
 
 <para>
 There is only one factorial operator (postfix <literal>!</>)
-defined in the standard catalog, and it takes an argument of type 
+defined in the standard catalog, and it takes an argument of type
 <type>bigint</type>.
 The scanner assigns an initial type of <type>integer</type> to the argument
 in this query expression:
@@ -407,7 +403,7 @@ In this case there is no initial hint for which type to use, since no types
 are specified in the query. So, the parser looks for all candidate operators
 and finds that there are candidates accepting both string-category and
 bit-string-category inputs.  Since string category is preferred when available,
-that category is selected, and then the 
+that category is selected, and then the
 preferred type for strings, <type>text</type>, is used as the specific
 type to resolve the unknown literals to.
 </para>
@@ -585,7 +581,7 @@ If only one candidate remains, use it; else continue to the next step.
 <step performance="required">
 <para>
 If any input arguments are <type>unknown</type>, check the type categories
-accepted 
+accepted
 at those argument positions by the remaining candidates.  At each position,
 select the <type>string</type> category if any candidate accepts that category.
 (This bias towards string
@@ -594,9 +590,8 @@ Otherwise, if all the remaining candidates accept the same type category,
 select that category; otherwise fail because
 the correct choice cannot be deduced without more clues.
 Now discard candidates that do not accept the selected type category.
-Furthermore, if any candidate accepts a preferred type at a given argument
-position, discard candidates that accept non-preferred types for that
-argument.
+Furthermore, if any candidate accepts a preferred type in that category,
+discard candidates that accept non-preferred types for that argument.
 </para>
 </step>
 <step performance="required">