On Thu, 9 Jan 2020 08:27:28 +0100 (CET)
Fabien COELHO <coe...@cri.ensmp.fr> wrote:
> > Another option would be to use "bytes bytea".
>
> > (The current patch uses "string bytea".)
> > This would probably also require some re-wording throughout.
> I like it, but this is only my own limited opinion, and I'm not a
> native English speaker.
Per your request for consistency I made this change throughout
the entire binary string section.
New patch attached: doc_base64_v13.patch
This required surprisingly little re-wording.
Added word "binary" into the descriptions of convert(),
substring(), convert_from(), and convert_to().
I also added data types to the call syntax of set_bit()
and set_byte().
And this patch adds hyperlinks from the get_bit(), get_byte(),
set_bit(), and set_byte() descriptions to the note
that offsets are zero-based.
I also removed the hyperlinked asterisks about the hash
function results and instead hyperlinked the word "hash"
in the descriptions. (Links to the note about md5()
returning hex text and the others returning bytea and how
to convert between the two.)
Regards,
Karl <k...@karlpinc.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 4b42f12862..3bd7232814 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,20 +3843,36 @@ 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>
+ <para id="functions-zerobased-note">
<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
@@ -3872,7 +3880,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
@@ -3886,6 +3894,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>
+ <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>
+ <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>
