File: typeconv2629.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 (332 lines) | stat: -rw-r--r-- 5,491 bytes
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML
><HEAD
><TITLE
>Functions</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="Operators"
HREF="typeconv2564.html"><LINK
REL="NEXT"
TITLE="Query Targets"
HREF="typeconv2683.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="typeconv2564.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="typeconv2683.html"
>Next</A
></TD
></TR
></TABLE
><HR
ALIGN="LEFT"
WIDTH="100%"></DIV
><H1
CLASS="SECT1"
><A
NAME="AEN2629"
>Functions</A
></H1
><P
></P
><DIV
CLASS="PROCEDURE"
><P
><B
>Function Evaluation</B
></P
><OL
TYPE="1"
><LI
><P
>Check for an exact match in the pg_proc system catalog.&#13;</P
></LI
><LI
><P
>Look for the best match.&#13;</P
><OL
CLASS="SUBSTEPS"
TYPE="a"
><LI
><P
>Make a list of all functions of the same name with the same number of arguments.&#13;</P
></LI
><LI
><P
>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.&#13;</P
></LI
><LI
><P
>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.&#13;</P
></LI
><LI
><P
>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".&#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="AEN2649"
>Examples</A
></H2
><H3
CLASS="SECT3"
><A
NAME="AEN2651"
>Factorial Function</A
></H3
><P
>There is only one factorial function defined in the pg_proc catalog.
So the following query automatically converts the <SPAN
CLASS="TYPE"
>int2</SPAN
> argument
to <SPAN
CLASS="TYPE"
>int4</SPAN
>:

<PRE
CLASS="PROGRAMLISTING"
>tgl=&#62; select int4fac(int2 '4');
int4fac
-------
     24
(1 row)</PRE
>

and is actually transformed by the parser to
<PRE
CLASS="PROGRAMLISTING"
>tgl=&#62; select int4fac(int4(int2 '4'));
int4fac
-------
     24
(1 row)</PRE
>&#13;</P
><H3
CLASS="SECT3"
><A
NAME="AEN2658"
>Substring Function</A
></H3
><P
>There are two <TT
CLASS="FUNCTION"
>substr</TT
> functions declared in pg_proc. However,
only one takes two arguments, of types <SPAN
CLASS="TYPE"
>text</SPAN
> and <SPAN
CLASS="TYPE"
>int4</SPAN
>.&#13;</P
><P
>If called with a string constant of unspecified type, the type is matched up
directly with the only candidate function type:
<PRE
CLASS="PROGRAMLISTING"
>tgl=&#62; select substr('1234', 3);
substr
------
    34
(1 row)</PRE
>&#13;</P
><P
>If the string is declared to be of type <SPAN
CLASS="TYPE"
>varchar</SPAN
>, as might be the case
if it comes from a table, then the parser will try to coerce it to become <SPAN
CLASS="TYPE"
>text</SPAN
>:
<PRE
CLASS="PROGRAMLISTING"
>tgl=&#62; select substr(varchar '1234', 3);
substr
------
    34
(1 row)</PRE
>
which is transformed by the parser to become
<PRE
CLASS="PROGRAMLISTING"
>tgl=&#62; select substr(text(varchar '1234'), 3);
substr
------
    34
(1 row)</PRE
>
<BLOCKQUOTE
CLASS="NOTE"
><P
><B
>Note: </B
>There are some heuristics in the parser to optimize the relationship between the
<SPAN
CLASS="TYPE"
>char</SPAN
>, <SPAN
CLASS="TYPE"
>varchar</SPAN
>, and <SPAN
CLASS="TYPE"
>text</SPAN
> types.
For this case, <TT
CLASS="FUNCTION"
>substr</TT
> is called directly with the <SPAN
CLASS="TYPE"
>varchar</SPAN
> string
rather than inserting an explicit conversion call.</P
></BLOCKQUOTE
>&#13;</P
><P
>And, if the function is called with an <SPAN
CLASS="TYPE"
>int4</SPAN
>, the parser will
try to convert that to <SPAN
CLASS="TYPE"
>text</SPAN
>:
<PRE
CLASS="PROGRAMLISTING"
>tgl=&#62; select substr(1234, 3);
substr
------
    34
(1 row)</PRE
>
actually executes as
<PRE
CLASS="PROGRAMLISTING"
>tgl=&#62; select substr(text(1234), 3);
substr
------
    34
(1 row)</PRE
>&#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="typeconv2564.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="typeconv2683.html"
>Next</A
></TD
></TR
><TR
><TD
WIDTH="33%"
ALIGN="left"
VALIGN="top"
>Operators</TD
><TD
WIDTH="34%"
ALIGN="center"
VALIGN="top"
><A
HREF="typeconv.html"
>Up</A
></TD
><TD
WIDTH="33%"
ALIGN="right"
VALIGN="top"
>Query Targets</TD
></TR
></TABLE
></DIV
></BODY
></HTML
>