On Thu, 16 Jan 2020 14:41:33 +0100 (CET)
Fabien COELHO <[email protected]> wrote:
> Some comments about v13:
>
> The note about get_byte reads:
>
> get_byte and set_byte number the first byte of a binary string as
> byte 0. get_bit and set_bit number bits from the right within each
> byte; for example bit 0 is the least significant bit of the first
> byte, and bit 15 is the most significant bit of the second byte.
>
> The two sentences starts with a lower case letter, which looks
> strange to me. I'd suggest to put "Functions" at the beginning of the
> sentences:
>
> Functions get_byte and set_byte number the first byte of a binary
> string as byte 0. Functions get_bit and set_bit number bits from the
> right within each byte; for example bit 0 is the least significant
> bit of the first byte, and bit 15 is the most significant bit of the
> second byte.
Excellent suggestion, done.
> The note about hash provides an example for getting the hex
> representation out of sha*. I'd add an exemple to get the bytea
> representation from md5, eg "DECODE(MD5('hello world'), 'hex')"…
Ok. Done.
> Maybe the encode/decode in the note could be linked to the function
> description? Well, they are just after, maybe it is not very useful.
Can't hurt? Done.
Patch attached: doc_base64_v14.patch
Regards,
Karl <[email protected]>
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 72072e7545..c075872364 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -1459,6 +1459,13 @@
natively for the bit-string types.
</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
@@ -1820,101 +1827,6 @@
<entry><literal>abcde,2,22</literal></entry>
</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>
@@ -1982,19 +1894,6 @@
<entry><literal>4</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', 'UTF8')</literal></entry>
- <entry><literal>4</literal></entry>
- </row>
-
<row>
<entry>
<indexterm>
@@ -2044,8 +1943,8 @@
</entry>
<entry><type>text</type></entry>
<entry>
- Calculates the MD5 hash of <parameter>string</parameter>,
- returning the result in hexadecimal
+ Calculates the MD5 <link linkend="functions-hashnote">hash</link>
+ of <parameter>string</parameter>, returning the result in hexadecimal
</entry>
<entry><literal>md5('abc')</literal></entry>
<entry><literal>900150983cd24fb0 d6963f7d28e17f72</literal></entry>
@@ -2358,6 +2257,66 @@
<entry><literal>test</literal></entry>
</row>
+ <row>
+ <entry>
+ <indexterm>
+ <primary>sha224</primary>
+ </indexterm>
+ <literal><function>sha224(<type>string</type>)</function></literal>
+ </entry>
+ <entry><type>bytea</type></entry>
+ <entry>
+ SHA-224 <link linkend="functions-hashnote">hash</link>
+ </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></entry>
+ <entry>
+ SHA-256 <link linkend="functions-hashnote">hash</link>
+ </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></entry>
+ <entry>
+ SHA-384 <link linkend="functions-hashnote">hash</link>
+ </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></entry>
+ <entry>
+ SHA-512 <link linkend="functions-hashnote">hash</link>
+ </entry>
+ <entry><literal>sha512('abc')</literal></entry>
+ <entry><literal>\xddaf35a193617abacc417349ae204131​12e6fa4e89a97ea20a9eeee64b55d39a​2192992a274fc1a836ba3c23a3feebbd​454d4423643ce80e2a9ac94fa54ca49f</literal></entry>
+ </row>
+
<row>
<entry>
<indexterm>
@@ -3494,7 +3453,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>
@@ -3530,11 +3491,11 @@ 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>bytes</parameter> <literal>||</literal>
+ <parameter>bytes</parameter></literal></entry>
<entry> <type>bytea</type> </entry>
<entry>
- String concatenation
+ Bytea concatenation
<indexterm>
<primary>binary string</primary>
<secondary>concatenation</secondary>
@@ -3544,12 +3505,25 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
<entry><literal>\\Post'gres\000</literal></entry>
</row>
+ <row>
+ <entry>
+ <indexterm>
+ <primary>bit_length</primary>
+ </indexterm>
+ <literal><function>bit_length(<parameter>bytes</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>bytes</parameter>)</function></literal>
</entry>
<entry><type>int</type></entry>
<entry>Number of bytes in binary string</entry>
@@ -3562,7 +3536,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>bytes</parameter> placing <parameter>bytes</parameter> from <type>int</type> <optional>for <type>int</type></optional>)</function></literal>
</entry>
<entry><type>bytea</type></entry>
<entry>
@@ -3577,7 +3551,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>bytesubstring</parameter> in <parameter>bytes</parameter>)</function></literal>
</entry>
<entry><type>int</type></entry>
<entry>Location of specified substring</entry>
@@ -3590,7 +3564,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>bytes</parameter> <optional>from <type>int</type></optional> <optional>for <type>int</type></optional>)</function></literal>
</entry>
<entry><type>bytea</type></entry>
<entry>
@@ -3606,18 +3580,34 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
<primary>trim</primary>
</indexterm>
<literal><function>trim(<optional>both</optional>
- <parameter>bytes</parameter> from
- <parameter>string</parameter>)</function></literal>
+ <parameter>bytesremoved</parameter> from
+ <parameter>bytes</parameter>)</function></literal>
</entry>
<entry><type>bytea</type></entry>
<entry>
Remove the longest string containing only bytes appearing in
- <parameter>bytes</parameter> from the start
- and end of <parameter>string</parameter>
+ <parameter>bytesremoved</parameter> from the start
+ and end of <parameter>bytes</parameter>
</entry>
<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>bytes</parameter>
+ <optional>, <parameter>bytesremoved</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>
@@ -3649,66 +3639,53 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
<indexterm>
<primary>btrim</primary>
</indexterm>
- <literal><function>btrim(<parameter>string</parameter>
- <type>bytea</type>, <parameter>bytes</parameter> <type>bytea</type>)</function></literal>
+ <literal><function>btrim(<parameter>bytes</parameter>
+ <type>bytea</type>, <parameter>bytesremoved</parameter> <type>bytea</type>)</function></literal>
</entry>
<entry><type>bytea</type></entry>
<entry>
Remove the longest string containing only bytes appearing in
- <parameter>bytes</parameter> from the start and end of
- <parameter>string</parameter>
- </entry>
+ <parameter>bytesremoved</parameter> from the start and end of
+ <parameter>bytes</parameter>
+ </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>bytes</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 binary 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>bytes</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>bytes</parameter> <type>bytea</type>, <parameter>offset</parameter>)</function></literal>
</entry>
<entry><type>int</type></entry>
<entry>
- Extract bit from string
+ <link linkend="functions-zerobased-note">Extract</link> bit from binary
+ string
</entry>
<entry><literal>get_bit('Th\000omas'::bytea, 45)</literal></entry>
<entry><literal>1</literal></entry>
@@ -3719,67 +3696,81 @@ 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>bytes</parameter> <type>bytea</type>, <parameter>offset</parameter>)</function></literal>
</entry>
<entry><type>int</type></entry>
<entry>
- Extract byte from string
+ <link linkend="functions-zerobased-note">Extract</link> 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>bytes</parameter> <type>bytea</type>)</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>bytes</parameter> <type>bytea</type>,
+ <parameter>encoding</parameter> <type>name</type> )</function></literal></entry>
+ <entry><type>int</type></entry>
+ <entry>
+ Number of characters in <parameter>bytes</parameter> in the given
+ <parameter>encoding</parameter>. The <parameter>bytes</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>bytes</parameter> <type>bytea</type>)</function></literal>
+ </entry>
+ <entry><type>text</type></entry>
+ <entry>
+ Calculates the MD5 <link linkend="functions-hashnote">hash</link>
+ of <parameter>bytes</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>,
- <parameter>offset</parameter>, <parameter>newvalue</parameter>)</function></literal>
+ <literal><function>set_bit(<parameter>bytes</parameter> <type>bytea</type>,
+ <parameter>offset</parameter> <type>int</type>,
+ <parameter>newvalue</parameter> <type>int</type>)</function></literal>
</entry>
<entry><type>bytea</type></entry>
<entry>
- Set bit in string
+ <link linkend="functions-zerobased-note">Set</link> bit in binary string
</entry>
<entry><literal>set_bit('Th\000omas'::bytea, 45, 0)</literal></entry>
<entry><literal>Th\000omAs</literal></entry>
@@ -3790,12 +3781,13 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
<indexterm>
<primary>set_byte</primary>
</indexterm>
- <literal><function>set_byte(<parameter>string</parameter>,
- <parameter>offset</parameter>, <parameter>newvalue</parameter>)</function></literal>
+ <literal><function>set_byte(<parameter>bytes</parameter> <type>bytea</type>,
+ <parameter>offset</parameter> <type>int</type>,
+ <parameter>newvalue</parameter> <type>int</type>)</function></literal>
</entry>
<entry><type>bytea</type></entry>
<entry>
- Set byte in string
+ <link linkend="functions-zerobased-note">Set</link> byte in binary string
</entry>
<entry><literal>set_byte('Th\000omas'::bytea, 4, 64)</literal></entry>
<entry><literal>Th\000o@as</literal></entry>
@@ -3806,13 +3798,13 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
<indexterm>
<primary>sha224</primary>
</indexterm>
- <literal><function>sha224(<type>bytea</type>)</function></literal>
+ <literal><function>sha224(<parameter>bytes</parameter> <type>bytea</type>)</function></literal>
</entry>
<entry><type>bytea</type></entry>
<entry>
- SHA-224 hash
+ SHA-224 <link linkend="functions-hashnote">hash</link>
</entry>
- <entry><literal>sha224('abc')</literal></entry>
+ <entry><literal>sha224('abc'::bytea)</literal></entry>
<entry><literal>\x23097d223405d8228642a477bda2​55b32aadbce4bda0b3f7e36c9da7</literal></entry>
</row>
@@ -3821,13 +3813,13 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
<indexterm>
<primary>sha256</primary>
</indexterm>
- <literal><function>sha256(<type>bytea</type>)</function></literal>
+ <literal><function>sha256(<parameter>bytes</parameter> <type>bytea</type>)</function></literal>
</entry>
<entry><type>bytea</type></entry>
<entry>
- SHA-256 hash
+ SHA-256 <link linkend="functions-hashnote">hash</link>
</entry>
- <entry><literal>sha256('abc')</literal></entry>
+ <entry><literal>sha256('abc'::bytea)</literal></entry>
<entry><literal>\xba7816bf8f01cfea414140de5dae2223​b00361a396177a9cb410ff61f20015ad</literal></entry>
</row>
@@ -3836,13 +3828,13 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
<indexterm>
<primary>sha384</primary>
</indexterm>
- <literal><function>sha384(<type>bytea</type>)</function></literal>
+ <literal><function>sha384(<parameter>bytes</parameter> <type>bytea</type>)</function></literal>
</entry>
<entry><type>bytea</type></entry>
<entry>
- SHA-384 hash
+ SHA-384 <link linkend="functions-hashnote">hash</link>
</entry>
- <entry><literal>sha384('abc')</literal></entry>
+ <entry><literal>sha384('abc'::bytea)</literal></entry>
<entry><literal>\xcb00753f45a35e8bb5a03d699ac65007​272c32ab0eded1631a8b605a43ff5bed​8086072ba1e7cc2358baeca134c825a7</literal></entry>
</row>
@@ -3851,34 +3843,54 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
<indexterm>
<primary>sha512</primary>
</indexterm>
- <literal><function>sha512(<type>bytea</type>)</function></literal>
+ <literal><function>sha512(<parameter>bytes</parameter> <type>bytea</type>)</function></literal>
</entry>
<entry><type>bytea</type></entry>
<entry>
- SHA-512 hash
+ SHA-512 <link linkend="functions-hashnote">hash</link>
</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>bytes</parameter> <type>bytea</type>, <parameter>from</parameter> <optional>, <parameter>count</parameter> </optional>)</function></literal>
+ </entry>
+ <entry><type>bytea</type></entry>
+ <entry>
+ Extract binary substring (same as
+ <literal>substring(<parameter>bytea</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>
</tbody>
</tgroup>
</table>
- <para>
- <function>get_byte</function> and <function>set_byte</function> number the first byte
- of a binary string as byte 0.
- <function>get_bit</function> and <function>set_bit</function> number bits from the
- right within each byte; for example bit 0 is the least significant bit of
- the first byte, and bit 15 is the most significant bit of the second byte.
+ <para id="functions-zerobased-note">
+ Functions <function>get_byte</function> and <function>set_byte</function>
+ number the first byte of a binary string as byte 0.
+ Functions <function>get_bit</function> and <function>set_bit</function>
+ number bits from the right within each byte; for example bit 0 is the least
+ significant bit of 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
- <function>encode</function> and <function>decode</function> to convert
- between the two, for example <literal>encode(sha256('abc'),
- 'hex')</literal> to get a hex-encoded text representation.
+ <link linkend="functions-encode"><function>encode</function></link>
+ and <link linkend="functions-decode"><function>decode</function></link> to
+ convert between the two. For example <literal>encode(sha256('abc'),
+ 'hex')</literal> to get a hex-encoded text representation
+ and <literal>decode(md5('abc'), 'hex')</literal> to get
+ a <type>bytea</type> value.
</para>
<para>
@@ -3886,6 +3898,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>bytes</parameter> <type>bytea</type>,
+ <parameter>src_encoding</parameter> <type>name</type>)</function></literal>
+ </entry>
+ <entry><type>text</type></entry>
+ <entry>
+ Convert binary string to the database encoding. The original encoding
+ is specified by <parameter>src_encoding</parameter>. The
+ <parameter>bytes</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>bytes</parameter> <type>text</type>,
+ <parameter>dest_encoding</parameter> <type>name</type>)</function></literal>
+ </entry>
+ <entry><type>bytea</type></entry>
+ <entry>
+ Convert binary 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 id="functions-encode">
+ <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>bytes</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 id="functions-decode">
+ <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>
