File: typeconv2564.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 (379 lines) | stat: -rw-r--r-- 7,066 bytes
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML
><HEAD
><TITLE
>Operators</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="UP"
TITLE="Type Conversion"
HREF="typeconv.html"><LINK
REL="PREVIOUS"
TITLE="Type Conversion"
HREF="typeconv.html"><LINK
REL="NEXT"
TITLE="Functions"
HREF="typeconv2629.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="typeconv.html"
>Prev</A
></TD
><TD
WIDTH="80%"
ALIGN="center"
VALIGN="bottom"
>Chapter 8. Type Conversion</TD
><TD
WIDTH="10%"
ALIGN="right"
VALIGN="bottom"
><A
HREF="typeconv2629.html"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><H1
CLASS="SECT1"
><A
NAME="AEN2564"
>Operators</A
></H1
><H2
CLASS="SECT2"
><A
NAME="AEN2566"
>Conversion Procedure</A
></H2
><P
></P
><DIV
CLASS="PROCEDURE"
><P
><B
>Operator Evaluation</B
></P
><P
></P
><OL
TYPE="1"
><LI
><P
>Check for an exact match in the pg_operator system catalog.&#13;</P
><OL
CLASS="SUBSTEPS"
TYPE="a"
><LI
><P
>If one argument of a binary operator is <SPAN
CLASS="TYPE"
>unknown</SPAN
>,
then assume it is the same type as the other argument.&#13;</P
></LI
><LI
><P
>Reverse the arguments, and look for an exact match with an operator which
points to itself as being commutative.
If found, then reverse the arguments in the parse tree and use this operator.&#13;</P
></LI
></OL
></LI
><LI
><P
>Look for the best match.&#13;</P
><OL
CLASS="SUBSTEPS"
TYPE="a"
><LI
><P
>Make a list of all operators of the same name.&#13;</P
></LI
><LI
><P
>If only one operator is in the list, use it if the input type can be coerced,
and throw an error if the type cannot be coerced.&#13;</P
></LI
><LI
><P
>Keep all operators 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.&#13;</P
></LI
><LI
><P
>If any input arguments are "unknown", categorize the input candidates 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".&#13;</P
></LI
><LI
><P
>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.</P
></LI
></OL
></LI
></OL
></DIV
><H2
CLASS="SECT2"
><A
NAME="AEN2593"
>Examples</A
></H2
><H3
CLASS="SECT3"
><A
NAME="AEN2595"
>Exponentiation Operator</A
></H3
><P
>There is only one exponentiation
operator defined in the catalog, and it takes <SPAN
CLASS="TYPE"
>float8</SPAN
> arguments.
The scanner assigns an initial type of <SPAN
CLASS="TYPE"
>int4</SPAN
> to both arguments
of this query expression:
<PRE
CLASS="PROGRAMLISTING"
>tgl=&#62; select 2 ^ 3 AS "Exp";
Exp
---
  8
(1 row)</PRE
>

So the parser does a type conversion on both operands and the query
is equivalent to

<PRE
CLASS="PROGRAMLISTING"
>tgl=&#62; select float8(2) ^ float8(3) AS "Exp";
Exp
---
  8
(1 row)</PRE
>

or

<PRE
CLASS="PROGRAMLISTING"
>tgl=&#62; select 2.0 ^ 3.0 AS "Exp";
Exp
---
  8
(1 row)</PRE
>

<BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Note: </B
>This last form has the least overhead, since no functions are called to do
implicit type conversion. This is not an issue for small queries, but may
have an impact on the performance of queries involving large tables.</P
></BLOCKQUOTE
>&#13;</P
><H3
CLASS="SECT3"
><A
NAME="AEN2605"
>String Concatenation</A
></H3
><P
>A string-like syntax is used for working with string types as well as for
working with complex extended types.
Strings with unspecified type are matched with likely operator candidates.&#13;</P
><P
>One unspecified argument:
<PRE
CLASS="PROGRAMLISTING"
>tgl=&#62; SELECT text 'abc' || 'def' AS "Text and Unknown";
Text and Unknown
----------------
abcdef
(1 row)</PRE
>&#13;</P
><P
>In this case the parser looks to see if there is an operator taking <SPAN
CLASS="TYPE"
>text</SPAN
>
for both arguments. Since there is, it assumes that the second argument should
be interpreted as of type <SPAN
CLASS="TYPE"
>text</SPAN
>.&#13;</P
><P
>Concatenation on unspecified types:
<PRE
CLASS="PROGRAMLISTING"
>tgl=&#62; SELECT 'abc' || 'def' AS "Unspecified";
Unspecified
-----------
abcdef
(1 row)</PRE
>&#13;</P
><P
>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, <SPAN
CLASS="TYPE"
>text</SPAN
>, for this query.

<BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Note: </B
>If a user defines a new type and defines an operator &#8220;||&#8221; 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.</P
></BLOCKQUOTE
>&#13;</P
><H3
CLASS="SECT3"
><A
NAME="AEN2620"
>Factorial</A
></H3
><P
>This example illustrates an interesting result. Traditionally, the
factorial operator is defined for integers only. The <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
>
operator catalog has only one entry for factorial, taking an integer operand.
If given a non-integer numeric argument, <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
>
will try to convert that argument to an integer for evaluation of the
factorial.

<PRE
CLASS="PROGRAMLISTING"
>tgl=&#62; select (4.3 !);
?column?
--------
      24
(1 row)</PRE
>

<BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Note: </B
>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, <SPAN
CLASS="PRODUCTNAME"
>Postgres</SPAN
>
will try to oblige.</P
></BLOCKQUOTE
>&#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="typeconv.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="typeconv2629.html"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Type Conversion</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="typeconv.html"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Functions</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>