Allow casting between bytea and integer types.

This allows smallint, integer, and bigint values to be cast to and
from bytea. The bytea value is the two's complement representation of
the integer, with the most significant byte first. For example:

  1234::bytea -> \x000004d2
  (-1234)::bytea -> \xfffffb2e

Author: Aleksander Alekseev <aleksander@timescale.com>
Reviewed-by: Joel Jacobson <joel@compiler.org>
Reviewed-by: Yugo Nagata <nagata@sraoss.co.jp>
Reviewed-by: Peter Eisentraut <peter@eisentraut.org>
Reviewed-by: Michael Paquier <michael@paquier.xyz>
Reviewed-by: Dean Rasheed <dean.a.rasheed@gmail.com>
Discussion: https://postgr.es/m/CAJ7c6TPtOp6%2BkFX5QX3fH1SVr7v65uHr-7yEJ%3DGMGQi5uhGtcA%40mail.gmail.com

3 files changed, 134 insertions, 0 deletions

diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out
index b673642ad1d..20bf9ea9cdf 100644
--- a/src/test/regress/expected/opr_sanity.out
+++ b/src/test/regress/expected/opr_sanity.out
@@ -875,6 +875,9 @@ uuid_extract_timestamp(uuid)
 uuid_extract_version(uuid)
 crc32(bytea)
 crc32c(bytea)
+bytea(smallint)
+bytea(integer)
+bytea(bigint)
 bytea_larger(bytea,bytea)
 bytea_smaller(bytea,bytea)
 -- Check that functions without argument are not marked as leakproof.
diff --git a/src/test/regress/expected/strings.out b/src/test/regress/expected/strings.out
index b65bb2d5368..f8cba9f5b24 100644
--- a/src/test/regress/expected/strings.out
+++ b/src/test/regress/expected/strings.out
@@ -2359,6 +2359,108 @@ SELECT set_byte('\x1234567890abcdef00'::bytea, 7, 11);
 SELECT set_byte('\x1234567890abcdef00'::bytea, 99, 11);  -- error
 ERROR:  index 99 out of valid range, 0..8
 --
+-- conversions between bytea and integer types
+--
+SELECT 0x1234::int2::bytea AS "\x1234", (-0x1234)::int2::bytea AS "\xedcc";
+ \x1234 | \xedcc 
+--------+--------
+ \x1234 | \xedcc
+(1 row)
+
+SELECT 0x12345678::int4::bytea AS "\x12345678", (-0x12345678)::int4::bytea AS "\xedcba988";
+ \x12345678 | \xedcba988 
+------------+------------
+ \x12345678 | \xedcba988
+(1 row)
+
+SELECT 0x1122334455667788::int8::bytea AS "\x1122334455667788",
+       (-0x1122334455667788)::int8::bytea AS "\xeeddccbbaa998878";
+ \x1122334455667788 | \xeeddccbbaa998878 
+--------------------+--------------------
+ \x1122334455667788 | \xeeddccbbaa998878
+(1 row)
+
+SELECT ''::bytea::int2 AS "0";
+ 0 
+---
+ 0
+(1 row)
+
+SELECT '\x12'::bytea::int2 AS "18";
+ 18 
+----
+ 18
+(1 row)
+
+SELECT '\x1234'::bytea::int2 AS "4460";
+ 4460 
+------
+ 4660
+(1 row)
+
+SELECT '\x123456'::bytea::int2; -- error
+ERROR:  smallint out of range
+SELECT ''::bytea::int4 AS "0";
+ 0 
+---
+ 0
+(1 row)
+
+SELECT '\x12'::bytea::int4 AS "18";
+ 18 
+----
+ 18
+(1 row)
+
+SELECT '\x12345678'::bytea::int4 AS "305419896";
+ 305419896 
+-----------
+ 305419896
+(1 row)
+
+SELECT '\x123456789A'::bytea::int4; -- error
+ERROR:  integer out of range
+SELECT ''::bytea::int8 AS "0";
+ 0 
+---
+ 0
+(1 row)
+
+SELECT '\x12'::bytea::int8 AS "18";
+ 18 
+----
+ 18
+(1 row)
+
+SELECT '\x1122334455667788'::bytea::int8 AS "1234605616436508552";
+ 1234605616436508552 
+---------------------
+ 1234605616436508552
+(1 row)
+
+SELECT '\x112233445566778899'::bytea::int8; -- error
+ERROR:  bigint out of range
+-- min/max integer values
+SELECT '\x8000'::bytea::int2 AS "-32768", '\x7FFF'::bytea::int2 AS "32767";
+ -32768 | 32767 
+--------+-------
+ -32768 | 32767
+(1 row)
+
+SELECT '\x80000000'::bytea::int4 AS "-2147483648", '\x7FFFFFFF'::bytea::int4 AS "2147483647";
+ -2147483648 | 2147483647 
+-------------+------------
+ -2147483648 | 2147483647
+(1 row)
+
+SELECT '\x8000000000000000'::bytea::int8 AS "-9223372036854775808",
+       '\x7FFFFFFFFFFFFFFF'::bytea::int8 AS "9223372036854775807";
+ -9223372036854775808 | 9223372036854775807 
+----------------------+---------------------
+ -9223372036854775808 | 9223372036854775807
+(1 row)
+
+--
 -- test behavior of escape_string_warning and standard_conforming_strings options
 --
 set escape_string_warning = off;
diff --git a/src/test/regress/sql/strings.sql b/src/test/regress/sql/strings.sql
index 8e0f3a0e75f..4deb0683d57 100644
--- a/src/test/regress/sql/strings.sql
+++ b/src/test/regress/sql/strings.sql
@@ -752,6 +752,35 @@ SELECT set_byte('\x1234567890abcdef00'::bytea, 7, 11);
 SELECT set_byte('\x1234567890abcdef00'::bytea, 99, 11);  -- error
 
 --
+-- conversions between bytea and integer types
+--
+SELECT 0x1234::int2::bytea AS "\x1234", (-0x1234)::int2::bytea AS "\xedcc";
+SELECT 0x12345678::int4::bytea AS "\x12345678", (-0x12345678)::int4::bytea AS "\xedcba988";
+SELECT 0x1122334455667788::int8::bytea AS "\x1122334455667788",
+       (-0x1122334455667788)::int8::bytea AS "\xeeddccbbaa998878";
+
+SELECT ''::bytea::int2 AS "0";
+SELECT '\x12'::bytea::int2 AS "18";
+SELECT '\x1234'::bytea::int2 AS "4460";
+SELECT '\x123456'::bytea::int2; -- error
+
+SELECT ''::bytea::int4 AS "0";
+SELECT '\x12'::bytea::int4 AS "18";
+SELECT '\x12345678'::bytea::int4 AS "305419896";
+SELECT '\x123456789A'::bytea::int4; -- error
+
+SELECT ''::bytea::int8 AS "0";
+SELECT '\x12'::bytea::int8 AS "18";
+SELECT '\x1122334455667788'::bytea::int8 AS "1234605616436508552";
+SELECT '\x112233445566778899'::bytea::int8; -- error
+
+-- min/max integer values
+SELECT '\x8000'::bytea::int2 AS "-32768", '\x7FFF'::bytea::int2 AS "32767";
+SELECT '\x80000000'::bytea::int4 AS "-2147483648", '\x7FFFFFFF'::bytea::int4 AS "2147483647";
+SELECT '\x8000000000000000'::bytea::int8 AS "-9223372036854775808",
+       '\x7FFFFFFFFFFFFFFF'::bytea::int8 AS "9223372036854775807";
+
+--
 -- test behavior of escape_string_warning and standard_conforming_strings options
 --
 set escape_string_warning = off;