Hi,
Attached is doc_base64_v11.patch
This addresses Tom's concerns. Functions
that operate on both strings and bytea
(e.g. length(text) and length(bytea))
are documented separately, one with
string functions and one with binary
string functions.
In this iteration I have also:
Added a sub-section for the functions
which convert between text and bytea.
Added some index entries.
Provided a link in the hash functions to
the text about why md5() returns text
not bytea.
Regards,
Karl <k...@meme.com>
Free Software: "You don't pay back, you pay forward."
-- Robert A. Heinlein
diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 57a1539506..aac9065e16 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -1432,6 +1432,13 @@
</para>
<para>
+ Functions which convert, both to and from, strings and
+ the <type>bytea</type> type
+ are <link linkend="functions-convertingstringbinary">documented
+ separately</link>.
+ </para>
+
+ <para>
<acronym>SQL</acronym> defines some string functions that use
key words, rather than commas, to separate
arguments. Details are in
@@ -1793,101 +1800,6 @@
</row>
<row>
- <entry>
- <indexterm>
- <primary>convert</primary>
- </indexterm>
- <literal><function>convert(<parameter>string</parameter> <type>bytea</type>,
- <parameter>src_encoding</parameter> <type>name</type>,
- <parameter>dest_encoding</parameter> <type>name</type>)</function></literal>
- </entry>
- <entry><type>bytea</type></entry>
- <entry>
- Convert string to <parameter>dest_encoding</parameter>. The
- original encoding is specified by
- <parameter>src_encoding</parameter>. The
- <parameter>string</parameter> must be valid in this encoding.
- Conversions can be defined by <command>CREATE CONVERSION</command>.
- Also there are some predefined conversions. See <xref
- linkend="conversion-names"/> for available conversions.
- </entry>
- <entry><literal>convert('text_in_utf8', 'UTF8', 'LATIN1')</literal></entry>
- <entry><literal>text_in_utf8</literal> represented in Latin-1
- encoding (ISO 8859-1)</entry>
- </row>
-
- <row>
- <entry>
- <indexterm>
- <primary>convert_from</primary>
- </indexterm>
- <literal><function>convert_from(<parameter>string</parameter> <type>bytea</type>,
- <parameter>src_encoding</parameter> <type>name</type>)</function></literal>
- </entry>
- <entry><type>text</type></entry>
- <entry>
- Convert string to the database encoding. The original encoding
- is specified by <parameter>src_encoding</parameter>. The
- <parameter>string</parameter> must be valid in this encoding.
- </entry>
- <entry><literal>convert_from('text_in_utf8', 'UTF8')</literal></entry>
- <entry><literal>text_in_utf8</literal> represented in the current database encoding</entry>
- </row>
-
- <row>
- <entry>
- <indexterm>
- <primary>convert_to</primary>
- </indexterm>
- <literal><function>convert_to(<parameter>string</parameter> <type>text</type>,
- <parameter>dest_encoding</parameter> <type>name</type>)</function></literal>
- </entry>
- <entry><type>bytea</type></entry>
- <entry>
- Convert string to <parameter>dest_encoding</parameter>.
- </entry>
- <entry><literal>convert_to('some text', 'UTF8')</literal></entry>
- <entry><literal>some text</literal> represented in the UTF8 encoding</entry>
- </row>
-
- <row>
- <entry>
- <indexterm>
- <primary>decode</primary>
- </indexterm>
- <literal><function>decode(<parameter>string</parameter> <type>text</type>,
- <parameter>format</parameter> <type>text</type>)</function></literal>
- </entry>
- <entry><type>bytea</type></entry>
- <entry>
- Decode binary data from textual representation in <parameter>string</parameter>.
- Options for <parameter>format</parameter> are same as in <function>encode</function>.
- </entry>
- <entry><literal>decode('MTIzAAE=', 'base64')</literal></entry>
- <entry><literal>\x3132330001</literal></entry>
- </row>
-
- <row>
- <entry>
- <indexterm>
- <primary>encode</primary>
- </indexterm>
- <literal><function>encode(<parameter>data</parameter> <type>bytea</type>,
- <parameter>format</parameter> <type>text</type>)</function></literal>
- </entry>
- <entry><type>text</type></entry>
- <entry>
- Encode binary data into a textual representation. Supported
- formats are: <literal>base64</literal>, <literal>hex</literal>, <literal>escape</literal>.
- <literal>escape</literal> converts zero bytes and high-bit-set bytes to
- octal sequences (<literal>\</literal><replaceable>nnn</replaceable>) and
- doubles backslashes.
- </entry>
- <entry><literal>encode('123\000\001', 'base64')</literal></entry>
- <entry><literal>MTIzAAE=</literal></entry>
- </row>
-
- <row>
<entry id="format">
<indexterm>
<primary>format</primary>
@@ -1955,19 +1867,6 @@
</row>
<row>
- <entry><literal><function>length(<parameter>string</parameter> <type>bytea</type>,
- <parameter>encoding</parameter> <type>name</type> )</function></literal></entry>
- <entry><type>int</type></entry>
- <entry>
- Number of characters in <parameter>string</parameter> in the given
- <parameter>encoding</parameter>. The <parameter>string</parameter>
- must be valid in this encoding.
- </entry>
- <entry><literal>length('jose', 'UTF8')</literal></entry>
- <entry><literal>4</literal></entry>
- </row>
-
- <row>
<entry>
<indexterm>
<primary>lpad</primary>
@@ -2014,7 +1913,7 @@
</indexterm>
<literal><function>md5(<parameter>string</parameter>)</function></literal>
</entry>
- <entry><type>text</type></entry>
+ <entry><type>text</type><link linkend="functions-hashnote">*</link></entry>
<entry>
Calculates the MD5 hash of <parameter>string</parameter>,
returning the result in hexadecimal
@@ -2333,6 +2232,66 @@
<row>
<entry>
<indexterm>
+ <primary>sha224</primary>
+ </indexterm>
+ <literal><function>sha224(<type>string</type>)</function></literal>
+ </entry>
+ <entry><type>bytea</type><link linkend="functions-hashnote">*</link></entry>
+ <entry>
+ SHA-224 hash
+ </entry>
+ <entry><literal>sha224('abc')</literal></entry>
+ <entry><literal>\x23097d223405d8228642a477bda2​55b32aadbce4bda0b3f7e36c9da7</literal></entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
+ <primary>sha256</primary>
+ </indexterm>
+ <literal><function>sha256(<type>string</type>)</function></literal>
+ </entry>
+ <entry><type>bytea</type><link linkend="functions-hashnote">*</link></entry>
+ <entry>
+ SHA-256 hash
+ </entry>
+ <entry><literal>sha256('abc')</literal></entry>
+ <entry><literal>\xba7816bf8f01cfea414140de5dae2223​b00361a396177a9cb410ff61f20015ad</literal></entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
+ <primary>sha384</primary>
+ </indexterm>
+ <literal><function>sha384(<type>string</type>)</function></literal>
+ </entry>
+ <entry><type>bytea</type><link linkend="functions-hashnote">*</link></entry>
+ <entry>
+ SHA-384 hash
+ </entry>
+ <entry><literal>sha384('abc')</literal></entry>
+ <entry><literal>\xcb00753f45a35e8bb5a03d699ac65007​272c32ab0eded1631a8b605a43ff5bed​8086072ba1e7cc2358baeca134c825a7</literal></entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
+ <primary>sha512</primary>
+ </indexterm>
+ <literal><function>sha512(<type>string</type>)</function></literal>
+ </entry>
+ <entry><type>bytea</type><link linkend="functions-hashnote">*</link></entry>
+ <entry>
+ SHA-512 hash
+ </entry>
+ <entry><literal>sha512('abc')</literal></entry>
+ <entry><literal>\xddaf35a193617abacc417349ae204131​12e6fa4e89a97ea20a9eeee64b55d39a​2192992a274fc1a836ba3c23a3feebbd​454d4423643ce80e2a9ac94fa54ca49f</literal></entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
<primary>split_part</primary>
</indexterm>
<literal><function>split_part(<parameter>string</parameter> <type>text</type>,
@@ -3466,7 +3425,9 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
<para>
This section describes functions and operators for examining and
- manipulating values of type <type>bytea</type>.
+ manipulating values of type <type>bytea</type>, functions which produce
+ strings from other binary inputs, and functions which convert
+ between <type>bytea</type> and strings.
</para>
<para>
@@ -3502,8 +3463,8 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
<tbody>
<row>
- <entry><literal><parameter>string</parameter> <literal>||</literal>
- <parameter>string</parameter></literal></entry>
+ <entry><literal><parameter>bytea</parameter> <literal>||</literal>
+ <parameter>bytea</parameter></literal></entry>
<entry> <type>bytea</type> </entry>
<entry>
String concatenation
@@ -3519,9 +3480,22 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
<row>
<entry>
<indexterm>
+ <primary>bit_length</primary>
+ </indexterm>
+ <literal><function>bit_length(<parameter>bytea</parameter>)</function></literal>
+ </entry>
+ <entry><type>int</type></entry>
+ <entry>Number of bits in binary string</entry>
+ <entry><literal>bit_length('jo\000se':bytea)</literal></entry>
+ <entry><literal>40</literal></entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
<primary>octet_length</primary>
</indexterm>
- <literal><function>octet_length(<parameter>string</parameter>)</function></literal>
+ <literal><function>octet_length(<parameter>bytea</parameter>)</function></literal>
</entry>
<entry><type>int</type></entry>
<entry>Number of bytes in binary string</entry>
@@ -3534,7 +3508,7 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
<indexterm>
<primary>overlay</primary>
</indexterm>
- <literal><function>overlay(<parameter>string</parameter> placing <parameter>string</parameter> from <type>int</type> <optional>for <type>int</type></optional>)</function></literal>
+ <literal><function>overlay(<parameter>bytea</parameter> placing <parameter>bytea</parameter> from <type>int</type> <optional>for <type>int</type></optional>)</function></literal>
</entry>
<entry><type>bytea</type></entry>
<entry>
@@ -3549,7 +3523,7 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
<indexterm>
<primary>position</primary>
</indexterm>
- <literal><function>position(<parameter>substring</parameter> in <parameter>string</parameter>)</function></literal>
+ <literal><function>position(<parameter>byteasubstring</parameter> in <parameter>bytea</parameter>)</function></literal>
</entry>
<entry><type>int</type></entry>
<entry>Location of specified substring</entry>
@@ -3562,7 +3536,7 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
<indexterm>
<primary>substring</primary>
</indexterm>
- <literal><function>substring(<parameter>string</parameter> <optional>from <type>int</type></optional> <optional>for <type>int</type></optional>)</function></literal>
+ <literal><function>substring(<parameter>bytea</parameter> <optional>from <type>int</type></optional> <optional>for <type>int</type></optional>)</function></literal>
</entry>
<entry><type>bytea</type></entry>
<entry>
@@ -3579,7 +3553,7 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
</indexterm>
<literal><function>trim(<optional>both</optional>
<parameter>bytes</parameter> from
- <parameter>string</parameter>)</function></literal>
+ <parameter>bytea</parameter>)</function></literal>
</entry>
<entry><type>bytea</type></entry>
<entry>
@@ -3590,6 +3564,22 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
<entry><literal>trim('\000\001'::bytea from '\000Tom\001'::bytea)</literal></entry>
<entry><literal>Tom</literal></entry>
</row>
+
+ <row>
+ <entry>
+ <literal><function>trim(<optional>leading | trailing
+ | both</optional> <optional>from</optional>
+ <parameter>bytea</parameter>
+ <optional>, <parameter>bytes</parameter></optional>
+ )</function></literal>
+ </entry>
+ <entry><type>bytea</type></entry>
+ <entry>
+ Non-standard syntax for <function>trim()</function>
+ </entry>
+ <entry><literal>trim(both from 'yxTomxx'::bytea, 'xyz'::bytea)</literal></entry>
+ <entry><literal>Tom</literal></entry>
+ </row>
</tbody>
</tgroup>
</table>
@@ -3629,58 +3619,44 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
Remove the longest string containing only bytes appearing in
<parameter>bytes</parameter> from the start and end of
<parameter>string</parameter>
- </entry>
+ </entry>
<entry><literal>btrim('\000trim\001'::bytea, '\000\001'::bytea)</literal></entry>
<entry><literal>trim</literal></entry>
</row>
- <row>
- <entry>
- <indexterm>
- <primary>decode</primary>
- </indexterm>
- <literal><function>decode(<parameter>string</parameter> <type>text</type>,
- <parameter>format</parameter> <type>text</type>)</function></literal>
- </entry>
- <entry><type>bytea</type></entry>
- <entry>
- Decode binary data from textual representation in <parameter>string</parameter>.
- Options for <parameter>format</parameter> are same as in <function>encode</function>.
- </entry>
- <entry><literal>decode('123\000456', 'escape')</literal></entry>
- <entry><literal>123\000456</literal></entry>
- </row>
-
- <row>
- <entry>
+ <row>
+ <entry>
<indexterm>
- <primary>encode</primary>
+ <primary>convert</primary>
</indexterm>
- <literal><function>encode(<parameter>data</parameter> <type>bytea</type>,
- <parameter>format</parameter> <type>text</type>)</function></literal>
- </entry>
- <entry><type>text</type></entry>
- <entry>
- Encode binary data into a textual representation. Supported
- formats are: <literal>base64</literal>, <literal>hex</literal>, <literal>escape</literal>.
- <literal>escape</literal> converts zero bytes and high-bit-set bytes to
- octal sequences (<literal>\</literal><replaceable>nnn</replaceable>) and
- doubles backslashes.
- </entry>
- <entry><literal>encode('123\000456'::bytea, 'escape')</literal></entry>
- <entry><literal>123\000456</literal></entry>
- </row>
+ <literal><function>convert(<parameter>string</parameter> <type>bytea</type>,
+ <parameter>src_encoding</parameter> <type>name</type>,
+ <parameter>dest_encoding</parameter> <type>name</type>)</function></literal>
+ </entry>
+ <entry><type>bytea</type></entry>
+ <entry>
+ Convert string from <parameter>src_encoding</parameter>
+ to <parameter>dest_encoding</parameter>, or raise an error. See
+ <xref linkend="conversion-names"/> for the predefined conversions.
+ The <parameter>string</parameter> must be valid
+ in <parameter>src_encoding</parameter>. New conversions may be
+ defined with <command>CREATE CONVERSION</command>.
+ </entry>
+ <entry><literal>convert('text_in_utf8', 'UTF8', 'LATIN1')</literal></entry>
+ <entry><literal>text_in_utf8</literal> represented in Latin-1
+ encoding (ISO 8859-1)</entry>
+ </row>
<row>
<entry>
<indexterm>
<primary>get_bit</primary>
</indexterm>
- <literal><function>get_bit(<parameter>string</parameter>, <parameter>offset</parameter>)</function></literal>
+ <literal><function>get_bit(<parameter>bytea</parameter>, <parameter>offset</parameter>)</function></literal>
</entry>
<entry><type>int</type></entry>
<entry>
- Extract bit from string
+ Extract bit from binary string
</entry>
<entry><literal>get_bit('Th\000omas'::bytea, 45)</literal></entry>
<entry><literal>1</literal></entry>
@@ -3691,67 +3667,80 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
<indexterm>
<primary>get_byte</primary>
</indexterm>
- <literal><function>get_byte(<parameter>string</parameter>, <parameter>offset</parameter>)</function></literal>
+ <literal><function>get_byte(<parameter>bytea</parameter>, <parameter>offset</parameter>)</function></literal>
</entry>
<entry><type>int</type></entry>
<entry>
- Extract byte from string
+ Extract byte from binary string
</entry>
<entry><literal>get_byte('Th\000omas'::bytea, 4)</literal></entry>
<entry><literal>109</literal></entry>
</row>
- <row>
- <entry>
- <indexterm>
- <primary>length</primary>
- </indexterm>
- <literal><function>length(<parameter>string</parameter>)</function></literal>
- </entry>
- <entry><type>int</type></entry>
- <entry>
- Length of binary string
- <indexterm>
- <primary>binary string</primary>
- <secondary>length</secondary>
- </indexterm>
- <indexterm>
- <primary>length</primary>
- <secondary sortas="binary string">of a binary string</secondary>
- <see>binary strings, length</see>
- </indexterm>
- </entry>
- <entry><literal>length('jo\000se'::bytea)</literal></entry>
- <entry><literal>5</literal></entry>
- </row>
+ <row>
+ <entry>
+ <indexterm>
+ <primary>length</primary>
+ </indexterm>
+ <literal><function>length(<parameter>bytea</parameter>)</function></literal>
+ </entry>
+ <entry><type>int</type></entry>
+ <entry>
+ Length of binary string
+ <indexterm>
+ <primary>binary string</primary>
+ <secondary>length</secondary>
+ </indexterm>
+ <indexterm>
+ <primary>length</primary>
+ <secondary sortas="binary string">of a binary string</secondary>
+ <see>binary strings, length</see>
+ </indexterm>
+ </entry>
+ <entry><literal>length('jo\000se'::bytea)</literal></entry>
+ <entry><literal>5</literal></entry>
+ </row>
- <row>
- <entry>
- <indexterm>
- <primary>md5</primary>
- </indexterm>
- <literal><function>md5(<parameter>string</parameter>)</function></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('Th\000omas'::bytea)</literal></entry>
- <entry><literal>8ab2d3c9689aaf18​b4958c334c82d8b1</literal></entry>
- </row>
+ <row>
+ <entry><literal><function>length(<parameter>string</parameter> <type>bytea</type>,
+ <parameter>encoding</parameter> <type>name</type> )</function></literal></entry>
+ <entry><type>int</type></entry>
+ <entry>
+ Number of characters in <parameter>string</parameter> in the given
+ <parameter>encoding</parameter>. The <parameter>string</parameter>
+ must be valid in this encoding.
+ </entry>
+ <entry><literal>length('jose'::bytea, 'UTF8')</literal></entry>
+ <entry><literal>4</literal></entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
+ <primary>md5</primary>
+ </indexterm>
+ <literal><function>md5(<parameter>string</parameter> <type>bytea</type>)</function></literal>
+ </entry>
+ <entry><type>text</type><link linkend="functions-hashnote">*</link></entry>
+ <entry>
+ Calculates the MD5 hash of <parameter>string</parameter>, returning
+ the result in hexadecimal
+ </entry>
+ <entry><literal>md5('Th\000omas'::bytea)</literal></entry>
+ <entry><literal>8ab2d3c9689aaf18​b4958c334c82d8b1</literal></entry>
+ </row>
<row>
<entry>
<indexterm>
<primary>set_bit</primary>
</indexterm>
- <literal><function>set_bit(<parameter>string</parameter>,
+ <literal><function>set_bit(<parameter>bytea</parameter>,
<parameter>offset</parameter>, <parameter>newvalue</parameter>)</function></literal>
</entry>
<entry><type>bytea</type></entry>
<entry>
- Set bit in string
+ Set bit in binary string
</entry>
<entry><literal>set_bit('Th\000omas'::bytea, 45, 0)</literal></entry>
<entry><literal>Th\000omAs</literal></entry>
@@ -3762,12 +3751,12 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
<indexterm>
<primary>set_byte</primary>
</indexterm>
- <literal><function>set_byte(<parameter>string</parameter>,
+ <literal><function>set_byte(<parameter>bytea</parameter>,
<parameter>offset</parameter>, <parameter>newvalue</parameter>)</function></literal>
</entry>
<entry><type>bytea</type></entry>
<entry>
- Set byte in string
+ Set byte in binary string
</entry>
<entry><literal>set_byte('Th\000omas'::bytea, 4, 64)</literal></entry>
<entry><literal>Th\000o@as</literal></entry>
@@ -3780,11 +3769,11 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
</indexterm>
<literal><function>sha224(<type>bytea</type>)</function></literal>
</entry>
- <entry><type>bytea</type></entry>
+ <entry><type>bytea</type><link linkend="functions-hashnote">*</link></entry>
<entry>
SHA-224 hash
</entry>
- <entry><literal>sha224('abc')</literal></entry>
+ <entry><literal>sha224('abc'::bytea)</literal></entry>
<entry><literal>\x23097d223405d8228642a477bda2​55b32aadbce4bda0b3f7e36c9da7</literal></entry>
</row>
@@ -3795,11 +3784,11 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
</indexterm>
<literal><function>sha256(<type>bytea</type>)</function></literal>
</entry>
- <entry><type>bytea</type></entry>
+ <entry><type>bytea</type><link linkend="functions-hashnote">*</link></entry>
<entry>
SHA-256 hash
</entry>
- <entry><literal>sha256('abc')</literal></entry>
+ <entry><literal>sha256('abc'::bytea)</literal></entry>
<entry><literal>\xba7816bf8f01cfea414140de5dae2223​b00361a396177a9cb410ff61f20015ad</literal></entry>
</row>
@@ -3810,11 +3799,11 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
</indexterm>
<literal><function>sha384(<type>bytea</type>)</function></literal>
</entry>
- <entry><type>bytea</type></entry>
+ <entry><type>bytea</type><link linkend="functions-hashnote">*</link></entry>
<entry>
SHA-384 hash
</entry>
- <entry><literal>sha384('abc')</literal></entry>
+ <entry><literal>sha384('abc'::bytea)</literal></entry>
<entry><literal>\xcb00753f45a35e8bb5a03d699ac65007​272c32ab0eded1631a8b605a43ff5bed​8086072ba1e7cc2358baeca134c825a7</literal></entry>
</row>
@@ -3825,13 +3814,29 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
</indexterm>
<literal><function>sha512(<type>bytea</type>)</function></literal>
</entry>
- <entry><type>bytea</type></entry>
+ <entry><type>bytea</type><link linkend="functions-hashnote">*</link></entry>
<entry>
- SHA-512 hash
+ SHA-512 hash
</entry>
- <entry><literal>sha512('abc')</literal></entry>
+ <entry><literal>sha512('abc'::bytea)</literal></entry>
<entry><literal>\xddaf35a193617abacc417349ae204131​12e6fa4e89a97ea20a9eeee64b55d39a​2192992a274fc1a836ba3c23a3feebbd​454d4423643ce80e2a9ac94fa54ca49f</literal></entry>
</row>
+
+ <row>
+ <entry>
+ <indexterm>
+ <primary>substr</primary>
+ </indexterm>
+ <literal><function>substr(<parameter>bytea</parameter>, <parameter>from</parameter> <type>bytea</type> <optional>, <parameter>count</parameter></optional>)</function></literal>
+ </entry>
+ <entry><type>bytea</type></entry>
+ <entry>
+ Extract substring (same as
+ <literal>substring(<parameter>bytea</parameter> from <parameter>from</parameter> <type>bytea</type> for <parameter>count</parameter>)</literal>)
+ </entry>
+ <entry><literal>substr('alphabet', 3, 2)</literal></entry>
+ <entry><literal>ph</literal></entry>
+ </row>
</tbody>
</tgroup>
</table>
@@ -3844,7 +3849,7 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
the first byte, and bit 15 is the most significant bit of the second byte.
</para>
- <para>
+ <para id="functions-hashnote">
Note that for historic reasons, the function <function>md5</function>
returns a hex-encoded value of type <type>text</type> whereas the SHA-2
functions return type <type>bytea</type>. Use the functions
@@ -3858,6 +3863,210 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
<xref linkend="functions-aggregate"/> and the large object functions
in <xref linkend="lo-funcs"/>.
</para>
+
+ <sect2 id="functions-convertingstringbinary">
+ <title>String to Binary and Binary to String Conversion</title>
+
+ <indexterm>
+ <primary>function</primary>
+ <secondary>converting between string and binary</secondary>
+ </indexterm>
+ <indexterm>
+ <primary>character string</primary>
+ <secondary>converting to binary</secondary>
+ </indexterm>
+ <indexterm>
+ <primary>binary string</primary>
+ <secondary>converting to character string</secondary>
+ </indexterm>
+
+ <table id="functions-binarystringconversions">
+ <title>Binary/String Conversion 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>
+ <indexterm>
+ <primary>convert_from</primary>
+ </indexterm>
+ <literal><function>convert_from(<parameter>string</parameter> <type>bytea</type>,
+ <parameter>src_encoding</parameter> <type>name</type>)</function></literal>
+ </entry>
+ <entry><type>text</type></entry>
+ <entry>
+ Convert string to the database encoding. The original encoding
+ is specified by <parameter>src_encoding</parameter>. The
+ <parameter>string</parameter> must be valid in this encoding. See
+ <xref linkend="conversion-names"/> for available conversions.
+ </entry>
+ <entry><literal>convert_from('text_in_utf8', 'UTF8')</literal></entry>
+ <entry><literal>text_in_utf8</literal> represented in the current database encoding</entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
+ <primary>convert_to</primary>
+ </indexterm>
+ <literal><function>convert_to(<parameter>string</parameter> <type>text</type>,
+ <parameter>dest_encoding</parameter> <type>name</type>)</function></literal>
+ </entry>
+ <entry><type>bytea</type></entry>
+ <entry>
+ Convert string to <parameter>dest_encoding</parameter>. See <xref
+ linkend="conversion-names"/> for available conversions.
+ </entry>
+ <entry><literal>convert_to('some text', 'UTF8')</literal></entry>
+ <entry><literal>some text</literal> represented in the UTF8 encoding</entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
+ <primary>encode</primary>
+ </indexterm>
+ <indexterm>
+ <primary>base64 encoding</primary>
+ </indexterm>
+ <indexterm>
+ <primary>hex encoding</primary>
+ </indexterm>
+ <indexterm>
+ <primary>escape encoding</primary>
+ </indexterm>
+ <literal><function>encode(<parameter>data</parameter> <type>bytea</type>,
+ <parameter>format</parameter> <type>text</type>)</function></literal>
+ </entry>
+ <entry><type>text</type></entry>
+ <entry>
+ Encode binary data into a textual representation. Supported
+ formats are:
+ <link linkend="base64-encoding"><literal>base64</literal></link>,
+ <link linkend="escape-encoding"><literal>escape</literal></link>,
+ <link linkend="hex-encoding"><literal>hex</literal></link>.
+ </entry>
+ <entry><literal>encode('123\000\001', 'base64')</literal></entry>
+ <entry><literal>MTIzAAE=</literal></entry>
+ </row>
+
+ <row>
+ <entry>
+ <indexterm>
+ <primary>decode</primary>
+ </indexterm>
+ <indexterm>
+ <primary>base64 encoding</primary>
+ </indexterm>
+ <literal><function>decode(<parameter>string</parameter> <type>text</type>,
+ <parameter>format</parameter> <type>text</type>)</function></literal>
+ </entry>
+ <entry><type>bytea</type></entry>
+ <entry>
+ Decode binary data from textual representation in <parameter>string</parameter>.
+ <link linkend="encoding-options">Options
+ for <parameter>format</parameter></link> are same as
+ in <function>encode</function>.
+ </entry>
+ <entry><literal>decode('MTIzAAE=', 'base64')</literal></entry>
+ <entry><literal>\x3132330001</literal></entry>
+ </row>
+ </tbody>
+ </tgroup>
+ </table>
+
+ <indexterm>
+ <primary>encode</primary>
+ </indexterm>
+ <indexterm>
+ <primary>decode</primary>
+ </indexterm>
+ <indexterm>
+ <primary>base64 encoding</primary>
+ </indexterm>
+ <indexterm>
+ <primary>hex encoding</primary>
+ </indexterm>
+ <indexterm>
+ <primary>escape encoding</primary>
+ </indexterm>
+
+ <para id="encoding-options">
+ The <function>encode</function> and <function>decode</function> functions
+ support the following encodings:
+
+ <variablelist>
+ <varlistentry id="base64-encoding">
+ <term>base64</term>
+ <listitem>
+ <para>
+ The <literal>base64</literal> encoding is that
+ of <ulink url="https://tools.ietf.org/html/rfc2045#section-6.8";>RFC
+ 2045 Section 6.8</ulink>. As per the RFC, encoded lines are
+ broken at 76 characters. However instead of the MIME CRLF
+ end-of-line marker, only a newline is used for end-of-line.
+ </para>
+ <para>
+ The <function>decode</function> function ignores carriage-return,
+ newline, space, and tab characters. Otherwise, an error is
+ raised when <function>decode</function> is supplied invalid
+ base64 data — including when trailing padding is incorrect.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="escape-encoding">
+ <term>escape</term>
+ <listitem>
+ <para>
+ The <literal>escape</literal> encoding converts zero bytes and
+ high-bit-set bytes to octal sequences
+ (<literal>\</literal><replaceable>nnn</replaceable>) and doubles
+ backslashes. Encoding always produces 4 characters for each
+ high-bit-set input byte.
+ </para>
+ <para>
+ The <function>decode</function> function accepts fewer than three
+ octal digits after a <literal>\</literal> character. An error is
+ raised when <function>decode</function> is supplied a
+ single <literal>\</literal> not followed by an octal digit.
+ </para>
+ </listitem>
+ </varlistentry>
+
+ <varlistentry id="hex-encoding">
+ <term>hex</term>
+ <listitem>
+ <para>
+ The <literal>hex</literal> encoding represents each 4 bits of
+ data as a single hexadecimal digit, <literal>0</literal>
+ through <literal>f</literal>. Encoding outputs
+ the <literal>a</literal>-<literal>f</literal> hex digits in lower
+ case. Because the smallest unit of data is 8 bits there are
+ always an even number of characters returned
+ by <function>encode</function>.
+ </para>
+ <para>
+ The <function>decode</function> function
+ accepts <literal>a</literal>-<literal>f</literal> characters in
+ either upper or lower case. An error is raised
+ when <function>decode</function> is supplied invalid hex data
+ — including when given an odd number of characters.
+ </para>
+ </listitem>
+ </varlistentry>
+ </variablelist>
+ </para>
+ </sect2>
</sect1>
