func.sgml

PostgreSQL 8.1.4的源码 适用于Linux下的开源数据库系统

   </indexterm>
   <indexterm>
    <primary>md5</primary>
   </indexterm>
   <indexterm>
    <primary>pg_client_encoding</primary>
   </indexterm>
   <indexterm>
    <primary>quote_ident</primary>
   </indexterm>
   <indexterm>
    <primary>quote_literal</primary>
   </indexterm>
   <indexterm>
    <primary>repeat</primary>
   </indexterm>
   <indexterm>
    <primary>replace</primary>
   </indexterm>
   <indexterm>
    <primary>rpad</primary>
   </indexterm>
   <indexterm>
    <primary>rtrim</primary>
   </indexterm>
   <indexterm>
    <primary>split_part</primary>
   </indexterm>
   <indexterm>
    <primary>strpos</primary>
   </indexterm>
   <indexterm>
    <primary>substr</primary>
   </indexterm>
   <indexterm>
    <primary>to_ascii</primary>
   </indexterm>
   <indexterm>
    <primary>to_hex</primary>
   </indexterm>
   <indexterm>
    <primary>translate</primary>
   </indexterm>

   <table id="functions-string-other">
    <title>Other String Functions</title>
    <tgroup cols="5">
     <thead>
      <row>
       <entry>Function</entry>
       <entry>Return Type</entry>
       <entry>Description</entry>
       <entry>Example</entry>
       <entry>Result</entry>
      </row>
     </thead>

     <tbody>
      <row>
       <entry><literal><function>ascii</function>(<type>text</type>)</literal></entry>
       <entry><type>int</type></entry>
       <entry><acronym>ASCII</acronym> code of the first character of the argument</entry>
       <entry><literal>ascii('x')</literal></entry>
       <entry><literal>120</literal></entry>
      </row>

      <row>
       <entry><literal><function>btrim</function>(<parameter>string</parameter> <type>text</type>
       <optional>, <parameter>characters</parameter> <type>text</type></optional>)</literal></entry>
       <entry><type>text</type></entry>
       <entry>
        Remove the longest string consisting only of characters
        in <parameter>characters</parameter> (a space by default)
        from the start and end of <parameter>string</parameter>
       </entry>
       <entry><literal>btrim('xyxtrimyyx', 'xy')</literal></entry>
       <entry><literal>trim</literal></entry>
      </row>

      <row>
       <entry><literal><function>chr</function>(<type>int</type>)</literal></entry>
       <entry><type>text</type></entry>
       <entry>Character with the given <acronym>ASCII</acronym> code</entry>
       <entry><literal>chr(65)</literal></entry>
       <entry><literal>A</literal></entry>
      </row>

      <row>
       <entry>
        <literal><function>convert</function>(<parameter>string</parameter>
        <type>text</type>,
        <optional><parameter>src_encoding</parameter> <type>name</type>,</optional>
        <parameter>dest_encoding</parameter> <type>name</type>)</literal>
       </entry>
       <entry><type>text</type></entry>
       <entry>
        Convert string to <parameter>dest_encoding</parameter>.
        The original encoding is specified by
        <parameter>src_encoding</parameter>.  If
        <parameter>src_encoding</parameter> is omitted, database
        encoding is assumed.
       </entry>
       <entry><literal>convert( 'text_in_utf8', 'UTF8', 'LATIN1')</literal></entry>
       <entry><literal>text_in_utf8</literal> represented in ISO 8859-1 encoding</entry>
      </row>

      <row>
       <entry>
        <literal><function>decode</function>(<parameter>string</parameter> <type>text</type>,
        <parameter>type</parameter> <type>text</type>)</literal>
       </entry>
       <entry><type>bytea</type></entry>
       <entry>
        Decode binary data from <parameter>string</parameter> previously 
        encoded with <function>encode</>.  Parameter type is same as in <function>encode</>.
       </entry>
       <entry><literal>decode('MTIzAAE=', 'base64')</literal></entry>
       <entry><literal>123\000\001</literal></entry>
      </row>       

      <row>
       <entry>
        <literal><function>encode</function>(<parameter>data</parameter> <type>bytea</type>,
        <parameter>type</parameter> <type>text</type>)</literal>
       </entry>
       <entry><type>text</type></entry>
       <entry>
        Encode binary data to <acronym>ASCII</acronym>-only representation.  Supported
        types are: <literal>base64</>, <literal>hex</>, <literal>escape</>.
       </entry>
       <entry><literal>encode( '123\\000\\001', 'base64')</literal></entry>
       <entry><literal>MTIzAAE=</literal></entry>
      </row>       

      <row>
       <entry><literal><function>initcap</function>(<type>text</type>)</literal></entry>
       <entry><type>text</type></entry>
       <entry>
        Convert the first letter of each word to uppercase and the
        rest to lowercase. Words are sequences of alphanumeric
        characters separated by non-alphanumeric characters.
       </entry>
       <entry><literal>initcap('hi THOMAS')</literal></entry>
       <entry><literal>Hi Thomas</literal></entry>
      </row>

      <row>
       <entry><literal><function>length</function>(<parameter>string</parameter> <type>text</type>)</literal></entry>
       <entry><type>int</type></entry>
       <entry>
        Number of characters in <parameter>string</parameter>
       </entry>
       <entry><literal>length('jose')</literal></entry>
       <entry><literal>4</literal></entry>
      </row>

      <row>
       <entry>
        <literal><function>lpad</function>(<parameter>string</parameter> <type>text</type>,
        <parameter>length</parameter> <type>int</type>
        <optional>, <parameter>fill</parameter> <type>text</type></optional>)</literal>
       </entry>
       <entry><type>text</type></entry>
       <entry>
        Fill up the <parameter>string</parameter> to length
        <parameter>length</parameter> by prepending the characters
        <parameter>fill</parameter> (a space by default).  If the
        <parameter>string</parameter> is already longer than
        <parameter>length</parameter> then it is truncated (on the
        right).
       </entry>
       <entry><literal>lpad('hi', 5, 'xy')</literal></entry>
       <entry><literal>xyxhi</literal></entry>
      </row>

      <row>
       <entry><literal><function>ltrim</function>(<parameter>string</parameter> <type>text</type>
        <optional>, <parameter>characters</parameter> <type>text</type></optional>)</literal>
       </entry>
       <entry><type>text</type></entry>
       <entry>
        Remove the longest string containing only characters from
        <parameter>characters</parameter> (a space by default) from the start of
        <parameter>string</parameter>
       </entry>
       <entry><literal>ltrim('zzzytrim', 'xyz')</literal></entry>
       <entry><literal>trim</literal></entry>
      </row>

      <row>
       <entry><literal><function>md5</function>(<parameter>string</parameter> <type>text</type>)</literal></entry>
       <entry><type>text</type></entry>
       <entry>
        Calculates the MD5 hash of <parameter>string</parameter>,
        returning the result in hexadecimal
       </entry>
       <entry><literal>md5('abc')</literal></entry>
       <entry><literal>900150983cd24fb0 d6963f7d28e17f72</literal></entry>
      </row>

      <row>
       <entry><literal><function>pg_client_encoding</function>()</literal></entry>
       <entry><type>name</type></entry>
       <entry>
        Current client encoding name
       </entry>
       <entry><literal>pg_client_encoding()</literal></entry>
       <entry><literal>SQL_ASCII</literal></entry>
      </row>

      <row>
       <entry><literal><function>quote_ident</function>(<parameter>string</parameter> text)</literal></entry>
       <entry><type>text</type></entry>
       <entry>
        Return the given string suitably quoted to be used as an identifier
        in an <acronym>SQL</acronym> statement string.
        Quotes are added only if necessary (i.e., if the string contains
        non-identifier characters or would be case-folded).
        Embedded quotes are properly doubled.
       </entry>
       <entry><literal>quote_ident('Foo bar')</literal></entry>
       <entry><literal>"Foo bar"</literal></entry>
      </row>

      <row>
       <entry><literal><function>quote_literal</function>(<parameter>string</parameter> text)</literal></entry>
       <entry><type>text</type></entry>
       <entry>
        Return the given string suitably quoted to be used as a string literal
        in an <acronym>SQL</acronym> statement string.
        Embedded quotes and backslashes are properly doubled.
       </entry>
       <entry><literal>quote_literal( 'O\'Reilly')</literal></entry>
       <entry><literal>'O''Reilly'</literal></entry>
      </row>

      <row>
       <entry><literal><function>repeat</function>(<parameter>string</parameter> <type>text</type>, <parameter>number</parameter> <type>int</type>)</literal></entry>
       <entry><type>text</type></entry>
       <entry>Repeat <parameter>string</parameter> the specified
       <parameter>number</parameter> of times</entry>
       <entry><literal>repeat('Pg', 4)</literal></entry>
       <entry><literal>PgPgPgPg</literal></entry>
      </row>

      <row>
       <entry><literal><function>replace</function>(<parameter>string</parameter> <type>text</type>,
       <parameter>from</parameter> <type>text</type>,
       <parameter>to</parameter> <type>text</type>)</literal></entry>
       <entry><type>text</type></entry>
       <entry>Replace all occurrences in <parameter>string</parameter> of substring
        <parameter>from</parameter> with substring <parameter>to</parameter>
       </entry>
       <entry><literal>replace( 'abcdefabcdef', 'cd', 'XX')</literal></entry>
       <entry><literal>abXXefabXXef</literal></entry>
      </row>

      <row>
       <entry>
        <literal><function>rpad</function>(<parameter>string</parameter> <type>text</type>,
        <parameter>length</parameter> <type>int</type>
        <optional>, <parameter>fill</parameter> <type>text</type></optional>)</literal>
       </entry>
       <entry><type>text</type></entry>
       <entry>
        Fill up the <parameter>string</parameter> to length
        <parameter>length</parameter> by appending the characters
        <parameter>fill</parameter> (a space by default).  If the
        <parameter>string</parameter> is already longer than
        <parameter>length</parameter> then it is truncated.
       </entry>
       <entry><literal>rpad('hi', 5, 'xy')</literal></entry>
       <entry><literal>hixyx</literal></entry>
      </row>

      <row>
       <entry><literal><function>rtrim</function>(<parameter>string</parameter> <type>text</type>
        <optional>, <parameter>characters</parameter> <type>text</type></optional>)</literal>
       </entry>
       <entry><type>text</type></entry>
       <entry>
        Remove the longest string containing only characters from
        <parameter>characters</parameter> (a space by default) from the end of
        <parameter>string</parameter>
       </entry>
       <entry><literal>rtrim('trimxxxx', 'x')</literal></entry>
       <entry><literal>trim</literal></entry>
      </row>

      <row>
       <entry><literal><function>split_part</function>(<parameter>string</parameter> <type>text</type>,
       <parameter>delimiter</parameter> <type>text</type>,
       <parameter>field</parameter> <type>int</type>)</literal></entry>
       <entry><type>text</type></entry>
       <entry>Split <parameter>string</parameter> on <parameter>delimiter</parameter>
        and return the given field (counting from one)
       </entry>
       <entry><literal>split_part('abc~@~def~@~ghi', '~@~', 2)</literal></entry>
       <entry><literal>def</literal></entry>
      </row>

      <row>
       <entry><literal><function>strpos</function>(<parameter>string</parameter>, <parameter>substring</parameter>)</literal></entry>
       <entry><type>int</type></entry>
       <entry>
        Location of specified substring (same as
        <literal>position(<parameter>substring</parameter> in
         <parameter>string</parameter>)</literal>, but note the reversed
        argument order)
       </entry>
       <entry><literal>strpos('high', 'ig')</literal></entry>
       <entry><literal>2</literal></entry>
      </row>

      <row>
       <entry><literal><function>substr</function>(<parameter>string</parameter>, <parameter>from</parameter> <optional>, <parameter>count</parameter></optional>)</literal></entry>
       <entry><type>text</type></entry>
       <entry>
        Extract substring (same as
        <literal>substring(<parameter>string</parameter> from <parameter>from</parameter> for <parameter>count</parameter>)</literal>)
       </entry>
       <entry><literal>substr('alphabet', 3, 2)</literal></entry>
       <entry><literal>ph</literal></entry>
      </row>

      <row>
       <entry><literal><function>to_ascii</function>(<type>text</type>
        <optional>, <parameter>encoding</parameter></optional>)</literal></entry>
       <entry><type>text</type></entry>

       <entry>
       Convert <parameter>text</parameter> to <acronym>ASCII</acronym> from another encoding
       <footnote>
        <para>
         The <function>to_ascii</function> function supports conversion from
         <literal>LATIN1</>, <literal>LATIN2</>, <literal>LATIN9</>,
         and <literal>WIN1250</> encodings only.
        </para>
       </footnote>
       </entry>

       <entry><literal>to_ascii('Karel')</literal></entry>
       <entry><literal>Karel</literal></entry>
      </row>

      <row>
       <entry><literal><function>to_hex</function>(<parameter>number</parameter> <type>int</type>
       or <type>bigint</type>)</literal></entry>
       <entry><type>text</type></entry>
       <entry>Convert <parameter>number</parameter> to its equivalent hexadecimal
        representation
       </entry>
       <entry><literal>to_hex(2147483647)</literal></entry>
       <entry><literal>7fffffff</literal></entry>
      </row>

      <row>
       <entry>
        <literal><function>translate</function>(<parameter>string</parameter>
        <type>text</type>,
        <parameter>from</parameter> <type>text</type>,
        <parameter>to</parameter> <type>text</type>)</literal>
       </entry>
       <entry><type>text</type></entry>
       <entry>
        Any character in <parameter>string</parameter> that matches a
        character in the <parameter>from</parameter> set is replaced by
        the corresponding character in the <parameter>to</parameter>
        set
       </entry>
       <entry><literal>translate('12345', '14', 'ax')</literal></entry>
       <entry><literal>a23x5</literal></entry>
      </row>       
      
     </tbody>
    </tgroup>
   </table>


   <table id="conversion-names">
    <title>Built-in Conversions</title>
    <tgroup cols="3">
     <thead>
      <row>
       <entry>Conversion Name
        <footnote>
         <para>
          The conversion names follow a standard naming scheme: The
          official name of the source encoding with all
          non-alphanumeric characters replaced by underscores followed
          by <literal>_to_</literal> followed by the equally processed
          destination encoding name. Therefore the names might deviate
          from the customary encoding names.
         </para>