typeconv.sgml

关系型数据库 Postgresql 6.5.2

<programlisting>
tgl=> SELECT 'abc' || 'def' AS "Unspecified";
Unspecified
-----------
abcdef
(1 row)
</programlisting>
</para>

<para>
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 all arguments for all the candidates are string types. It chooses
the "preferred type" for strings, <type>text</type>, for this query.
</para>

<note>
<para>
If a user defines a new type and defines an operator <quote>||</quote> to work
with it, then this query would no longer succeed as written. The parser would
now have candidate types from two categories, and could not decide which to use.
</para>
</note>
</sect3>

<sect3>
<title>Factorial</title>

<para>
This example illustrates an interesting result. Traditionally, the
factorial operator is defined for integers only. The <productname>Postgres</productname>
operator catalog has only one entry for factorial, taking an integer operand.
If given a non-integer numeric argument, <productname>Postgres</productname>
will try to convert that argument to an integer for evaluation of the
factorial.

<programlisting>
tgl=> select (4.3 !);
?column?
--------
      24
(1 row)
</programlisting>

<note>
<para>
Of course, this leads to a mathematically suspect result,
since in principle the factorial of a non-integer is not defined.
However, the role of a database is not to teach mathematics, but
to be a tool for data manipulation. If a user chooses to take the
factorial of a floating point number, <productname>Postgres</productname>
will try to oblige.
</para>
</note>
</para>
</sect3>
</sect2>
</sect1>

<sect1>
<title>Functions</title>

<procedure>
<title>Function Evaluation</title>

<step performance="required">
<para>
Check for an exact match in the pg_proc system catalog.
</para></step>
<step performance="required">
<para>
Look for the best match.
</para>
<substeps>
<step performance="required">
<para>
Make a list of all functions of the same name with the same number of arguments.
</para></step>
<step performance="required">
<para>
If only one function is in the list, use it if the input types can be coerced,
and throw an error if the types cannot be coerced.
</para></step>
<step performance="required">
<para>
Keep all functions with the most explicit matches for types. Keep all if there
are no explicit matches and move to the next step.
If only one candidate remains, use it if the type can be coerced.
</para></step>
<step performance="required">
<para>
If any input arguments are "unknown", categorize the input candidate arguments as
boolean, numeric, string, geometric, or user-defined. If there is a mix of
categories, or more than one user-defined type, throw an error because
the correct choice cannot be deduced without more clues.
If only one category is present, then assign the "preferred type"
to the input column which had been previously "unknown".
</para></step>
<step performance="required">
<para>
Choose the candidate with the most exact type matches, and which matches
the "preferred type" for each column category from the previous step.
If there is still more than one candidate, or if there are none,
then throw an error.
</para></step>
</substeps>
</step>
</procedure>
<sect2>
<title>Examples</title>

<sect3>
<title>Factorial Function</title>

<para>
There is only one factorial function defined in the pg_proc catalog.
So the following query automatically converts the <type>int2</type> argument
to <type>int4</type>:

<programlisting>
tgl=> select int4fac(int2 '4');
int4fac
-------
     24
(1 row)
</programlisting>

and is actually transformed by the parser to
<programlisting>
tgl=> select int4fac(int4(int2 '4'));
int4fac
-------
     24
(1 row)
</programlisting>
</para>
</sect3>

<sect3>
<title>Substring Function</title>

<para>
There are two <function>substr</function> functions declared in pg_proc. However,
only one takes two arguments, of types <type>text</type> and <type>int4</type>.
</para>

<para>
If called with a string constant of unspecified type, the type is matched up
directly with the only candidate function type:
<programlisting>
tgl=> select substr('1234', 3);
substr
------
    34
(1 row)
</programlisting>
</para>

<para>
If the string is declared to be of type <type>varchar</type>, as might be the case
if it comes from a table, then the parser will try to coerce it to become <type>text</type>:
<programlisting>
tgl=> select substr(varchar '1234', 3);
substr
------
    34
(1 row)
</programlisting>
which is transformed by the parser to become
<programlisting>
tgl=> select substr(text(varchar '1234'), 3);
substr
------
    34
(1 row)
</programlisting>
</para>
<note>
<para>
There are some heuristics in the parser to optimize the relationship between the
<type>char</type>, <type>varchar</type>, and <type>text</type> types.
For this case, <function>substr</function> is called directly with the <type>varchar</type> string
rather than inserting an explicit conversion call.
</para>
</note>

<para>
And, if the function is called with an <type>int4</type>, the parser will
try to convert that to <type>text</type>:
<programlisting>
tgl=> select substr(1234, 3);
substr
------
    34
(1 row)
</programlisting>
actually executes as
<programlisting>
tgl=> select substr(text(1234), 3);
substr
------
    34
(1 row)
</programlisting>
</para>
</sect3>
</sect2>
</sect1>

<sect1>
<title>Query Targets</title>

<procedure>
<title>Target Evaluation</title>

<step performance="required">
<para>
Check for an exact match with the target.
</para></step>
<step performance="required">
<para>
Try to coerce the expression directly to the target type if necessary.
</para></step>

<step performance="required">
<para>
If the target is a fixed-length type (e.g. <type>char</type> or <type>varchar</type>
declared with a length) then try to find a sizing function of the same name
as the type taking two arguments, the first the type name and the second an
integer length.
</para></step>

</procedure>

<sect2>
<title>Examples</title>

<sect3>
<title><type>varchar</type> Storage</title>

<para>
For a target column declared as <type>varchar(4)</type> the following query
ensures that the target is sized correctly:

<programlisting>
tgl=> CREATE TABLE vv (v varchar(4));
CREATE
tgl=> INSERT INTO vv SELECT 'abc' || 'def';
INSERT 392905 1
tgl=> select * from vv;
v
----
abcd
(1 row)
</programlisting>
</para>
</sect3>
</sect2>
</sect1>

<sect1>
<title>UNION Queries</title>

<para>
The UNION construct is somewhat different in that it must match up
possibly dissimilar types to become a single result set.
</para>
<procedure>
<title>UNION Evaluation</title>

<step performance="required">
<para>
Check for identical types for all results.
</para></step>

<step performance="required">
<para>
Coerce each result from the UNION clauses to match the type of the
first SELECT clause or the target column.
</para></step>
</procedure>

<sect2>
<title>Examples</title>

<sect3>
<title>Underspecified Types</title>

<para>
<programlisting>
tgl=> SELECT text 'a' AS "Text" UNION SELECT 'b';
Text
----
a
b
(2 rows)
</programlisting>
</para>
</sect3>

<sect3>
<title>Simple UNION</title>

<para>
<programlisting>
tgl=> SELECT 1.2 AS Float8 UNION SELECT 1;
Float8
------
     1
   1.2
(2 rows)
</programlisting>
</para>
</sect3>

<sect3>
<title>Transposed UNION</title>

<para>
The types of the union are forced to match the types of
the first/top clause in the union:

<programlisting>
tgl=> SELECT 1 AS "All integers"
tgl-> UNION SELECT '2.2'::float4
tgl-> UNION SELECT 3.3;
All integers
------------
           1
           2
           3
(3 rows)
</programlisting>
</para>
<para>
An alternate parser strategy could be to choose the "best" type of the bunch, but
this is more difficult because of the nice recursion technique used in the
parser. However, the "best" type is used when selecting <emphasis>into</emphasis>
a table:

<programlisting>
tgl=> CREATE TABLE ff (f float);
CREATE
tgl=> INSERT INTO ff
tgl-> SELECT 1
tgl-> UNION SELECT '2.2'::float4
tgl-> UNION SELECT 3.3;
INSERT 0 3
tgl=> SELECT f AS "Floating point" from ff;
  Floating point
----------------
               1
2.20000004768372
             3.3
(3 rows)
</programlisting>
</para>
</sect3>
</sect2>
</sect1>
</chapter>