Hello Fabien,
On Sun, 5 Jan 2020 12:48:59 +0100 (CET)
Fabien COELHO <coe...@cri.ensmp.fr> wrote:
> I'm in favor of moving and reorganizing these function descriptions,
> as they are somehow scattered with a unclear logic when you are
> looking for them.
I assume by this you mean you are happy with the organization done
by the patch.
For review (I think I've got this right) the organizational
changes are:
The changes suggested by Tom where 2
functions with the same name, one of which takes string
arguments and the other of which takes bytea arguments
now show up both in the doc section on string functions and
in the doc section on bytea functions.
I believe I also alphabetized the binary function ordering.
And this patch introduces a separate table/sub-section for
functions which convert between binary and string.)
There are much-expanded descriptions of encode()
and decode(). (Which is how this patch series
started, explaining base64 encoding/decoding.)
FYI. There is also an unusual usage of a hyperlinked
asterisk following the returned datatype of the hash
functions. The hyperlink leads to the historical
note on the datatype used for the md5() function v.s.
the other hash functions.
> + <entry><literal><parameter>bytea</parameter>
> <literal>||</literal>
> + <parameter>bytea</parameter></literal></entry>
> <entry> <type>bytea</type> </entry>
> <entry>
> String concatenation
>
> Bytea concatenation?
Done. (Could just say "Concatenation" I suppose. But "Bytea
concatenation" does not hurt and would be nice if you
ever looked at the tables for string operators and bytea
operators side-by-side.)
> I'm not keen on calling the parameter the name of its type. I'd
> suggest to keep "string" as a name everywhere, which is not a type
> name in Pg.
>
> The functions descriptions are not homogeneous. Some have parameter
> name & type "btrim(string bytea, bytes bytea)" and others only type
> or parameter with tagged as a parameter "get_bit(bytea,
> offset)" (first param), "sha224(bytea)".
>
> I'd suggest to be consistent, eg use "string bytea" everywhere
> appropriate.
Ok. Done. Except that I've left the encode() function
as encode(data bytea, format text) because the whole
point is to convert _to_ a string/text datatype
from something that's _not_ a string. Calling
the input a string just seems wrong. This inconsistency seems ok
because encode() is in the table of string <-> bytea functions,
away from the other bytea functions.
If you're interested, another possibility would be the
consistent use of "data bytea" everywhere. I like this
choice because it works well to write
encode(<parameter>data</parameter> bytea,
<parameter>format</parameter text), and probably
works well in other places too. But then the word
"string" does not really fit in a lot of the descriptions.
So this choice would involve re-writing descriptions so
that the existing description:
btrim(<parameter>string</parameter> bytea,
<parameter>bytes</parameter> bytea)
Remove the longest string containing only bytes appearing in
<parameter>bytes</parameter> from the start and end of
<parameter>string</parameter>
Would change to (say):
btrim(<parameter>data</parameter> bytea,
<parameter>bytes</parameter> bytea)
Remove the longest contiguous sequence of bytes containing only
those bytes appearing in <parameter>bytes</parameter>
from the start and end of <parameter>data</parameter>
The trouble with using "data bytea" is that there might
need to be adjustments to the word "string" elsewhere in
the section, not just in the descriptions.
Let me know if you'd prefer "data bytea" to "string bytea"
and consequent frobbing of descriptions. That might be
out-of-scope for this patch. (Which is already
a poster-child for feature-creep.)
Attached is doc_base64_v12.patch.
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..2e45bf71c8 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -1431,6 +1431,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
@@ -1792,101 +1799,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>
@@ -1954,19 +1866,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>
@@ -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
@@ -2330,6 +2229,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><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>
@@ -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,11 +3463,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>bytea</parameter> <literal>||</literal>
+ <parameter>bytea</parameter></literal></entry>
<entry> <type>bytea</type> </entry>
<entry>
- String concatenation
+ Bytea concatenation
<indexterm>
<primary>binary string</primary>
<secondary>concatenation</secondary>
@@ -3516,12 +3477,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>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>string</parameter> <type>bytea</type>, <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>string</parameter> <type>bytea</type>, <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>string</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>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>string</parameter> <type>bytea</type>,
<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>string</parameter> <type>bytea</type>,
<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>
@@ -3778,13 +3767,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>string</parameter> <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>
@@ -3793,13 +3782,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>string</parameter> <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>
@@ -3808,13 +3797,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>string</parameter> <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>
@@ -3823,15 +3812,31 @@ 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>string</parameter> <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>string</parameter> <type>bytea</type>, <parameter>from</parameter> <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> 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>
