File: typeconv.html

package info (click to toggle)
mpsql 2.1-2
links: PTS
area: non-free
in suites: potato
size: 3,528 kB
ctags: 4,886
sloc: ansic: 35,184; makefile: 3,761; sh: 44
file content (410 lines) | stat: -rw-r--r-- 9,196 bytes
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML
><HEAD
><TITLE
>Type Conversion</TITLE
><META
NAME="GENERATOR"
CONTENT="Modular DocBook HTML Stylesheet version 1.19"><LINK
REL="HOME"
TITLE="PostgreSQL User's Guide"
HREF="user.html"><LINK
REL="PREVIOUS"
TITLE="IP V4 Functions"
HREF="functions2446.html"><LINK
REL="NEXT"
TITLE="Operators"
HREF="typeconv2564.html"></HEAD
><BODY
BGCOLOR="#FFFFFF"
TEXT="#000000"
><DIV
CLASS="NAVHEADER"
><TABLE
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TH
COLSPAN="3"
ALIGN="center"
>PostgreSQL User's Guide</TH
></TR
><TR
><TD
WIDTH="10%"
ALIGN="left"
VALIGN="bottom"
><A
HREF="functions2446.html"
>Prev</A
></TD
><TD
WIDTH="80%"
ALIGN="center"
VALIGN="bottom"
></TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="bottom"
><A
HREF="typeconv2564.html"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><H1
><A
NAME="TYPECONV"
>Chapter 8. Type Conversion</A
></H1
><DIV
CLASS="TOC"
><DL
><DT
><B
>Table of Contents</B
></DT
><DT
><A
HREF="typeconv.html#AEN2501"
>Overview</A
></DT
><DT
><A
HREF="typeconv2564.html"
>Operators</A
></DT
><DT
><A
HREF="typeconv2629.html"
>Functions</A
></DT
><DT
><A
HREF="typeconv2683.html"
>Query Targets</A
></DT
><DT
><A
HREF="typeconv2704.html"
>UNION Queries</A
></DT
></DL
></DIV
><P
><SPAN
CLASS="ACRONYM"
>SQL</SPAN
> queries can, intentionally or not, require
mixing of different data types in the same expression. 
<SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> has extensive facilities for
evaluating mixed-type expressions.&#13;</P
><P
>In many cases a user will not need
to understand the details of the type conversion mechanism.
However, the implicit conversions done by <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
>
can affect the apparent results of a query, and these results
can be tailored by a user or programmer
using <I
CLASS="EMPHASIS"
>explicit</I
> type coersion.&#13;</P
><P
>This chapter introduces the <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
>
 type conversion mechanisms and conventions.
Refer to the relevant sections in the User's Guide and Programmer's Guide
for more information on specific data types and allowed functions and operators.&#13;</P
><P
>The Programmer's Guide has more details on the exact algorithms used for
implicit type conversion and coersion.&#13;</P
><H1
CLASS="SECT1"
><A
NAME="AEN2501"
>Overview</A
></H1
><P
><SPAN
CLASS="ACRONYM"
>SQL</SPAN
> is a strongly typed language. That is, every data item
has an associated data type which determines its behavior and allowed usage.
<SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> has an extensible type system which is
much more general and flexible than other <SPAN
CLASS="ACRONYM"
>RDBMS</SPAN
> implementations.
Hence, most type conversion behavior in <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
>
should be governed by general rules rather than by ad-hoc heuristics to allow
mixed-type expressions to be meaningful, even with user-defined types.&#13;</P
><P
>The <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> scanner/parser decodes lexical elements
into only five fundamental categories: integers, floats, strings, names, and keywords.
Most extended types are first tokenized into strings. The <SPAN
CLASS="ACRONYM"
>SQL</SPAN
>
language definition allows specifying type names with strings, and this mechanism
is used by <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
>
to start the parser down the correct path. For example, the query

<PRE
CLASS="PROGRAMLISTING"
>tgl=&#62; SELECT text 'Origin' AS "Label", point '(0,0)' AS "Value";
Label |Value
------+-----
Origin|(0,0)
(1 row)</PRE
>

has two strings, of type <SPAN
CLASS="TYPE"
>text</SPAN
> and <SPAN
CLASS="TYPE"
>point</SPAN
>.
If a type is not specified, then the placeholder type <SPAN
CLASS="TYPE"
>unknown</SPAN
>
is assigned initially, to be resolved in later stages as described below.&#13;</P
><P
>There are four fundamental <SPAN
CLASS="ACRONYM"
>SQL</SPAN
> constructs requiring
distinct type conversion rules in the <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
>
parser:

<P
></P
></P><DL
><DT
>Operators</DT
><DD
><P
><SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> allows expressions with
left- and right-unary (one argument) operators,
as well as binary (two argument) operators.</P
></DD
><DT
>Function calls</DT
><DD
><P
>Much of the <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> type system is built around a rich set of
functions. Function calls have one or more arguments which, for any specific query,
must be matched to the functions available in the system catalog.</P
></DD
><DT
>Query targets</DT
><DD
><P
><SPAN
CLASS="ACRONYM"
>SQL</SPAN
> INSERT statements place the results of query into a table. The expressions
in the query must be matched up with, and perhaps converted to, the target columns of the insert.</P
></DD
><DT
>UNION queries</DT
><DD
><P
>Since all select results from a UNION SELECT statement must appear in a single set of columns, the types
of each SELECT clause must be matched up and converted to a uniform set.</P
></DD
></DL
><P>&#13;</P
><P
>Many of the general type conversion rules use simple conventions built on
the <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> function and operator system tables.
There are some heuristics included in the conversion rules to better support
conventions for the <SPAN
CLASS="ACRONYM"
>SQL92</SPAN
> standard native types such as
<SPAN
CLASS="TYPE"
>smallint</SPAN
>, <SPAN
CLASS="TYPE"
>integer</SPAN
>, and <SPAN
CLASS="TYPE"
>float</SPAN
>.&#13;</P
><P
>The <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
> parser uses the convention that all
type conversion functions take a single argument of the source type and are
named with the same name as the target type. Any function meeting this
criteria is considered to be a valid conversion function, and may be used
by the parser as such. This simple assumption gives the parser the power
to explore type conversion possibilities without hardcoding, allowing
extended user-defined types to use these same features transparently.&#13;</P
><P
>An additional heuristic is provided in the parser to allow better guesses
at proper behavior for <SPAN
CLASS="ACRONYM"
>SQL</SPAN
> standard types. There are
five categories of types defined: boolean, string, numeric, geometric,
and user-defined. Each category, with the exception of user-defined, has
a "preferred type" which is used to resolve ambiguities in candidates.
Each "user-defined" type is its own "preferred type", so ambiguous
expressions (those with multiple candidate parsing solutions)
with only one user-defined type can resolve to a single best choice, while those with
multiple user-defined types will remain ambiguous and throw an error.&#13;</P
><P
>Ambiguous expressions which have candidate solutions within only one type category are
likely to resolve, while ambiguous expressions with candidates spanning multiple
categories are likely to throw an error and ask for clarification from the user.&#13;</P
><H2
CLASS="SECT2"
><A
NAME="AEN2550"
>Guidelines</A
></H2
><P
>All type conversion rules are designed with several principles in mind:

<P
></P
></P><UL
COMPACT="COMPACT"
><LI
STYLE="list-style-type: disc"
><P
>Implicit conversions should never have suprising or unpredictable outcomes.&#13;</P
></LI
><LI
STYLE="list-style-type: disc"
><P
>User-defined types, of which the parser has no apriori knowledge, should be
"higher" in the type heirarchy. In mixed-type expressions, native types shall always
be converted to a user-defined type (of course, only if conversion is necessary).&#13;</P
></LI
><LI
STYLE="list-style-type: disc"
><P
>User-defined types are not related. Currently, <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
>
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
in the catalog.&#13;</P
></LI
><LI
STYLE="list-style-type: disc"
><P
>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
without spending extra time in the parser and without introducing unnecessary implicit conversion
functions into the query.&#13;</P
><P
>Additionally, if a query usually requires an implicit conversion for a function, and
if then the user defines an explicit function with the correct argument types, the parser
should use this new function and will no longer do the implicit conversion using the old function.</P
></LI
></UL
><P>&#13;</P
><DIV
CLASS="NAVFOOTER"
><HR
ALIGN="LEFT"
WIDTH="100%"><TABLE
WIDTH="100%"
BORDER="0"
CELLPADDING="0"
CELLSPACING="0"
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
><A
HREF="functions2446.html"
>Prev</A
></TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="user.html"
>Home</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
><A
HREF="typeconv2564.html"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>IP V4 Functions</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
>;</TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Operators</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>