[PATCH] Add get_bytes() and set_bytes() functions

From 010dd3aa8611ed943abf3e6464b2f1d6403662dc Mon Sep 17 00:00:00 2001
From: Aleksander Alekseev <aleksander@timescale.com>
Date: Wed, 14 Aug 2024 12:11:47 +0300
Subject: [PATCH v2] Add get_bytes() and set_bytes() functions.

The new functions provide a convenient way of converting between integer
types and bytea. Previously there were only get_byte() and set_byte() which
operate with a single byte.

Aleksander Alekseev, reviewed by Joel Jacobson, Yugo Nagata
Discussion: https://postgr.es/m/CAJ7c6TPtOp6%2BkFX5QX3fH1SVr7v65uHr-7yEJ%3DGMGQi5uhGtcA%40mail.gmail.com

BUMP CATVERSION
---
 doc/src/sgml/func.sgml                | 46 ++++++++++++++-
 src/backend/utils/adt/varlena.c       | 80 +++++++++++++++++++++++++++
 src/include/catalog/pg_proc.dat       |  6 ++
 src/test/regress/expected/strings.out | 36 ++++++++++++
 src/test/regress/sql/strings.sql      | 10 ++++
 5 files changed, 177 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 5dd95d73a1..f794eed8e6 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -4562,6 +4562,27 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>get_bytes</primary>
+        </indexterm>
+        <function>get_bytes</function> ( <parameter>bytes</parameter> <type>bytea</type>,
+        <parameter>offset</parameter> <type>integer</type>,
+        <parameter>size</parameter> <type>integer</type> )
+        <returnvalue>bigint</returnvalue>
+       </para>
+       <para>
+        Extracts <parameter>size</parameter> bytes at a given
+        <link linkend="functions-zerobased-note">offset</link>
+        from binary string.
+       </para>
+       <para>
+        <literal>get_bytes('\x0123456789ABCDEF'::bytea, 5, 2)</literal>
+        <returnvalue>43981</returnvalue>
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
@@ -4662,6 +4683,28 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>set_bytes</primary>
+        </indexterm>
+        <function>set_bytes</function> ( <parameter>bytes</parameter> <type>bytea</type>,
+        <parameter>offset</parameter> <type>integer</type>,
+        <parameter>size</parameter> <type>integer</type>,
+        <parameter>newvalue</parameter> <type>bigint</type> )
+        <returnvalue>bytea</returnvalue>
+       </para>
+       <para>
+        Sets <parameter>size</parameter> bytes at a given
+        <link linkend="functions-zerobased-note">offset</link>
+        in binary string to <parameter>newvalue</parameter>.
+       </para>
+       <para>
+        <literal>set_bytes('\x0123456789abcdef'::bytea, 5, 2, 0x1122)</literal>
+        <returnvalue>\x01234567891122ef</returnvalue>
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
@@ -4761,7 +4804,8 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
   </table>
 
   <para id="functions-zerobased-note">
-   Functions <function>get_byte</function> and <function>set_byte</function>
+   Functions <function>get_byte</function>, <function>set_byte</function>,
+   <function>get_bytes</function> and <function>set_bytes</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
diff --git a/src/backend/utils/adt/varlena.c b/src/backend/utils/adt/varlena.c
index 4f9a676c93..ffc22f1107 100644
--- a/src/backend/utils/adt/varlena.c
+++ b/src/backend/utils/adt/varlena.c
@@ -3212,6 +3212,46 @@ byteaGetByte(PG_FUNCTION_ARGS)
 	PG_RETURN_INT32(byte);
 }
 
+/*-------------------------------------------------------------
+ * byteaGetBytes
+ *
+ * This routine treats "bytea" as an array of bytes.
+ * It returns the N bytes at a given offset as a bigint value.
+ *-------------------------------------------------------------
+ */
+Datum
+byteaGetBytes(PG_FUNCTION_ARGS)
+{
+	bytea	   *v = PG_GETARG_BYTEA_PP(0);
+	int32		offset = PG_GETARG_INT32(1);
+	int32		size = PG_GETARG_INT32(2);
+	int64		result = 0;
+	int			len;
+
+	if (size < 1 || size > 8)
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("size %d out of valid range, 1..8",
+						size)));
+
+	len = VARSIZE_ANY_EXHDR(v);
+
+	if (offset < 0 || offset > (len-size))
+		ereport(ERROR,
+				(errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR),
+				 errmsg("index %d out of valid range, 0..%d",
+						offset, len - size)));
+
+	while (size--)
+	{
+		result = result << 8;
+		result |= ((unsigned char *) VARDATA_ANY(v))[offset];
+		offset++;
+	}
+
+	PG_RETURN_INT64(result);
+}
+
 /*-------------------------------------------------------------
  * byteaGetBit
  *
@@ -3282,6 +3322,46 @@ byteaSetByte(PG_FUNCTION_ARGS)
 	PG_RETURN_BYTEA_P(res);
 }
 
+/*-------------------------------------------------------------
+ * byteaSetBytes
+ *
+ * Given an instance of type 'bytea' creates a new one with
+ * the N bytes at a given offset set to the provided bigint value.
+ *
+ *-------------------------------------------------------------
+ */
+Datum
+byteaSetBytes(PG_FUNCTION_ARGS)
+{
+	bytea	   *res = PG_GETARG_BYTEA_P_COPY(0);
+	int32		offset = PG_GETARG_INT32(1);
+	int32		size = PG_GETARG_INT32(2);
+	int64		newValue = PG_GETARG_INT64(3);
+	int			len;
+
+	if (size < 1 || size > 8)
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("size %d out of valid range, 1..8",
+						size)));
+
+	len = VARSIZE_ANY_EXHDR(res);
+
+	if (offset < 0 || offset > (len-size))
+		ereport(ERROR,
+				(errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR),
+				 errmsg("index %d out of valid range, 0..%d",
+						offset, len - size)));
+	while (size)
+	{
+		((unsigned char*) VARDATA_ANY(res))[offset+size-1] = newValue & 0xFF;
+		newValue = newValue >> 8;
+		size--;
+	}
+
+	PG_RETURN_BYTEA_P(res);
+}
+
 /*-------------------------------------------------------------
  * byteaSetBit
  *
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 4abc6d9526..5daa1132c7 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -1441,6 +1441,12 @@
 { oid => '722', descr => 'set byte',
   proname => 'set_byte', prorettype => 'bytea',
   proargtypes => 'bytea int4 int4', prosrc => 'byteaSetByte' },
+{ oid => '8573', descr => 'get bytes',
+  proname => 'get_bytes', prorettype => 'int8', proargtypes => 'bytea int4 int4',
+  prosrc => 'byteaGetBytes' },
+{ oid => '8574', descr => 'set bytes',
+  proname => 'set_bytes', prorettype => 'bytea',
+  proargtypes => 'bytea int4 int4 int8', prosrc => 'byteaSetBytes' },
 { oid => '723', descr => 'get bit',
   proname => 'get_bit', prorettype => 'int4', proargtypes => 'bytea int8',
   prosrc => 'byteaGetBit' },
diff --git a/src/test/regress/expected/strings.out b/src/test/regress/expected/strings.out
index b65bb2d536..9fd52a1e11 100644
--- a/src/test/regress/expected/strings.out
+++ b/src/test/regress/expected/strings.out
@@ -2358,6 +2358,42 @@ SELECT set_byte('\x1234567890abcdef00'::bytea, 7, 11);
 
 SELECT set_byte('\x1234567890abcdef00'::bytea, 99, 11);  -- error
 ERROR:  index 99 out of valid range, 0..8
+SELECT get_bytes('\x1122334455667788'::bytea, 0, 8) = 0x1122334455667788;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT get_bytes('\x1122334455667788'::bytea, 1, 2) = 0x2233;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT get_bytes('\x1122334455667788'::bytea, 0, 0); -- error
+ERROR:  size 0 out of valid range, 1..8
+SELECT get_bytes('\x1122334455667788'::bytea, 0, 9); -- error
+ERROR:  size 9 out of valid range, 1..8
+SELECT get_bytes('\x1122334455667788'::bytea, 1, 8); -- error
+ERROR:  index 1 out of valid range, 0..0
+SELECT set_bytes('\x0123456789abcdef'::bytea, 0, 8, 0x1122334455667788);
+     set_bytes      
+--------------------
+ \x1122334455667788
+(1 row)
+
+SELECT set_bytes('\x1122334455667788'::bytea, 1, 2, 0xAABB);
+     set_bytes      
+--------------------
+ \x11aabb4455667788
+(1 row)
+
+SELECT set_bytes('\x0123456789abcdef'::bytea, 0, 0, 123); -- error
+ERROR:  size 0 out of valid range, 1..8
+SELECT set_bytes('\x0123456789abcdef'::bytea, 0, 9, 123); -- error
+ERROR:  size 9 out of valid range, 1..8
+SELECT set_bytes('\x0123456789abcdef'::bytea, 1, 8, 123); -- error
+ERROR:  index 1 out of valid range, 0..0
 --
 -- test behavior of escape_string_warning and standard_conforming_strings options
 --
diff --git a/src/test/regress/sql/strings.sql b/src/test/regress/sql/strings.sql
index 8e0f3a0e75..d0aa44ee2c 100644
--- a/src/test/regress/sql/strings.sql
+++ b/src/test/regress/sql/strings.sql
@@ -750,6 +750,16 @@ SELECT get_byte('\x1234567890abcdef00'::bytea, 3);
 SELECT get_byte('\x1234567890abcdef00'::bytea, 99);  -- error
 SELECT set_byte('\x1234567890abcdef00'::bytea, 7, 11);
 SELECT set_byte('\x1234567890abcdef00'::bytea, 99, 11);  -- error
+SELECT get_bytes('\x1122334455667788'::bytea, 0, 8) = 0x1122334455667788;
+SELECT get_bytes('\x1122334455667788'::bytea, 1, 2) = 0x2233;
+SELECT get_bytes('\x1122334455667788'::bytea, 0, 0); -- error
+SELECT get_bytes('\x1122334455667788'::bytea, 0, 9); -- error
+SELECT get_bytes('\x1122334455667788'::bytea, 1, 8); -- error
+SELECT set_bytes('\x0123456789abcdef'::bytea, 0, 8, 0x1122334455667788);
+SELECT set_bytes('\x1122334455667788'::bytea, 1, 2, 0xAABB);
+SELECT set_bytes('\x0123456789abcdef'::bytea, 0, 0, 123); -- error
+SELECT set_bytes('\x0123456789abcdef'::bytea, 0, 9, 123); -- error
+SELECT set_bytes('\x0123456789abcdef'::bytea, 1, 8, 123); -- error
 
 --
 -- test behavior of escape_string_warning and standard_conforming_strings options
-- 
2.46.0

From 8881aec49da43b6512f8804f6d64a604220df92c Mon Sep 17 00:00:00 2001
From: Aleksander Alekseev <aleksander@timescale.com>
Date: Mon, 26 Aug 2024 12:09:59 +0300
Subject: [PATCH v3 1/2] Allow casting between bytea and integer types.

For instance:

SELECT '\x12345678'::bytea::integer;
SELECT 0x12345678::bytea;

This works with int2's, int4's and int8's.

Aleksander Alekseev, reviewed by TODO FIXME
Discussion: https://postgr.es/m/CAJ7c6TPtOp6%2BkFX5QX3fH1SVr7v65uHr-7yEJ%3DGMGQi5uhGtcA%40mail.gmail.com

BUMP CATVERSION
---
 src/backend/utils/adt/int.c              | 55 ++++++++++++++++
 src/include/catalog/pg_cast.dat          | 14 ++++
 src/include/catalog/pg_proc.dat          | 18 ++++++
 src/test/regress/expected/opr_sanity.out | 15 ++++-
 src/test/regress/expected/strings.out    | 82 ++++++++++++++++++++++++
 src/test/regress/sql/strings.sql         | 24 +++++++
 6 files changed, 205 insertions(+), 3 deletions(-)

diff --git a/src/backend/utils/adt/int.c b/src/backend/utils/adt/int.c
index 234f20796b..a9ab582283 100644
--- a/src/backend/utils/adt/int.c
+++ b/src/backend/utils/adt/int.c
@@ -330,6 +330,61 @@ int4send(PG_FUNCTION_ARGS)
 }
 
 
+/*
+ *		Common code for bytea_int2, bytea_int4 and bytea_int8
+ */
+static int64
+bytea_integer(bytea* v, int max_size)
+{
+	int 	len = VARSIZE_ANY_EXHDR(v);
+	int 	offset = 0;
+	int64 	result = 0;
+
+	if (len > max_size)
+		ereport(ERROR,
+			(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+			errmsg("bytea size %d out of valid range, 0..%d",
+					len, max_size)));
+	while (len--)
+	{
+		result = result << 8;
+		result |= ((unsigned char *) VARDATA_ANY(v))[offset];
+		offset++;
+	}
+
+	return result;
+}
+
+/*
+ *		bytea_int2			- converts bytea to int2
+ */
+Datum
+bytea_int2(PG_FUNCTION_ARGS)
+{
+	bytea	*v = PG_GETARG_BYTEA_PP(0);
+	PG_RETURN_INT16((int16)bytea_integer(v, sizeof(int16)));
+}
+
+/*
+ *		bytea_int4			- converts bytea to int4
+ */
+Datum
+bytea_int4(PG_FUNCTION_ARGS)
+{
+	bytea	*v = PG_GETARG_BYTEA_PP(0);
+	PG_RETURN_INT32((int32)bytea_integer(v, sizeof(int32)));
+}
+
+/*
+ *		bytea_int8			- converts bytea to int8
+ */
+Datum
+bytea_int8(PG_FUNCTION_ARGS)
+{
+	bytea	*v = PG_GETARG_BYTEA_PP(0);
+	PG_RETURN_INT64(bytea_integer(v, sizeof(int64)));
+}
+
 /*
  *		===================
  *		CONVERSION ROUTINES
diff --git a/src/include/catalog/pg_cast.dat b/src/include/catalog/pg_cast.dat
index ca7b6d7191..c0350e7f09 100644
--- a/src/include/catalog/pg_cast.dat
+++ b/src/include/catalog/pg_cast.dat
@@ -320,6 +320,20 @@
 { castsource => 'varchar', casttarget => 'name', castfunc => 'name(varchar)',
   castcontext => 'i', castmethod => 'f' },
 
+# Allow explicit coercions between bytea and integer types
+{ castsource => 'int2', casttarget => 'bytea', castfunc => 'bytea(int2)',
+  castcontext => 'e', castmethod => 'f' },
+{ castsource => 'int4', casttarget => 'bytea', castfunc => 'bytea(int4)',
+  castcontext => 'e', castmethod => 'f' },
+{ castsource => 'int8', casttarget => 'bytea', castfunc => 'bytea(int8)',
+  castcontext => 'e', castmethod => 'f' },
+{ castsource => 'bytea', casttarget => 'int2', castfunc => 'int2(bytea)',
+  castcontext => 'e', castmethod => 'f' },
+{ castsource => 'bytea', casttarget => 'int4', castfunc => 'int4(bytea)',
+  castcontext => 'e', castmethod => 'f' },
+{ castsource => 'bytea', casttarget => 'int8', castfunc => 'int8(bytea)',
+  castcontext => 'e', castmethod => 'f' },
+
 # Allow explicit coercions between int4 and "char"
 { castsource => 'char', casttarget => 'int4', castfunc => 'int4(char)',
   castcontext => 'e', castmethod => 'f' },
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 4abc6d9526..b8640218b0 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -1164,6 +1164,24 @@
 { oid => '409', descr => 'convert char(n) to name',
   proname => 'name', proleakproof => 't', prorettype => 'name',
   proargtypes => 'bpchar', prosrc => 'bpchar_name' },
+{ oid => '8575', descr => 'convert int2 to bytea',
+  proname => 'bytea', proleakproof => 't', prorettype => 'bytea',
+  proargtypes => 'int2', prosrc => 'int2send' },
+{ oid => '8576', descr => 'convert int4 to bytea',
+  proname => 'bytea', proleakproof => 't', prorettype => 'bytea',
+  proargtypes => 'int4', prosrc => 'int4send' },
+{ oid => '8577', descr => 'convert int8 to bytea',
+  proname => 'bytea', proleakproof => 't', prorettype => 'bytea',
+  proargtypes => 'int8', prosrc => 'int8send' },
+{ oid => '8578', descr => 'convert bytea to int2',
+  proname => 'int2', proleakproof => 't', prorettype => 'int2',
+  proargtypes => 'bytea', prosrc => 'bytea_int2' },
+{ oid => '8579', descr => 'convert bytea to int4',
+  proname => 'int4', proleakproof => 't', prorettype => 'int4',
+  proargtypes => 'bytea', prosrc => 'bytea_int4' },
+{ oid => '8580', descr => 'convert bytea to int8',
+  proname => 'int8', proleakproof => 't', prorettype => 'int8',
+  proargtypes => 'bytea', prosrc => 'bytea_int8' },
 
 { oid => '449', descr => 'hash',
   proname => 'hashint2', prorettype => 'int4', proargtypes => 'int2',
diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out
index 0d734169f1..2a80b3ced2 100644
--- a/src/test/regress/expected/opr_sanity.out
+++ b/src/test/regress/expected/opr_sanity.out
@@ -126,9 +126,12 @@ WHERE p1.oid < p2.oid AND
      p1.proretset != p2.proretset OR
      p1.provolatile != p2.provolatile OR
      p1.pronargs != p2.pronargs);
- oid | proname | oid | proname 
------+---------+-----+---------
-(0 rows)
+ oid  | proname  | oid  | proname 
+------+----------+------+---------
+ 2405 | int2send | 8575 | bytea
+ 2407 | int4send | 8576 | bytea
+ 2409 | int8send | 8577 | bytea
+(3 rows)
 
 -- Look for uses of different type OIDs in the argument/result type fields
 -- for different aliases of the same built-in function.
@@ -876,6 +879,12 @@ uuid_extract_timestamp(uuid)
 uuid_extract_version(uuid)
 crc32(bytea)
 crc32c(bytea)
+bytea(smallint)
+bytea(integer)
+bytea(bigint)
+int2(bytea)
+int4(bytea)
+int8(bytea)
 -- restore normal output mode
 \a\t
 -- List of functions used by libpq's fe-lobj.c
diff --git a/src/test/regress/expected/strings.out b/src/test/regress/expected/strings.out
index b65bb2d536..709f58ad7f 100644
--- a/src/test/regress/expected/strings.out
+++ b/src/test/regress/expected/strings.out
@@ -2690,3 +2690,85 @@ ERROR:  invalid Unicode code point: 2FFFFF
 SELECT unistr('wrong: \xyz');
 ERROR:  invalid Unicode escape
 HINT:  Unicode escapes must be \XXXX, \+XXXXXX, \uXXXX, or \UXXXXXXXX.
+--
+-- Test coercions between bytea and integer types
+--
+SET bytea_output TO hex;
+SELECT 0x1234::int2::bytea;
+ bytea  
+--------
+ \x1234
+(1 row)
+
+SELECT 0x12345678::int4::bytea;
+   bytea    
+------------
+ \x12345678
+(1 row)
+
+SELECT 0x1122334455667788::int8::bytea;
+       bytea        
+--------------------
+ \x1122334455667788
+(1 row)
+
+SELECT ''::bytea::int2 = 0;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x12'::bytea::int2 = 0x12;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x1234'::bytea::int2 = 0x1234;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x123456'::bytea::int2; -- error
+ERROR:  bytea size 3 out of valid range, 0..2
+SELECT ''::bytea::int4 = 0;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x12'::bytea::int4 = 0x12;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x12345678'::bytea::int4 = 0x12345678;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x123456789A'::bytea::int4; -- error
+ERROR:  bytea size 5 out of valid range, 0..4
+SELECT ''::bytea::int8 = 0;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x12'::bytea::int8 = 0x12;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x1122334455667788'::bytea::int8 = 0x1122334455667788;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x112233445566778899'::bytea::int8; -- error
+ERROR:  bytea size 9 out of valid range, 0..8
diff --git a/src/test/regress/sql/strings.sql b/src/test/regress/sql/strings.sql
index 8e0f3a0e75..b8858c9aea 100644
--- a/src/test/regress/sql/strings.sql
+++ b/src/test/regress/sql/strings.sql
@@ -848,3 +848,27 @@ SELECT unistr('wrong: \udb99\u0061');
 SELECT unistr('wrong: \U0000db99\U00000061');
 SELECT unistr('wrong: \U002FFFFF');
 SELECT unistr('wrong: \xyz');
+
+--
+-- Test coercions between bytea and integer types
+--
+SET bytea_output TO hex;
+
+SELECT 0x1234::int2::bytea;
+SELECT 0x12345678::int4::bytea;
+SELECT 0x1122334455667788::int8::bytea;
+
+SELECT ''::bytea::int2 = 0;
+SELECT '\x12'::bytea::int2 = 0x12;
+SELECT '\x1234'::bytea::int2 = 0x1234;
+SELECT '\x123456'::bytea::int2; -- error
+
+SELECT ''::bytea::int4 = 0;
+SELECT '\x12'::bytea::int4 = 0x12;
+SELECT '\x12345678'::bytea::int4 = 0x12345678;
+SELECT '\x123456789A'::bytea::int4; -- error
+
+SELECT ''::bytea::int8 = 0;
+SELECT '\x12'::bytea::int8 = 0x12;
+SELECT '\x1122334455667788'::bytea::int8 = 0x1122334455667788;
+SELECT '\x112233445566778899'::bytea::int8; -- error
-- 
2.46.0

From 20631f194e9820ba6629892b566f5d0d7a401560 Mon Sep 17 00:00:00 2001
From: Aleksander Alekseev <aleksander@timescale.com>
Date: Wed, 14 Aug 2024 12:11:47 +0300
Subject: [PATCH v3 2/2] Add get_bytes() and set_bytes() functions.

The new functions provide a convenient way of extracting integers from
bytea's and putting integer values into bytea's. Previously there were only
get_byte() and set_byte() which operate with a single byte.

Aleksander Alekseev, reviewed by Joel Jacobson, Yugo Nagata
Discussion: https://postgr.es/m/CAJ7c6TPtOp6%2BkFX5QX3fH1SVr7v65uHr-7yEJ%3DGMGQi5uhGtcA%40mail.gmail.com

BUMP CATVERSION
---
 doc/src/sgml/func.sgml                | 46 ++++++++++++++-
 src/backend/utils/adt/varlena.c       | 80 +++++++++++++++++++++++++++
 src/include/catalog/pg_proc.dat       |  6 ++
 src/test/regress/expected/strings.out | 36 ++++++++++++
 src/test/regress/sql/strings.sql      | 10 ++++
 5 files changed, 177 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 461fc3f437..8f7f65b954 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -4562,6 +4562,27 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>get_bytes</primary>
+        </indexterm>
+        <function>get_bytes</function> ( <parameter>bytes</parameter> <type>bytea</type>,
+        <parameter>offset</parameter> <type>integer</type>,
+        <parameter>size</parameter> <type>integer</type> )
+        <returnvalue>bigint</returnvalue>
+       </para>
+       <para>
+        Extracts <parameter>size</parameter> bytes at a given
+        <link linkend="functions-zerobased-note">offset</link>
+        from binary string.
+       </para>
+       <para>
+        <literal>get_bytes('\x0123456789ABCDEF'::bytea, 5, 2)</literal>
+        <returnvalue>43981</returnvalue>
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
@@ -4662,6 +4683,28 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>set_bytes</primary>
+        </indexterm>
+        <function>set_bytes</function> ( <parameter>bytes</parameter> <type>bytea</type>,
+        <parameter>offset</parameter> <type>integer</type>,
+        <parameter>size</parameter> <type>integer</type>,
+        <parameter>newvalue</parameter> <type>bigint</type> )
+        <returnvalue>bytea</returnvalue>
+       </para>
+       <para>
+        Sets <parameter>size</parameter> bytes at a given
+        <link linkend="functions-zerobased-note">offset</link>
+        in binary string to <parameter>newvalue</parameter>.
+       </para>
+       <para>
+        <literal>set_bytes('\x0123456789abcdef'::bytea, 5, 2, 0x1122)</literal>
+        <returnvalue>\x01234567891122ef</returnvalue>
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
@@ -4761,7 +4804,8 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
   </table>
 
   <para id="functions-zerobased-note">
-   Functions <function>get_byte</function> and <function>set_byte</function>
+   Functions <function>get_byte</function>, <function>set_byte</function>,
+   <function>get_bytes</function> and <function>set_bytes</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
diff --git a/src/backend/utils/adt/varlena.c b/src/backend/utils/adt/varlena.c
index 7c6391a276..ff26591f63 100644
--- a/src/backend/utils/adt/varlena.c
+++ b/src/backend/utils/adt/varlena.c
@@ -3206,6 +3206,46 @@ byteaGetByte(PG_FUNCTION_ARGS)
 	PG_RETURN_INT32(byte);
 }
 
+/*-------------------------------------------------------------
+ * byteaGetBytes
+ *
+ * This routine treats "bytea" as an array of bytes.
+ * It returns the N bytes at a given offset as a bigint value.
+ *-------------------------------------------------------------
+ */
+Datum
+byteaGetBytes(PG_FUNCTION_ARGS)
+{
+	bytea	   *v = PG_GETARG_BYTEA_PP(0);
+	int32		offset = PG_GETARG_INT32(1);
+	int32		size = PG_GETARG_INT32(2);
+	int64		result = 0;
+	int			len;
+
+	if (size < 1 || size > 8)
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("size %d out of valid range, 1..8",
+						size)));
+
+	len = VARSIZE_ANY_EXHDR(v);
+
+	if (offset < 0 || offset > (len-size))
+		ereport(ERROR,
+				(errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR),
+				 errmsg("index %d out of valid range, 0..%d",
+						offset, len - size)));
+
+	while (size--)
+	{
+		result = result << 8;
+		result |= ((unsigned char *) VARDATA_ANY(v))[offset];
+		offset++;
+	}
+
+	PG_RETURN_INT64(result);
+}
+
 /*-------------------------------------------------------------
  * byteaGetBit
  *
@@ -3276,6 +3316,46 @@ byteaSetByte(PG_FUNCTION_ARGS)
 	PG_RETURN_BYTEA_P(res);
 }
 
+/*-------------------------------------------------------------
+ * byteaSetBytes
+ *
+ * Given an instance of type 'bytea' creates a new one with
+ * the N bytes at a given offset set to the provided bigint value.
+ *
+ *-------------------------------------------------------------
+ */
+Datum
+byteaSetBytes(PG_FUNCTION_ARGS)
+{
+	bytea	   *res = PG_GETARG_BYTEA_P_COPY(0);
+	int32		offset = PG_GETARG_INT32(1);
+	int32		size = PG_GETARG_INT32(2);
+	int64		newValue = PG_GETARG_INT64(3);
+	int			len;
+
+	if (size < 1 || size > 8)
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("size %d out of valid range, 1..8",
+						size)));
+
+	len = VARSIZE_ANY_EXHDR(res);
+
+	if (offset < 0 || offset > (len-size))
+		ereport(ERROR,
+				(errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR),
+				 errmsg("index %d out of valid range, 0..%d",
+						offset, len - size)));
+	while (size)
+	{
+		((unsigned char*) VARDATA_ANY(res))[offset+size-1] = newValue & 0xFF;
+		newValue = newValue >> 8;
+		size--;
+	}
+
+	PG_RETURN_BYTEA_P(res);
+}
+
 /*-------------------------------------------------------------
  * byteaSetBit
  *
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index b8640218b0..3424c903e0 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -1459,6 +1459,12 @@
 { oid => '722', descr => 'set byte',
   proname => 'set_byte', prorettype => 'bytea',
   proargtypes => 'bytea int4 int4', prosrc => 'byteaSetByte' },
+{ oid => '8573', descr => 'get bytes',
+  proname => 'get_bytes', prorettype => 'int8', proargtypes => 'bytea int4 int4',
+  prosrc => 'byteaGetBytes' },
+{ oid => '8574', descr => 'set bytes',
+  proname => 'set_bytes', prorettype => 'bytea',
+  proargtypes => 'bytea int4 int4 int8', prosrc => 'byteaSetBytes' },
 { oid => '723', descr => 'get bit',
   proname => 'get_bit', prorettype => 'int4', proargtypes => 'bytea int8',
   prosrc => 'byteaGetBit' },
diff --git a/src/test/regress/expected/strings.out b/src/test/regress/expected/strings.out
index 709f58ad7f..c58fc9b918 100644
--- a/src/test/regress/expected/strings.out
+++ b/src/test/regress/expected/strings.out
@@ -2358,6 +2358,42 @@ SELECT set_byte('\x1234567890abcdef00'::bytea, 7, 11);
 
 SELECT set_byte('\x1234567890abcdef00'::bytea, 99, 11);  -- error
 ERROR:  index 99 out of valid range, 0..8
+SELECT get_bytes('\x1122334455667788'::bytea, 0, 8) = 0x1122334455667788;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT get_bytes('\x1122334455667788'::bytea, 1, 2) = 0x2233;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT get_bytes('\x1122334455667788'::bytea, 0, 0); -- error
+ERROR:  size 0 out of valid range, 1..8
+SELECT get_bytes('\x1122334455667788'::bytea, 0, 9); -- error
+ERROR:  size 9 out of valid range, 1..8
+SELECT get_bytes('\x1122334455667788'::bytea, 1, 8); -- error
+ERROR:  index 1 out of valid range, 0..0
+SELECT set_bytes('\x0123456789abcdef'::bytea, 0, 8, 0x1122334455667788);
+     set_bytes      
+--------------------
+ \x1122334455667788
+(1 row)
+
+SELECT set_bytes('\x1122334455667788'::bytea, 1, 2, 0xAABB);
+     set_bytes      
+--------------------
+ \x11aabb4455667788
+(1 row)
+
+SELECT set_bytes('\x0123456789abcdef'::bytea, 0, 0, 123); -- error
+ERROR:  size 0 out of valid range, 1..8
+SELECT set_bytes('\x0123456789abcdef'::bytea, 0, 9, 123); -- error
+ERROR:  size 9 out of valid range, 1..8
+SELECT set_bytes('\x0123456789abcdef'::bytea, 1, 8, 123); -- error
+ERROR:  index 1 out of valid range, 0..0
 --
 -- test behavior of escape_string_warning and standard_conforming_strings options
 --
diff --git a/src/test/regress/sql/strings.sql b/src/test/regress/sql/strings.sql
index b8858c9aea..82d677562a 100644
--- a/src/test/regress/sql/strings.sql
+++ b/src/test/regress/sql/strings.sql
@@ -750,6 +750,16 @@ SELECT get_byte('\x1234567890abcdef00'::bytea, 3);
 SELECT get_byte('\x1234567890abcdef00'::bytea, 99);  -- error
 SELECT set_byte('\x1234567890abcdef00'::bytea, 7, 11);
 SELECT set_byte('\x1234567890abcdef00'::bytea, 99, 11);  -- error
+SELECT get_bytes('\x1122334455667788'::bytea, 0, 8) = 0x1122334455667788;
+SELECT get_bytes('\x1122334455667788'::bytea, 1, 2) = 0x2233;
+SELECT get_bytes('\x1122334455667788'::bytea, 0, 0); -- error
+SELECT get_bytes('\x1122334455667788'::bytea, 0, 9); -- error
+SELECT get_bytes('\x1122334455667788'::bytea, 1, 8); -- error
+SELECT set_bytes('\x0123456789abcdef'::bytea, 0, 8, 0x1122334455667788);
+SELECT set_bytes('\x1122334455667788'::bytea, 1, 2, 0xAABB);
+SELECT set_bytes('\x0123456789abcdef'::bytea, 0, 0, 123); -- error
+SELECT set_bytes('\x0123456789abcdef'::bytea, 0, 9, 123); -- error
+SELECT set_bytes('\x0123456789abcdef'::bytea, 1, 8, 123); -- error
 
 --
 -- test behavior of escape_string_warning and standard_conforming_strings options
-- 
2.46.0

From d31bd5bd525566f6dee815b4f47d4fe64ac304c2 Mon Sep 17 00:00:00 2001
From: Aleksander Alekseev <aleksander@timescale.com>
Date: Mon, 26 Aug 2024 12:09:59 +0300
Subject: [PATCH v4 1/2] Allow casting between bytea and integer types.

For instance:

SELECT '\x12345678'::bytea::integer;
SELECT 0x12345678::bytea;

This works with int2's, int4's and int8's.

Aleksander Alekseev, reviewed by TODO FIXME
Discussion: https://postgr.es/m/CAJ7c6TPtOp6%2BkFX5QX3fH1SVr7v65uHr-7yEJ%3DGMGQi5uhGtcA%40mail.gmail.com

BUMP CATVERSION
---
 src/backend/utils/adt/int.c              | 55 ++++++++++++++++
 src/include/catalog/pg_cast.dat          | 14 ++++
 src/include/catalog/pg_proc.dat          | 18 ++++++
 src/test/regress/expected/opr_sanity.out | 15 ++++-
 src/test/regress/expected/strings.out    | 82 ++++++++++++++++++++++++
 src/test/regress/sql/strings.sql         | 24 +++++++
 6 files changed, 205 insertions(+), 3 deletions(-)

diff --git a/src/backend/utils/adt/int.c b/src/backend/utils/adt/int.c
index 234f20796b..a9ab582283 100644
--- a/src/backend/utils/adt/int.c
+++ b/src/backend/utils/adt/int.c
@@ -330,6 +330,61 @@ int4send(PG_FUNCTION_ARGS)
 }
 
 
+/*
+ *		Common code for bytea_int2, bytea_int4 and bytea_int8
+ */
+static int64
+bytea_integer(bytea* v, int max_size)
+{
+	int 	len = VARSIZE_ANY_EXHDR(v);
+	int 	offset = 0;
+	int64 	result = 0;
+
+	if (len > max_size)
+		ereport(ERROR,
+			(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+			errmsg("bytea size %d out of valid range, 0..%d",
+					len, max_size)));
+	while (len--)
+	{
+		result = result << 8;
+		result |= ((unsigned char *) VARDATA_ANY(v))[offset];
+		offset++;
+	}
+
+	return result;
+}
+
+/*
+ *		bytea_int2			- converts bytea to int2
+ */
+Datum
+bytea_int2(PG_FUNCTION_ARGS)
+{
+	bytea	*v = PG_GETARG_BYTEA_PP(0);
+	PG_RETURN_INT16((int16)bytea_integer(v, sizeof(int16)));
+}
+
+/*
+ *		bytea_int4			- converts bytea to int4
+ */
+Datum
+bytea_int4(PG_FUNCTION_ARGS)
+{
+	bytea	*v = PG_GETARG_BYTEA_PP(0);
+	PG_RETURN_INT32((int32)bytea_integer(v, sizeof(int32)));
+}
+
+/*
+ *		bytea_int8			- converts bytea to int8
+ */
+Datum
+bytea_int8(PG_FUNCTION_ARGS)
+{
+	bytea	*v = PG_GETARG_BYTEA_PP(0);
+	PG_RETURN_INT64(bytea_integer(v, sizeof(int64)));
+}
+
 /*
  *		===================
  *		CONVERSION ROUTINES
diff --git a/src/include/catalog/pg_cast.dat b/src/include/catalog/pg_cast.dat
index ca7b6d7191..c0350e7f09 100644
--- a/src/include/catalog/pg_cast.dat
+++ b/src/include/catalog/pg_cast.dat
@@ -320,6 +320,20 @@
 { castsource => 'varchar', casttarget => 'name', castfunc => 'name(varchar)',
   castcontext => 'i', castmethod => 'f' },
 
+# Allow explicit coercions between bytea and integer types
+{ castsource => 'int2', casttarget => 'bytea', castfunc => 'bytea(int2)',
+  castcontext => 'e', castmethod => 'f' },
+{ castsource => 'int4', casttarget => 'bytea', castfunc => 'bytea(int4)',
+  castcontext => 'e', castmethod => 'f' },
+{ castsource => 'int8', casttarget => 'bytea', castfunc => 'bytea(int8)',
+  castcontext => 'e', castmethod => 'f' },
+{ castsource => 'bytea', casttarget => 'int2', castfunc => 'int2(bytea)',
+  castcontext => 'e', castmethod => 'f' },
+{ castsource => 'bytea', casttarget => 'int4', castfunc => 'int4(bytea)',
+  castcontext => 'e', castmethod => 'f' },
+{ castsource => 'bytea', casttarget => 'int8', castfunc => 'int8(bytea)',
+  castcontext => 'e', castmethod => 'f' },
+
 # Allow explicit coercions between int4 and "char"
 { castsource => 'char', casttarget => 'int4', castfunc => 'int4(char)',
   castcontext => 'e', castmethod => 'f' },
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 77f54a79e6..fb4e53ddaa 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -1164,6 +1164,24 @@
 { oid => '409', descr => 'convert char(n) to name',
   proname => 'name', proleakproof => 't', prorettype => 'name',
   proargtypes => 'bpchar', prosrc => 'bpchar_name' },
+{ oid => '8577', descr => 'convert int2 to bytea',
+  proname => 'bytea', proleakproof => 't', prorettype => 'bytea',
+  proargtypes => 'int2', prosrc => 'int2send' },
+{ oid => '8578', descr => 'convert int4 to bytea',
+  proname => 'bytea', proleakproof => 't', prorettype => 'bytea',
+  proargtypes => 'int4', prosrc => 'int4send' },
+{ oid => '8579', descr => 'convert int8 to bytea',
+  proname => 'bytea', proleakproof => 't', prorettype => 'bytea',
+  proargtypes => 'int8', prosrc => 'int8send' },
+{ oid => '8580', descr => 'convert bytea to int2',
+  proname => 'int2', proleakproof => 't', prorettype => 'int2',
+  proargtypes => 'bytea', prosrc => 'bytea_int2' },
+{ oid => '8581', descr => 'convert bytea to int4',
+  proname => 'int4', proleakproof => 't', prorettype => 'int4',
+  proargtypes => 'bytea', prosrc => 'bytea_int4' },
+{ oid => '8582', descr => 'convert bytea to int8',
+  proname => 'int8', proleakproof => 't', prorettype => 'int8',
+  proargtypes => 'bytea', prosrc => 'bytea_int8' },
 
 { oid => '449', descr => 'hash',
   proname => 'hashint2', prorettype => 'int4', proargtypes => 'int2',
diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out
index 0d734169f1..16135cb765 100644
--- a/src/test/regress/expected/opr_sanity.out
+++ b/src/test/regress/expected/opr_sanity.out
@@ -126,9 +126,12 @@ WHERE p1.oid < p2.oid AND
      p1.proretset != p2.proretset OR
      p1.provolatile != p2.provolatile OR
      p1.pronargs != p2.pronargs);
- oid | proname | oid | proname 
------+---------+-----+---------
-(0 rows)
+ oid  | proname  | oid  | proname 
+------+----------+------+---------
+ 2405 | int2send | 8577 | bytea
+ 2407 | int4send | 8578 | bytea
+ 2409 | int8send | 8579 | bytea
+(3 rows)
 
 -- Look for uses of different type OIDs in the argument/result type fields
 -- for different aliases of the same built-in function.
@@ -876,6 +879,12 @@ uuid_extract_timestamp(uuid)
 uuid_extract_version(uuid)
 crc32(bytea)
 crc32c(bytea)
+bytea(smallint)
+bytea(integer)
+bytea(bigint)
+int2(bytea)
+int4(bytea)
+int8(bytea)
 -- restore normal output mode
 \a\t
 -- List of functions used by libpq's fe-lobj.c
diff --git a/src/test/regress/expected/strings.out b/src/test/regress/expected/strings.out
index b65bb2d536..709f58ad7f 100644
--- a/src/test/regress/expected/strings.out
+++ b/src/test/regress/expected/strings.out
@@ -2690,3 +2690,85 @@ ERROR:  invalid Unicode code point: 2FFFFF
 SELECT unistr('wrong: \xyz');
 ERROR:  invalid Unicode escape
 HINT:  Unicode escapes must be \XXXX, \+XXXXXX, \uXXXX, or \UXXXXXXXX.
+--
+-- Test coercions between bytea and integer types
+--
+SET bytea_output TO hex;
+SELECT 0x1234::int2::bytea;
+ bytea  
+--------
+ \x1234
+(1 row)
+
+SELECT 0x12345678::int4::bytea;
+   bytea    
+------------
+ \x12345678
+(1 row)
+
+SELECT 0x1122334455667788::int8::bytea;
+       bytea        
+--------------------
+ \x1122334455667788
+(1 row)
+
+SELECT ''::bytea::int2 = 0;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x12'::bytea::int2 = 0x12;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x1234'::bytea::int2 = 0x1234;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x123456'::bytea::int2; -- error
+ERROR:  bytea size 3 out of valid range, 0..2
+SELECT ''::bytea::int4 = 0;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x12'::bytea::int4 = 0x12;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x12345678'::bytea::int4 = 0x12345678;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x123456789A'::bytea::int4; -- error
+ERROR:  bytea size 5 out of valid range, 0..4
+SELECT ''::bytea::int8 = 0;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x12'::bytea::int8 = 0x12;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x1122334455667788'::bytea::int8 = 0x1122334455667788;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x112233445566778899'::bytea::int8; -- error
+ERROR:  bytea size 9 out of valid range, 0..8
diff --git a/src/test/regress/sql/strings.sql b/src/test/regress/sql/strings.sql
index 8e0f3a0e75..b8858c9aea 100644
--- a/src/test/regress/sql/strings.sql
+++ b/src/test/regress/sql/strings.sql
@@ -848,3 +848,27 @@ SELECT unistr('wrong: \udb99\u0061');
 SELECT unistr('wrong: \U0000db99\U00000061');
 SELECT unistr('wrong: \U002FFFFF');
 SELECT unistr('wrong: \xyz');
+
+--
+-- Test coercions between bytea and integer types
+--
+SET bytea_output TO hex;
+
+SELECT 0x1234::int2::bytea;
+SELECT 0x12345678::int4::bytea;
+SELECT 0x1122334455667788::int8::bytea;
+
+SELECT ''::bytea::int2 = 0;
+SELECT '\x12'::bytea::int2 = 0x12;
+SELECT '\x1234'::bytea::int2 = 0x1234;
+SELECT '\x123456'::bytea::int2; -- error
+
+SELECT ''::bytea::int4 = 0;
+SELECT '\x12'::bytea::int4 = 0x12;
+SELECT '\x12345678'::bytea::int4 = 0x12345678;
+SELECT '\x123456789A'::bytea::int4; -- error
+
+SELECT ''::bytea::int8 = 0;
+SELECT '\x12'::bytea::int8 = 0x12;
+SELECT '\x1122334455667788'::bytea::int8 = 0x1122334455667788;
+SELECT '\x112233445566778899'::bytea::int8; -- error
-- 
2.46.0

From 8bff868366462aa8c0edc273d05eb30ad0214512 Mon Sep 17 00:00:00 2001
From: Aleksander Alekseev <aleksander@timescale.com>
Date: Wed, 14 Aug 2024 12:11:47 +0300
Subject: [PATCH v4 2/2] Add get_bytes() and set_bytes() functions.

The new functions provide a convenient way of extracting integers from
bytea's and putting integer values into bytea's. Previously there were only
get_byte() and set_byte() which operate with a single byte.

Aleksander Alekseev, reviewed by Joel Jacobson, Yugo Nagata
Discussion: https://postgr.es/m/CAJ7c6TPtOp6%2BkFX5QX3fH1SVr7v65uHr-7yEJ%3DGMGQi5uhGtcA%40mail.gmail.com

BUMP CATVERSION
---
 doc/src/sgml/func.sgml                | 46 ++++++++++++++-
 src/backend/utils/adt/varlena.c       | 80 +++++++++++++++++++++++++++
 src/include/catalog/pg_proc.dat       |  6 ++
 src/test/regress/expected/strings.out | 36 ++++++++++++
 src/test/regress/sql/strings.sql      | 10 ++++
 5 files changed, 177 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 7b4fbb5047..c1ef05de7d 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -4562,6 +4562,27 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>get_bytes</primary>
+        </indexterm>
+        <function>get_bytes</function> ( <parameter>bytes</parameter> <type>bytea</type>,
+        <parameter>offset</parameter> <type>integer</type>,
+        <parameter>size</parameter> <type>integer</type> )
+        <returnvalue>bigint</returnvalue>
+       </para>
+       <para>
+        Extracts <parameter>size</parameter> bytes at a given
+        <link linkend="functions-zerobased-note">offset</link>
+        from binary string.
+       </para>
+       <para>
+        <literal>get_bytes('\x0123456789ABCDEF'::bytea, 5, 2)</literal>
+        <returnvalue>43981</returnvalue>
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
@@ -4662,6 +4683,28 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>set_bytes</primary>
+        </indexterm>
+        <function>set_bytes</function> ( <parameter>bytes</parameter> <type>bytea</type>,
+        <parameter>offset</parameter> <type>integer</type>,
+        <parameter>size</parameter> <type>integer</type>,
+        <parameter>newvalue</parameter> <type>bigint</type> )
+        <returnvalue>bytea</returnvalue>
+       </para>
+       <para>
+        Sets <parameter>size</parameter> bytes at a given
+        <link linkend="functions-zerobased-note">offset</link>
+        in binary string to <parameter>newvalue</parameter>.
+       </para>
+       <para>
+        <literal>set_bytes('\x0123456789abcdef'::bytea, 5, 2, 0x1122)</literal>
+        <returnvalue>\x01234567891122ef</returnvalue>
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
@@ -4761,7 +4804,8 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
   </table>
 
   <para id="functions-zerobased-note">
-   Functions <function>get_byte</function> and <function>set_byte</function>
+   Functions <function>get_byte</function>, <function>set_byte</function>,
+   <function>get_bytes</function> and <function>set_bytes</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
diff --git a/src/backend/utils/adt/varlena.c b/src/backend/utils/adt/varlena.c
index d46ed3ccf9..e53c1a9898 100644
--- a/src/backend/utils/adt/varlena.c
+++ b/src/backend/utils/adt/varlena.c
@@ -3201,6 +3201,46 @@ byteaGetByte(PG_FUNCTION_ARGS)
 	PG_RETURN_INT32(byte);
 }
 
+/*-------------------------------------------------------------
+ * byteaGetBytes
+ *
+ * This routine treats "bytea" as an array of bytes.
+ * It returns the N bytes at a given offset as a bigint value.
+ *-------------------------------------------------------------
+ */
+Datum
+byteaGetBytes(PG_FUNCTION_ARGS)
+{
+	bytea	   *v = PG_GETARG_BYTEA_PP(0);
+	int32		offset = PG_GETARG_INT32(1);
+	int32		size = PG_GETARG_INT32(2);
+	int64		result = 0;
+	int			len;
+
+	if (size < 1 || size > 8)
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("size %d out of valid range, 1..8",
+						size)));
+
+	len = VARSIZE_ANY_EXHDR(v);
+
+	if (offset < 0 || offset > (len-size))
+		ereport(ERROR,
+				(errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR),
+				 errmsg("index %d out of valid range, 0..%d",
+						offset, len - size)));
+
+	while (size--)
+	{
+		result = result << 8;
+		result |= ((unsigned char *) VARDATA_ANY(v))[offset];
+		offset++;
+	}
+
+	PG_RETURN_INT64(result);
+}
+
 /*-------------------------------------------------------------
  * byteaGetBit
  *
@@ -3271,6 +3311,46 @@ byteaSetByte(PG_FUNCTION_ARGS)
 	PG_RETURN_BYTEA_P(res);
 }
 
+/*-------------------------------------------------------------
+ * byteaSetBytes
+ *
+ * Given an instance of type 'bytea' creates a new one with
+ * the N bytes at a given offset set to the provided bigint value.
+ *
+ *-------------------------------------------------------------
+ */
+Datum
+byteaSetBytes(PG_FUNCTION_ARGS)
+{
+	bytea	   *res = PG_GETARG_BYTEA_P_COPY(0);
+	int32		offset = PG_GETARG_INT32(1);
+	int32		size = PG_GETARG_INT32(2);
+	int64		newValue = PG_GETARG_INT64(3);
+	int			len;
+
+	if (size < 1 || size > 8)
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("size %d out of valid range, 1..8",
+						size)));
+
+	len = VARSIZE_ANY_EXHDR(res);
+
+	if (offset < 0 || offset > (len-size))
+		ereport(ERROR,
+				(errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR),
+				 errmsg("index %d out of valid range, 0..%d",
+						offset, len - size)));
+	while (size)
+	{
+		((unsigned char*) VARDATA_ANY(res))[offset+size-1] = newValue & 0xFF;
+		newValue = newValue >> 8;
+		size--;
+	}
+
+	PG_RETURN_BYTEA_P(res);
+}
+
 /*-------------------------------------------------------------
  * byteaSetBit
  *
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index fb4e53ddaa..7de3d0cf71 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -1495,6 +1495,12 @@
 { oid => '722', descr => 'set byte',
   proname => 'set_byte', prorettype => 'bytea',
   proargtypes => 'bytea int4 int4', prosrc => 'byteaSetByte' },
+{ oid => '8575', descr => 'get bytes',
+  proname => 'get_bytes', prorettype => 'int8', proargtypes => 'bytea int4 int4',
+  prosrc => 'byteaGetBytes' },
+{ oid => '8576', descr => 'set bytes',
+  proname => 'set_bytes', prorettype => 'bytea',
+  proargtypes => 'bytea int4 int4 int8', prosrc => 'byteaSetBytes' },
 { oid => '723', descr => 'get bit',
   proname => 'get_bit', prorettype => 'int4', proargtypes => 'bytea int8',
   prosrc => 'byteaGetBit' },
diff --git a/src/test/regress/expected/strings.out b/src/test/regress/expected/strings.out
index 709f58ad7f..c58fc9b918 100644
--- a/src/test/regress/expected/strings.out
+++ b/src/test/regress/expected/strings.out
@@ -2358,6 +2358,42 @@ SELECT set_byte('\x1234567890abcdef00'::bytea, 7, 11);
 
 SELECT set_byte('\x1234567890abcdef00'::bytea, 99, 11);  -- error
 ERROR:  index 99 out of valid range, 0..8
+SELECT get_bytes('\x1122334455667788'::bytea, 0, 8) = 0x1122334455667788;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT get_bytes('\x1122334455667788'::bytea, 1, 2) = 0x2233;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT get_bytes('\x1122334455667788'::bytea, 0, 0); -- error
+ERROR:  size 0 out of valid range, 1..8
+SELECT get_bytes('\x1122334455667788'::bytea, 0, 9); -- error
+ERROR:  size 9 out of valid range, 1..8
+SELECT get_bytes('\x1122334455667788'::bytea, 1, 8); -- error
+ERROR:  index 1 out of valid range, 0..0
+SELECT set_bytes('\x0123456789abcdef'::bytea, 0, 8, 0x1122334455667788);
+     set_bytes      
+--------------------
+ \x1122334455667788
+(1 row)
+
+SELECT set_bytes('\x1122334455667788'::bytea, 1, 2, 0xAABB);
+     set_bytes      
+--------------------
+ \x11aabb4455667788
+(1 row)
+
+SELECT set_bytes('\x0123456789abcdef'::bytea, 0, 0, 123); -- error
+ERROR:  size 0 out of valid range, 1..8
+SELECT set_bytes('\x0123456789abcdef'::bytea, 0, 9, 123); -- error
+ERROR:  size 9 out of valid range, 1..8
+SELECT set_bytes('\x0123456789abcdef'::bytea, 1, 8, 123); -- error
+ERROR:  index 1 out of valid range, 0..0
 --
 -- test behavior of escape_string_warning and standard_conforming_strings options
 --
diff --git a/src/test/regress/sql/strings.sql b/src/test/regress/sql/strings.sql
index b8858c9aea..82d677562a 100644
--- a/src/test/regress/sql/strings.sql
+++ b/src/test/regress/sql/strings.sql
@@ -750,6 +750,16 @@ SELECT get_byte('\x1234567890abcdef00'::bytea, 3);
 SELECT get_byte('\x1234567890abcdef00'::bytea, 99);  -- error
 SELECT set_byte('\x1234567890abcdef00'::bytea, 7, 11);
 SELECT set_byte('\x1234567890abcdef00'::bytea, 99, 11);  -- error
+SELECT get_bytes('\x1122334455667788'::bytea, 0, 8) = 0x1122334455667788;
+SELECT get_bytes('\x1122334455667788'::bytea, 1, 2) = 0x2233;
+SELECT get_bytes('\x1122334455667788'::bytea, 0, 0); -- error
+SELECT get_bytes('\x1122334455667788'::bytea, 0, 9); -- error
+SELECT get_bytes('\x1122334455667788'::bytea, 1, 8); -- error
+SELECT set_bytes('\x0123456789abcdef'::bytea, 0, 8, 0x1122334455667788);
+SELECT set_bytes('\x1122334455667788'::bytea, 1, 2, 0xAABB);
+SELECT set_bytes('\x0123456789abcdef'::bytea, 0, 0, 123); -- error
+SELECT set_bytes('\x0123456789abcdef'::bytea, 0, 9, 123); -- error
+SELECT set_bytes('\x0123456789abcdef'::bytea, 1, 8, 123); -- error
 
 --
 -- test behavior of escape_string_warning and standard_conforming_strings options
-- 
2.46.0

From b7cf59a809539cfaa5207cb23aca578af2b04977 Mon Sep 17 00:00:00 2001
From: Aleksander Alekseev <aleksander@timescale.com>
Date: Mon, 26 Aug 2024 12:09:59 +0300
Subject: [PATCH v5 1/2] Allow casting between bytea and integer types.

For instance:

SELECT '\x12345678'::bytea::integer;
SELECT 0x12345678::bytea;

This works with int2's, int4's and int8's.

Aleksander Alekseev, reviewed by TODO FIXME
Discussion: https://postgr.es/m/CAJ7c6TPtOp6%2BkFX5QX3fH1SVr7v65uHr-7yEJ%3DGMGQi5uhGtcA%40mail.gmail.com

BUMP CATVERSION
---
 src/backend/utils/adt/int.c              | 55 ++++++++++++++++
 src/include/catalog/pg_cast.dat          | 14 ++++
 src/include/catalog/pg_proc.dat          | 18 ++++++
 src/test/regress/expected/opr_sanity.out | 15 ++++-
 src/test/regress/expected/strings.out    | 82 ++++++++++++++++++++++++
 src/test/regress/sql/strings.sql         | 24 +++++++
 6 files changed, 205 insertions(+), 3 deletions(-)

diff --git a/src/backend/utils/adt/int.c b/src/backend/utils/adt/int.c
index 234f20796b..a9ab582283 100644
--- a/src/backend/utils/adt/int.c
+++ b/src/backend/utils/adt/int.c
@@ -330,6 +330,61 @@ int4send(PG_FUNCTION_ARGS)
 }
 
 
+/*
+ *		Common code for bytea_int2, bytea_int4 and bytea_int8
+ */
+static int64
+bytea_integer(bytea* v, int max_size)
+{
+	int 	len = VARSIZE_ANY_EXHDR(v);
+	int 	offset = 0;
+	int64 	result = 0;
+
+	if (len > max_size)
+		ereport(ERROR,
+			(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+			errmsg("bytea size %d out of valid range, 0..%d",
+					len, max_size)));
+	while (len--)
+	{
+		result = result << 8;
+		result |= ((unsigned char *) VARDATA_ANY(v))[offset];
+		offset++;
+	}
+
+	return result;
+}
+
+/*
+ *		bytea_int2			- converts bytea to int2
+ */
+Datum
+bytea_int2(PG_FUNCTION_ARGS)
+{
+	bytea	*v = PG_GETARG_BYTEA_PP(0);
+	PG_RETURN_INT16((int16)bytea_integer(v, sizeof(int16)));
+}
+
+/*
+ *		bytea_int4			- converts bytea to int4
+ */
+Datum
+bytea_int4(PG_FUNCTION_ARGS)
+{
+	bytea	*v = PG_GETARG_BYTEA_PP(0);
+	PG_RETURN_INT32((int32)bytea_integer(v, sizeof(int32)));
+}
+
+/*
+ *		bytea_int8			- converts bytea to int8
+ */
+Datum
+bytea_int8(PG_FUNCTION_ARGS)
+{
+	bytea	*v = PG_GETARG_BYTEA_PP(0);
+	PG_RETURN_INT64(bytea_integer(v, sizeof(int64)));
+}
+
 /*
  *		===================
  *		CONVERSION ROUTINES
diff --git a/src/include/catalog/pg_cast.dat b/src/include/catalog/pg_cast.dat
index ca7b6d7191..c0350e7f09 100644
--- a/src/include/catalog/pg_cast.dat
+++ b/src/include/catalog/pg_cast.dat
@@ -320,6 +320,20 @@
 { castsource => 'varchar', casttarget => 'name', castfunc => 'name(varchar)',
   castcontext => 'i', castmethod => 'f' },
 
+# Allow explicit coercions between bytea and integer types
+{ castsource => 'int2', casttarget => 'bytea', castfunc => 'bytea(int2)',
+  castcontext => 'e', castmethod => 'f' },
+{ castsource => 'int4', casttarget => 'bytea', castfunc => 'bytea(int4)',
+  castcontext => 'e', castmethod => 'f' },
+{ castsource => 'int8', casttarget => 'bytea', castfunc => 'bytea(int8)',
+  castcontext => 'e', castmethod => 'f' },
+{ castsource => 'bytea', casttarget => 'int2', castfunc => 'int2(bytea)',
+  castcontext => 'e', castmethod => 'f' },
+{ castsource => 'bytea', casttarget => 'int4', castfunc => 'int4(bytea)',
+  castcontext => 'e', castmethod => 'f' },
+{ castsource => 'bytea', casttarget => 'int8', castfunc => 'int8(bytea)',
+  castcontext => 'e', castmethod => 'f' },
+
 # Allow explicit coercions between int4 and "char"
 { castsource => 'char', casttarget => 'int4', castfunc => 'int4(char)',
   castcontext => 'e', castmethod => 'f' },
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 7c0b74fe05..0336110b0d 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -1164,6 +1164,24 @@
 { oid => '409', descr => 'convert char(n) to name',
   proname => 'name', proleakproof => 't', prorettype => 'name',
   proargtypes => 'bpchar', prosrc => 'bpchar_name' },
+{ oid => '8577', descr => 'convert int2 to bytea',
+  proname => 'bytea', proleakproof => 't', prorettype => 'bytea',
+  proargtypes => 'int2', prosrc => 'int2send' },
+{ oid => '8578', descr => 'convert int4 to bytea',
+  proname => 'bytea', proleakproof => 't', prorettype => 'bytea',
+  proargtypes => 'int4', prosrc => 'int4send' },
+{ oid => '8579', descr => 'convert int8 to bytea',
+  proname => 'bytea', proleakproof => 't', prorettype => 'bytea',
+  proargtypes => 'int8', prosrc => 'int8send' },
+{ oid => '8580', descr => 'convert bytea to int2',
+  proname => 'int2', proleakproof => 't', prorettype => 'int2',
+  proargtypes => 'bytea', prosrc => 'bytea_int2' },
+{ oid => '8581', descr => 'convert bytea to int4',
+  proname => 'int4', proleakproof => 't', prorettype => 'int4',
+  proargtypes => 'bytea', prosrc => 'bytea_int4' },
+{ oid => '8582', descr => 'convert bytea to int8',
+  proname => 'int8', proleakproof => 't', prorettype => 'int8',
+  proargtypes => 'bytea', prosrc => 'bytea_int8' },
 
 { oid => '449', descr => 'hash',
   proname => 'hashint2', prorettype => 'int4', proargtypes => 'int2',
diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out
index 34a32bd11d..4b1ebef8fa 100644
--- a/src/test/regress/expected/opr_sanity.out
+++ b/src/test/regress/expected/opr_sanity.out
@@ -126,9 +126,12 @@ WHERE p1.oid < p2.oid AND
      p1.proretset != p2.proretset OR
      p1.provolatile != p2.provolatile OR
      p1.pronargs != p2.pronargs);
- oid | proname | oid | proname 
------+---------+-----+---------
-(0 rows)
+ oid  | proname  | oid  | proname 
+------+----------+------+---------
+ 2405 | int2send | 8577 | bytea
+ 2407 | int4send | 8578 | bytea
+ 2409 | int8send | 8579 | bytea
+(3 rows)
 
 -- Look for uses of different type OIDs in the argument/result type fields
 -- for different aliases of the same built-in function.
@@ -876,6 +879,12 @@ uuid_extract_timestamp(uuid)
 uuid_extract_version(uuid)
 crc32(bytea)
 crc32c(bytea)
+bytea(smallint)
+bytea(integer)
+bytea(bigint)
+int2(bytea)
+int4(bytea)
+int8(bytea)
 bytea_larger(bytea,bytea)
 bytea_smaller(bytea,bytea)
 -- restore normal output mode
diff --git a/src/test/regress/expected/strings.out b/src/test/regress/expected/strings.out
index b65bb2d536..709f58ad7f 100644
--- a/src/test/regress/expected/strings.out
+++ b/src/test/regress/expected/strings.out
@@ -2690,3 +2690,85 @@ ERROR:  invalid Unicode code point: 2FFFFF
 SELECT unistr('wrong: \xyz');
 ERROR:  invalid Unicode escape
 HINT:  Unicode escapes must be \XXXX, \+XXXXXX, \uXXXX, or \UXXXXXXXX.
+--
+-- Test coercions between bytea and integer types
+--
+SET bytea_output TO hex;
+SELECT 0x1234::int2::bytea;
+ bytea  
+--------
+ \x1234
+(1 row)
+
+SELECT 0x12345678::int4::bytea;
+   bytea    
+------------
+ \x12345678
+(1 row)
+
+SELECT 0x1122334455667788::int8::bytea;
+       bytea        
+--------------------
+ \x1122334455667788
+(1 row)
+
+SELECT ''::bytea::int2 = 0;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x12'::bytea::int2 = 0x12;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x1234'::bytea::int2 = 0x1234;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x123456'::bytea::int2; -- error
+ERROR:  bytea size 3 out of valid range, 0..2
+SELECT ''::bytea::int4 = 0;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x12'::bytea::int4 = 0x12;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x12345678'::bytea::int4 = 0x12345678;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x123456789A'::bytea::int4; -- error
+ERROR:  bytea size 5 out of valid range, 0..4
+SELECT ''::bytea::int8 = 0;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x12'::bytea::int8 = 0x12;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x1122334455667788'::bytea::int8 = 0x1122334455667788;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x112233445566778899'::bytea::int8; -- error
+ERROR:  bytea size 9 out of valid range, 0..8
diff --git a/src/test/regress/sql/strings.sql b/src/test/regress/sql/strings.sql
index 8e0f3a0e75..b8858c9aea 100644
--- a/src/test/regress/sql/strings.sql
+++ b/src/test/regress/sql/strings.sql
@@ -848,3 +848,27 @@ SELECT unistr('wrong: \udb99\u0061');
 SELECT unistr('wrong: \U0000db99\U00000061');
 SELECT unistr('wrong: \U002FFFFF');
 SELECT unistr('wrong: \xyz');
+
+--
+-- Test coercions between bytea and integer types
+--
+SET bytea_output TO hex;
+
+SELECT 0x1234::int2::bytea;
+SELECT 0x12345678::int4::bytea;
+SELECT 0x1122334455667788::int8::bytea;
+
+SELECT ''::bytea::int2 = 0;
+SELECT '\x12'::bytea::int2 = 0x12;
+SELECT '\x1234'::bytea::int2 = 0x1234;
+SELECT '\x123456'::bytea::int2; -- error
+
+SELECT ''::bytea::int4 = 0;
+SELECT '\x12'::bytea::int4 = 0x12;
+SELECT '\x12345678'::bytea::int4 = 0x12345678;
+SELECT '\x123456789A'::bytea::int4; -- error
+
+SELECT ''::bytea::int8 = 0;
+SELECT '\x12'::bytea::int8 = 0x12;
+SELECT '\x1122334455667788'::bytea::int8 = 0x1122334455667788;
+SELECT '\x112233445566778899'::bytea::int8; -- error
-- 
2.47.0

From 6fd52c99926bfdac215a94afc00f2b5a8afb3690 Mon Sep 17 00:00:00 2001
From: Aleksander Alekseev <aleksander@timescale.com>
Date: Wed, 14 Aug 2024 12:11:47 +0300
Subject: [PATCH v5 2/2] Add get_bytes() and set_bytes() functions.

The new functions provide a convenient way of extracting integers from
bytea's and putting integer values into bytea's. Previously there were only
get_byte() and set_byte() which operate with a single byte.

Aleksander Alekseev, reviewed by Joel Jacobson, Yugo Nagata
Discussion: https://postgr.es/m/CAJ7c6TPtOp6%2BkFX5QX3fH1SVr7v65uHr-7yEJ%3DGMGQi5uhGtcA%40mail.gmail.com

BUMP CATVERSION
---
 doc/src/sgml/func.sgml                | 46 ++++++++++++++-
 src/backend/utils/adt/varlena.c       | 80 +++++++++++++++++++++++++++
 src/include/catalog/pg_proc.dat       |  6 ++
 src/test/regress/expected/strings.out | 36 ++++++++++++
 src/test/regress/sql/strings.sql      | 10 ++++
 5 files changed, 177 insertions(+), 1 deletion(-)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index f8a0d76d12..5d907c9e44 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -4562,6 +4562,27 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>get_bytes</primary>
+        </indexterm>
+        <function>get_bytes</function> ( <parameter>bytes</parameter> <type>bytea</type>,
+        <parameter>offset</parameter> <type>integer</type>,
+        <parameter>size</parameter> <type>integer</type> )
+        <returnvalue>bigint</returnvalue>
+       </para>
+       <para>
+        Extracts <parameter>size</parameter> bytes at a given
+        <link linkend="functions-zerobased-note">offset</link>
+        from binary string.
+       </para>
+       <para>
+        <literal>get_bytes('\x0123456789ABCDEF'::bytea, 5, 2)</literal>
+        <returnvalue>43981</returnvalue>
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
@@ -4662,6 +4683,28 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
        </para></entry>
       </row>
 
+      <row>
+       <entry role="func_table_entry"><para role="func_signature">
+        <indexterm>
+         <primary>set_bytes</primary>
+        </indexterm>
+        <function>set_bytes</function> ( <parameter>bytes</parameter> <type>bytea</type>,
+        <parameter>offset</parameter> <type>integer</type>,
+        <parameter>size</parameter> <type>integer</type>,
+        <parameter>newvalue</parameter> <type>bigint</type> )
+        <returnvalue>bytea</returnvalue>
+       </para>
+       <para>
+        Sets <parameter>size</parameter> bytes at a given
+        <link linkend="functions-zerobased-note">offset</link>
+        in binary string to <parameter>newvalue</parameter>.
+       </para>
+       <para>
+        <literal>set_bytes('\x0123456789abcdef'::bytea, 5, 2, 0x1122)</literal>
+        <returnvalue>\x01234567891122ef</returnvalue>
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
@@ -4761,7 +4804,8 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
   </table>
 
   <para id="functions-zerobased-note">
-   Functions <function>get_byte</function> and <function>set_byte</function>
+   Functions <function>get_byte</function>, <function>set_byte</function>,
+   <function>get_bytes</function> and <function>set_bytes</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
diff --git a/src/backend/utils/adt/varlena.c b/src/backend/utils/adt/varlena.c
index 533bebc1c7..0a1560556d 100644
--- a/src/backend/utils/adt/varlena.c
+++ b/src/backend/utils/adt/varlena.c
@@ -3201,6 +3201,46 @@ byteaGetByte(PG_FUNCTION_ARGS)
 	PG_RETURN_INT32(byte);
 }
 
+/*-------------------------------------------------------------
+ * byteaGetBytes
+ *
+ * This routine treats "bytea" as an array of bytes.
+ * It returns the N bytes at a given offset as a bigint value.
+ *-------------------------------------------------------------
+ */
+Datum
+byteaGetBytes(PG_FUNCTION_ARGS)
+{
+	bytea	   *v = PG_GETARG_BYTEA_PP(0);
+	int32		offset = PG_GETARG_INT32(1);
+	int32		size = PG_GETARG_INT32(2);
+	int64		result = 0;
+	int			len;
+
+	if (size < 1 || size > 8)
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("size %d out of valid range, 1..8",
+						size)));
+
+	len = VARSIZE_ANY_EXHDR(v);
+
+	if (offset < 0 || offset > (len-size))
+		ereport(ERROR,
+				(errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR),
+				 errmsg("index %d out of valid range, 0..%d",
+						offset, len - size)));
+
+	while (size--)
+	{
+		result = result << 8;
+		result |= ((unsigned char *) VARDATA_ANY(v))[offset];
+		offset++;
+	}
+
+	PG_RETURN_INT64(result);
+}
+
 /*-------------------------------------------------------------
  * byteaGetBit
  *
@@ -3271,6 +3311,46 @@ byteaSetByte(PG_FUNCTION_ARGS)
 	PG_RETURN_BYTEA_P(res);
 }
 
+/*-------------------------------------------------------------
+ * byteaSetBytes
+ *
+ * Given an instance of type 'bytea' creates a new one with
+ * the N bytes at a given offset set to the provided bigint value.
+ *
+ *-------------------------------------------------------------
+ */
+Datum
+byteaSetBytes(PG_FUNCTION_ARGS)
+{
+	bytea	   *res = PG_GETARG_BYTEA_P_COPY(0);
+	int32		offset = PG_GETARG_INT32(1);
+	int32		size = PG_GETARG_INT32(2);
+	int64		newValue = PG_GETARG_INT64(3);
+	int			len;
+
+	if (size < 1 || size > 8)
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("size %d out of valid range, 1..8",
+						size)));
+
+	len = VARSIZE_ANY_EXHDR(res);
+
+	if (offset < 0 || offset > (len-size))
+		ereport(ERROR,
+				(errcode(ERRCODE_ARRAY_SUBSCRIPT_ERROR),
+				 errmsg("index %d out of valid range, 0..%d",
+						offset, len - size)));
+	while (size)
+	{
+		((unsigned char*) VARDATA_ANY(res))[offset+size-1] = newValue & 0xFF;
+		newValue = newValue >> 8;
+		size--;
+	}
+
+	PG_RETURN_BYTEA_P(res);
+}
+
 /*-------------------------------------------------------------
  * byteaSetBit
  *
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 0336110b0d..8ff28a4139 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -1502,6 +1502,12 @@
 { oid => '722', descr => 'set byte',
   proname => 'set_byte', prorettype => 'bytea',
   proargtypes => 'bytea int4 int4', prosrc => 'byteaSetByte' },
+{ oid => '8575', descr => 'get bytes',
+  proname => 'get_bytes', prorettype => 'int8', proargtypes => 'bytea int4 int4',
+  prosrc => 'byteaGetBytes' },
+{ oid => '8576', descr => 'set bytes',
+  proname => 'set_bytes', prorettype => 'bytea',
+  proargtypes => 'bytea int4 int4 int8', prosrc => 'byteaSetBytes' },
 { oid => '723', descr => 'get bit',
   proname => 'get_bit', prorettype => 'int4', proargtypes => 'bytea int8',
   prosrc => 'byteaGetBit' },
diff --git a/src/test/regress/expected/strings.out b/src/test/regress/expected/strings.out
index 709f58ad7f..c58fc9b918 100644
--- a/src/test/regress/expected/strings.out
+++ b/src/test/regress/expected/strings.out
@@ -2358,6 +2358,42 @@ SELECT set_byte('\x1234567890abcdef00'::bytea, 7, 11);
 
 SELECT set_byte('\x1234567890abcdef00'::bytea, 99, 11);  -- error
 ERROR:  index 99 out of valid range, 0..8
+SELECT get_bytes('\x1122334455667788'::bytea, 0, 8) = 0x1122334455667788;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT get_bytes('\x1122334455667788'::bytea, 1, 2) = 0x2233;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT get_bytes('\x1122334455667788'::bytea, 0, 0); -- error
+ERROR:  size 0 out of valid range, 1..8
+SELECT get_bytes('\x1122334455667788'::bytea, 0, 9); -- error
+ERROR:  size 9 out of valid range, 1..8
+SELECT get_bytes('\x1122334455667788'::bytea, 1, 8); -- error
+ERROR:  index 1 out of valid range, 0..0
+SELECT set_bytes('\x0123456789abcdef'::bytea, 0, 8, 0x1122334455667788);
+     set_bytes      
+--------------------
+ \x1122334455667788
+(1 row)
+
+SELECT set_bytes('\x1122334455667788'::bytea, 1, 2, 0xAABB);
+     set_bytes      
+--------------------
+ \x11aabb4455667788
+(1 row)
+
+SELECT set_bytes('\x0123456789abcdef'::bytea, 0, 0, 123); -- error
+ERROR:  size 0 out of valid range, 1..8
+SELECT set_bytes('\x0123456789abcdef'::bytea, 0, 9, 123); -- error
+ERROR:  size 9 out of valid range, 1..8
+SELECT set_bytes('\x0123456789abcdef'::bytea, 1, 8, 123); -- error
+ERROR:  index 1 out of valid range, 0..0
 --
 -- test behavior of escape_string_warning and standard_conforming_strings options
 --
diff --git a/src/test/regress/sql/strings.sql b/src/test/regress/sql/strings.sql
index b8858c9aea..82d677562a 100644
--- a/src/test/regress/sql/strings.sql
+++ b/src/test/regress/sql/strings.sql
@@ -750,6 +750,16 @@ SELECT get_byte('\x1234567890abcdef00'::bytea, 3);
 SELECT get_byte('\x1234567890abcdef00'::bytea, 99);  -- error
 SELECT set_byte('\x1234567890abcdef00'::bytea, 7, 11);
 SELECT set_byte('\x1234567890abcdef00'::bytea, 99, 11);  -- error
+SELECT get_bytes('\x1122334455667788'::bytea, 0, 8) = 0x1122334455667788;
+SELECT get_bytes('\x1122334455667788'::bytea, 1, 2) = 0x2233;
+SELECT get_bytes('\x1122334455667788'::bytea, 0, 0); -- error
+SELECT get_bytes('\x1122334455667788'::bytea, 0, 9); -- error
+SELECT get_bytes('\x1122334455667788'::bytea, 1, 8); -- error
+SELECT set_bytes('\x0123456789abcdef'::bytea, 0, 8, 0x1122334455667788);
+SELECT set_bytes('\x1122334455667788'::bytea, 1, 2, 0xAABB);
+SELECT set_bytes('\x0123456789abcdef'::bytea, 0, 0, 123); -- error
+SELECT set_bytes('\x0123456789abcdef'::bytea, 0, 9, 123); -- error
+SELECT set_bytes('\x0123456789abcdef'::bytea, 1, 8, 123); -- error
 
 --
 -- test behavior of escape_string_warning and standard_conforming_strings options
-- 
2.47.0

From 4b25ab46f10c8d284b2b2f8f3a7998a7b00c12d0 Mon Sep 17 00:00:00 2001
From: Aleksander Alekseev <aleksander@timescale.com>
Date: Mon, 26 Aug 2024 12:09:59 +0300
Subject: [PATCH v6] Allow casting between bytea and integer types.

For instance:

SELECT '\x12345678'::bytea::integer;
SELECT 0x12345678::bytea;

This works with int2's, int4's and int8's.

Aleksander Alekseev, reviewed by Peter Eisentraut, Michael Paquier

Discussion: https://postgr.es/m/CAJ7c6TPtOp6%2BkFX5QX3fH1SVr7v65uHr-7yEJ%3DGMGQi5uhGtcA%40mail.gmail.com

BUMP CATVERSION
---
 src/backend/utils/adt/int.c              |  68 +++++++++++++
 src/include/catalog/pg_cast.dat          |  14 +++
 src/include/catalog/pg_proc.dat          |  18 ++++
 src/test/regress/expected/opr_sanity.out |   6 ++
 src/test/regress/expected/strings.out    | 120 +++++++++++++++++++++++
 src/test/regress/sql/strings.sql         |  34 +++++++
 6 files changed, 260 insertions(+)

diff --git a/src/backend/utils/adt/int.c b/src/backend/utils/adt/int.c
index b5781989a64..a240f647a48 100644
--- a/src/backend/utils/adt/int.c
+++ b/src/backend/utils/adt/int.c
@@ -336,6 +336,74 @@ int4send(PG_FUNCTION_ARGS)
  *		===================
  */
 
+/* Common code for bytea_int2, bytea_int4 and bytea_int8 */
+static int64
+bytea_integer(bytea* v, int max_size, const char* max_value)
+{
+	int 	len = VARSIZE_ANY_EXHDR(v);
+	int 	offset = 0;
+	int64 	result = 0;
+
+	if (len > max_size)
+		ereport(ERROR,
+			(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+			errmsg("bytea out of valid range, ''..'\\x%s'",
+					max_value)));
+	while (len--)
+	{
+		result = result << 8;
+		result |= ((unsigned char *) VARDATA_ANY(v))[offset];
+		offset++;
+	}
+
+	return result;
+}
+
+/* Cast bytea -> int2 */
+Datum
+bytea_int2(PG_FUNCTION_ARGS)
+{
+	bytea	*v = PG_GETARG_BYTEA_PP(0);
+	PG_RETURN_INT16((int16)bytea_integer(v, sizeof(int16), "FFFF"));
+}
+
+/* Cast bytea -> int4 */
+Datum
+bytea_int4(PG_FUNCTION_ARGS)
+{
+	bytea	*v = PG_GETARG_BYTEA_PP(0);
+	PG_RETURN_INT32((int32)bytea_integer(v, sizeof(int32), "FFFFFFFF"));
+}
+
+/* Cast bytea -> int8 */
+Datum
+bytea_int8(PG_FUNCTION_ARGS)
+{
+	bytea	*v = PG_GETARG_BYTEA_PP(0);
+	PG_RETURN_INT64(bytea_integer(v, sizeof(int64), "FFFFFFFFFFFFFFFF"));
+}
+
+/* Cast int2 -> bytea; currently just a wrapper for int2send() */
+Datum
+int2_bytea(PG_FUNCTION_ARGS)
+{
+	return int2send(fcinfo);
+}
+
+/* Cast int4 -> bytea; currently just a wrapper for int4send() */
+Datum
+int4_bytea(PG_FUNCTION_ARGS)
+{
+	return int4send(fcinfo);
+}
+
+/* Cast int8 -> bytea; currently just a wrapper for int8send() */
+Datum
+int8_bytea(PG_FUNCTION_ARGS)
+{
+	return int8send(fcinfo);
+}
+
 Datum
 i2toi4(PG_FUNCTION_ARGS)
 {
diff --git a/src/include/catalog/pg_cast.dat b/src/include/catalog/pg_cast.dat
index a26ba34e869..ab46be606f0 100644
--- a/src/include/catalog/pg_cast.dat
+++ b/src/include/catalog/pg_cast.dat
@@ -320,6 +320,20 @@
 { castsource => 'varchar', casttarget => 'name', castfunc => 'name(varchar)',
   castcontext => 'i', castmethod => 'f' },
 
+# Allow explicit coercions between bytea and integer types
+{ castsource => 'int2', casttarget => 'bytea', castfunc => 'bytea(int2)',
+  castcontext => 'e', castmethod => 'f' },
+{ castsource => 'int4', casttarget => 'bytea', castfunc => 'bytea(int4)',
+  castcontext => 'e', castmethod => 'f' },
+{ castsource => 'int8', casttarget => 'bytea', castfunc => 'bytea(int8)',
+  castcontext => 'e', castmethod => 'f' },
+{ castsource => 'bytea', casttarget => 'int2', castfunc => 'int2(bytea)',
+  castcontext => 'e', castmethod => 'f' },
+{ castsource => 'bytea', casttarget => 'int4', castfunc => 'int4(bytea)',
+  castcontext => 'e', castmethod => 'f' },
+{ castsource => 'bytea', casttarget => 'int8', castfunc => 'int8(bytea)',
+  castcontext => 'e', castmethod => 'f' },
+
 # Allow explicit coercions between int4 and "char"
 { castsource => 'char', casttarget => 'int4', castfunc => 'int4(char)',
   castcontext => 'e', castmethod => 'f' },
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index b37e8a6f882..c9d9b91e282 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -1164,6 +1164,24 @@
 { oid => '409', descr => 'convert char(n) to name',
   proname => 'name', proleakproof => 't', prorettype => 'name',
   proargtypes => 'bpchar', prosrc => 'bpchar_name' },
+{ oid => '8577', descr => 'convert int2 to bytea',
+  proname => 'bytea', proleakproof => 't', prorettype => 'bytea',
+  proargtypes => 'int2', prosrc => 'int2_bytea' },
+{ oid => '8578', descr => 'convert int4 to bytea',
+  proname => 'bytea', proleakproof => 't', prorettype => 'bytea',
+  proargtypes => 'int4', prosrc => 'int4_bytea' },
+{ oid => '8579', descr => 'convert int8 to bytea',
+  proname => 'bytea', proleakproof => 't', prorettype => 'bytea',
+  proargtypes => 'int8', prosrc => 'int8_bytea' },
+{ oid => '8580', descr => 'convert bytea to int2',
+  proname => 'int2', proleakproof => 't', prorettype => 'int2',
+  proargtypes => 'bytea', prosrc => 'bytea_int2' },
+{ oid => '8581', descr => 'convert bytea to int4',
+  proname => 'int4', proleakproof => 't', prorettype => 'int4',
+  proargtypes => 'bytea', prosrc => 'bytea_int4' },
+{ oid => '8582', descr => 'convert bytea to int8',
+  proname => 'int8', proleakproof => 't', prorettype => 'int8',
+  proargtypes => 'bytea', prosrc => 'bytea_int8' },
 
 { oid => '449', descr => 'hash',
   proname => 'hashint2', prorettype => 'int4', proargtypes => 'int2',
diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out
index b673642ad1d..462c56b7887 100644
--- a/src/test/regress/expected/opr_sanity.out
+++ b/src/test/regress/expected/opr_sanity.out
@@ -875,6 +875,12 @@ uuid_extract_timestamp(uuid)
 uuid_extract_version(uuid)
 crc32(bytea)
 crc32c(bytea)
+bytea(smallint)
+bytea(integer)
+bytea(bigint)
+int2(bytea)
+int4(bytea)
+int8(bytea)
 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..e35ae5d29c5 100644
--- a/src/test/regress/expected/strings.out
+++ b/src/test/regress/expected/strings.out
@@ -2690,3 +2690,123 @@ ERROR:  invalid Unicode code point: 2FFFFF
 SELECT unistr('wrong: \xyz');
 ERROR:  invalid Unicode escape
 HINT:  Unicode escapes must be \XXXX, \+XXXXXX, \uXXXX, or \UXXXXXXXX.
+--
+-- Test coercions between bytea and integer types
+--
+SET bytea_output TO hex;
+SELECT 0x1234::int2::bytea;
+ bytea  
+--------
+ \x1234
+(1 row)
+
+SELECT 0x12345678::int4::bytea;
+   bytea    
+------------
+ \x12345678
+(1 row)
+
+SELECT 0x1122334455667788::int8::bytea;
+       bytea        
+--------------------
+ \x1122334455667788
+(1 row)
+
+SELECT ''::bytea::int2 = 0;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x12'::bytea::int2 = 0x12;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x1234'::bytea::int2 = 0x1234;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x123456'::bytea::int2; -- error
+ERROR:  bytea out of valid range, ''..'\xFFFF'
+SELECT ''::bytea::int4 = 0;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x12'::bytea::int4 = 0x12;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x12345678'::bytea::int4 = 0x12345678;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x123456789A'::bytea::int4; -- error
+ERROR:  bytea out of valid range, ''..'\xFFFFFFFF'
+SELECT ''::bytea::int8 = 0;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x12'::bytea::int8 = 0x12;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x1122334455667788'::bytea::int8 = 0x1122334455667788;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x112233445566778899'::bytea::int8; -- error
+ERROR:  bytea out of valid range, ''..'\xFFFFFFFFFFFFFFFF'
+-- max integer values
+SELECT '\x7FFF'::bytea::int2 = 0x7FFF;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x7FFFFFFF'::bytea::int4 = 0x7FFFFFFF;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x7FFFFFFFFFFFFFFF'::bytea::int8 = 0x7FFFFFFFFFFFFFFF;
+ ?column? 
+----------
+ t
+(1 row)
+
+-- min integer values
+SELECT '\x8000'::bytea::int2 = -0x8000;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x80000000'::bytea::int4 = -0x80000000;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x8000000000000000'::bytea::int8 = -0x8000000000000000;
+ ?column? 
+----------
+ t
+(1 row)
+
diff --git a/src/test/regress/sql/strings.sql b/src/test/regress/sql/strings.sql
index 8e0f3a0e75f..16cceb5c746 100644
--- a/src/test/regress/sql/strings.sql
+++ b/src/test/regress/sql/strings.sql
@@ -848,3 +848,37 @@ SELECT unistr('wrong: \udb99\u0061');
 SELECT unistr('wrong: \U0000db99\U00000061');
 SELECT unistr('wrong: \U002FFFFF');
 SELECT unistr('wrong: \xyz');
+
+--
+-- Test coercions between bytea and integer types
+--
+SET bytea_output TO hex;
+
+SELECT 0x1234::int2::bytea;
+SELECT 0x12345678::int4::bytea;
+SELECT 0x1122334455667788::int8::bytea;
+
+SELECT ''::bytea::int2 = 0;
+SELECT '\x12'::bytea::int2 = 0x12;
+SELECT '\x1234'::bytea::int2 = 0x1234;
+SELECT '\x123456'::bytea::int2; -- error
+
+SELECT ''::bytea::int4 = 0;
+SELECT '\x12'::bytea::int4 = 0x12;
+SELECT '\x12345678'::bytea::int4 = 0x12345678;
+SELECT '\x123456789A'::bytea::int4; -- error
+
+SELECT ''::bytea::int8 = 0;
+SELECT '\x12'::bytea::int8 = 0x12;
+SELECT '\x1122334455667788'::bytea::int8 = 0x1122334455667788;
+SELECT '\x112233445566778899'::bytea::int8; -- error
+
+-- max integer values
+SELECT '\x7FFF'::bytea::int2 = 0x7FFF;
+SELECT '\x7FFFFFFF'::bytea::int4 = 0x7FFFFFFF;
+SELECT '\x7FFFFFFFFFFFFFFF'::bytea::int8 = 0x7FFFFFFFFFFFFFFF;
+
+-- min integer values
+SELECT '\x8000'::bytea::int2 = -0x8000;
+SELECT '\x80000000'::bytea::int4 = -0x80000000;
+SELECT '\x8000000000000000'::bytea::int8 = -0x8000000000000000;
-- 
2.47.1

From b9b5e358d00945bc0c4bb4a1b6e52497a6014690 Mon Sep 17 00:00:00 2001
From: Aleksander Alekseev <aleksander@timescale.com>
Date: Mon, 26 Aug 2024 12:09:59 +0300
Subject: [PATCH v7] Allow casting between bytea and integer types.

For instance:

SELECT '\x12345678'::bytea::integer;
SELECT 0x12345678::bytea;

This works with int2's, int4's and int8's.

Author: Aleksander Alekseev
Reviewed-by: Peter Eisentraut, Michael Paquier, Dean Rasheed
Discussion: https://postgr.es/m/CAJ7c6TPtOp6%2BkFX5QX3fH1SVr7v65uHr-7yEJ%3DGMGQi5uhGtcA%40mail.gmail.com

BUMP CATVERSION
---
 src/backend/utils/adt/int.c              |  84 ++++++++++++++++
 src/include/catalog/pg_cast.dat          |  14 +++
 src/include/catalog/pg_proc.dat          |  18 ++++
 src/test/regress/expected/opr_sanity.out |   6 ++
 src/test/regress/expected/strings.out    | 120 +++++++++++++++++++++++
 src/test/regress/sql/strings.sql         |  34 +++++++
 6 files changed, 276 insertions(+)

diff --git a/src/backend/utils/adt/int.c b/src/backend/utils/adt/int.c
index b5781989a64..0be739bae64 100644
--- a/src/backend/utils/adt/int.c
+++ b/src/backend/utils/adt/int.c
@@ -336,6 +336,90 @@ int4send(PG_FUNCTION_ARGS)
  *		===================
  */
 
+/* Common code for bytea_int2, bytea_int4 and bytea_int8 */
+static int64
+bytea_integer(const bytea *v, int len)
+{
+	int			offset = 0;
+	int64		result = 0;
+
+	while (len--)
+	{
+		result = result << 8;
+		result |= ((unsigned char *) VARDATA_ANY(v))[offset];
+		offset++;
+	}
+
+	return result;
+}
+
+/* Cast bytea -> int2 */
+Datum
+bytea_int2(PG_FUNCTION_ARGS)
+{
+	bytea	   *v = PG_GETARG_BYTEA_PP(0);
+	int			len = VARSIZE_ANY_EXHDR(v);
+
+	if (len > sizeof(int16))
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("smallint out of range")));
+
+	PG_RETURN_INT16((int16) bytea_integer(v, len));
+}
+
+/* Cast bytea -> int4 */
+Datum
+bytea_int4(PG_FUNCTION_ARGS)
+{
+	bytea	   *v = PG_GETARG_BYTEA_PP(0);
+	int			len = VARSIZE_ANY_EXHDR(v);
+
+	if (len > sizeof(int32))
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("integer out of range")));
+
+	PG_RETURN_INT32((int32) bytea_integer(v, len));
+}
+
+/* Cast bytea -> int8 */
+Datum
+bytea_int8(PG_FUNCTION_ARGS)
+{
+	bytea	   *v = PG_GETARG_BYTEA_PP(0);
+	int			len = VARSIZE_ANY_EXHDR(v);
+
+	if (len > sizeof(int64))
+		ereport(ERROR,
+				(errcode(ERRCODE_INVALID_PARAMETER_VALUE),
+				 errmsg("bigint out of range")));
+
+
+	PG_RETURN_INT64(bytea_integer(v, len));
+}
+
+/* Cast int2 -> bytea; currently just a wrapper for int2send() */
+Datum
+int2_bytea(PG_FUNCTION_ARGS)
+{
+	return int2send(fcinfo);
+}
+
+/* Cast int4 -> bytea; currently just a wrapper for int4send() */
+Datum
+int4_bytea(PG_FUNCTION_ARGS)
+{
+	return int4send(fcinfo);
+}
+
+/* Cast int8 -> bytea; currently just a wrapper for int8send() */
+Datum
+int8_bytea(PG_FUNCTION_ARGS)
+{
+	return int8send(fcinfo);
+}
+
 Datum
 i2toi4(PG_FUNCTION_ARGS)
 {
diff --git a/src/include/catalog/pg_cast.dat b/src/include/catalog/pg_cast.dat
index a26ba34e869..ab46be606f0 100644
--- a/src/include/catalog/pg_cast.dat
+++ b/src/include/catalog/pg_cast.dat
@@ -320,6 +320,20 @@
 { castsource => 'varchar', casttarget => 'name', castfunc => 'name(varchar)',
   castcontext => 'i', castmethod => 'f' },
 
+# Allow explicit coercions between bytea and integer types
+{ castsource => 'int2', casttarget => 'bytea', castfunc => 'bytea(int2)',
+  castcontext => 'e', castmethod => 'f' },
+{ castsource => 'int4', casttarget => 'bytea', castfunc => 'bytea(int4)',
+  castcontext => 'e', castmethod => 'f' },
+{ castsource => 'int8', casttarget => 'bytea', castfunc => 'bytea(int8)',
+  castcontext => 'e', castmethod => 'f' },
+{ castsource => 'bytea', casttarget => 'int2', castfunc => 'int2(bytea)',
+  castcontext => 'e', castmethod => 'f' },
+{ castsource => 'bytea', casttarget => 'int4', castfunc => 'int4(bytea)',
+  castcontext => 'e', castmethod => 'f' },
+{ castsource => 'bytea', casttarget => 'int8', castfunc => 'int8(bytea)',
+  castcontext => 'e', castmethod => 'f' },
+
 # Allow explicit coercions between int4 and "char"
 { castsource => 'char', casttarget => 'int4', castfunc => 'int4(char)',
   castcontext => 'e', castmethod => 'f' },
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 872cd6e01a3..18187f9375a 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -1164,6 +1164,24 @@
 { oid => '409', descr => 'convert char(n) to name',
   proname => 'name', proleakproof => 't', prorettype => 'name',
   proargtypes => 'bpchar', prosrc => 'bpchar_name' },
+{ oid => '8577', descr => 'convert int2 to bytea',
+  proname => 'bytea', proleakproof => 't', prorettype => 'bytea',
+  proargtypes => 'int2', prosrc => 'int2_bytea' },
+{ oid => '8578', descr => 'convert int4 to bytea',
+  proname => 'bytea', proleakproof => 't', prorettype => 'bytea',
+  proargtypes => 'int4', prosrc => 'int4_bytea' },
+{ oid => '8579', descr => 'convert int8 to bytea',
+  proname => 'bytea', proleakproof => 't', prorettype => 'bytea',
+  proargtypes => 'int8', prosrc => 'int8_bytea' },
+{ oid => '8580', descr => 'convert bytea to int2',
+  proname => 'int2', proleakproof => 't', prorettype => 'int2',
+  proargtypes => 'bytea', prosrc => 'bytea_int2' },
+{ oid => '8581', descr => 'convert bytea to int4',
+  proname => 'int4', proleakproof => 't', prorettype => 'int4',
+  proargtypes => 'bytea', prosrc => 'bytea_int4' },
+{ oid => '8582', descr => 'convert bytea to int8',
+  proname => 'int8', proleakproof => 't', prorettype => 'int8',
+  proargtypes => 'bytea', prosrc => 'bytea_int8' },
 
 { oid => '449', descr => 'hash',
   proname => 'hashint2', prorettype => 'int4', proargtypes => 'int2',
diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out
index b673642ad1d..462c56b7887 100644
--- a/src/test/regress/expected/opr_sanity.out
+++ b/src/test/regress/expected/opr_sanity.out
@@ -875,6 +875,12 @@ uuid_extract_timestamp(uuid)
 uuid_extract_version(uuid)
 crc32(bytea)
 crc32c(bytea)
+bytea(smallint)
+bytea(integer)
+bytea(bigint)
+int2(bytea)
+int4(bytea)
+int8(bytea)
 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..69f1e66aa98 100644
--- a/src/test/regress/expected/strings.out
+++ b/src/test/regress/expected/strings.out
@@ -2690,3 +2690,123 @@ ERROR:  invalid Unicode code point: 2FFFFF
 SELECT unistr('wrong: \xyz');
 ERROR:  invalid Unicode escape
 HINT:  Unicode escapes must be \XXXX, \+XXXXXX, \uXXXX, or \UXXXXXXXX.
+--
+-- Test coercions between bytea and integer types
+--
+SET bytea_output TO hex;
+SELECT 0x1234::int2::bytea;
+ bytea  
+--------
+ \x1234
+(1 row)
+
+SELECT 0x12345678::int4::bytea;
+   bytea    
+------------
+ \x12345678
+(1 row)
+
+SELECT 0x1122334455667788::int8::bytea;
+       bytea        
+--------------------
+ \x1122334455667788
+(1 row)
+
+SELECT ''::bytea::int2 = 0;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x12'::bytea::int2 = 0x12;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x1234'::bytea::int2 = 0x1234;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x123456'::bytea::int2; -- error
+ERROR:  smallint out of range
+SELECT ''::bytea::int4 = 0;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x12'::bytea::int4 = 0x12;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x12345678'::bytea::int4 = 0x12345678;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x123456789A'::bytea::int4; -- error
+ERROR:  integer out of range
+SELECT ''::bytea::int8 = 0;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x12'::bytea::int8 = 0x12;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x1122334455667788'::bytea::int8 = 0x1122334455667788;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x112233445566778899'::bytea::int8; -- error
+ERROR:  bigint out of range
+-- max integer values
+SELECT '\x7FFF'::bytea::int2 = 0x7FFF;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x7FFFFFFF'::bytea::int4 = 0x7FFFFFFF;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x7FFFFFFFFFFFFFFF'::bytea::int8 = 0x7FFFFFFFFFFFFFFF;
+ ?column? 
+----------
+ t
+(1 row)
+
+-- min integer values
+SELECT '\x8000'::bytea::int2 = -0x8000;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x80000000'::bytea::int4 = -0x80000000;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x8000000000000000'::bytea::int8 = -0x8000000000000000;
+ ?column? 
+----------
+ t
+(1 row)
+
diff --git a/src/test/regress/sql/strings.sql b/src/test/regress/sql/strings.sql
index 8e0f3a0e75f..16cceb5c746 100644
--- a/src/test/regress/sql/strings.sql
+++ b/src/test/regress/sql/strings.sql
@@ -848,3 +848,37 @@ SELECT unistr('wrong: \udb99\u0061');
 SELECT unistr('wrong: \U0000db99\U00000061');
 SELECT unistr('wrong: \U002FFFFF');
 SELECT unistr('wrong: \xyz');
+
+--
+-- Test coercions between bytea and integer types
+--
+SET bytea_output TO hex;
+
+SELECT 0x1234::int2::bytea;
+SELECT 0x12345678::int4::bytea;
+SELECT 0x1122334455667788::int8::bytea;
+
+SELECT ''::bytea::int2 = 0;
+SELECT '\x12'::bytea::int2 = 0x12;
+SELECT '\x1234'::bytea::int2 = 0x1234;
+SELECT '\x123456'::bytea::int2; -- error
+
+SELECT ''::bytea::int4 = 0;
+SELECT '\x12'::bytea::int4 = 0x12;
+SELECT '\x12345678'::bytea::int4 = 0x12345678;
+SELECT '\x123456789A'::bytea::int4; -- error
+
+SELECT ''::bytea::int8 = 0;
+SELECT '\x12'::bytea::int8 = 0x12;
+SELECT '\x1122334455667788'::bytea::int8 = 0x1122334455667788;
+SELECT '\x112233445566778899'::bytea::int8; -- error
+
+-- max integer values
+SELECT '\x7FFF'::bytea::int2 = 0x7FFF;
+SELECT '\x7FFFFFFF'::bytea::int4 = 0x7FFFFFFF;
+SELECT '\x7FFFFFFFFFFFFFFF'::bytea::int8 = 0x7FFFFFFFFFFFFFFF;
+
+-- min integer values
+SELECT '\x8000'::bytea::int2 = -0x8000;
+SELECT '\x80000000'::bytea::int4 = -0x80000000;
+SELECT '\x8000000000000000'::bytea::int8 = -0x8000000000000000;
-- 
2.47.1

From 0c8e3055c0ff10abd10db5d460424a9c7cebb625 Mon Sep 17 00:00:00 2001
From: Aleksander Alekseev <aleksander@timescale.com>
Date: Mon, 26 Aug 2024 12:09:59 +0300
Subject: [PATCH v8] Allow casting between bytea and integer types.

For instance:

SELECT '\x12345678'::bytea::integer;
SELECT 0x12345678::bytea;

This works with int2's, int4's and int8's.

Author: Aleksander Alekseev
Reviewed-by: Peter Eisentraut, Michael Paquier, Dean Rasheed
Discussion: https://postgr.es/m/CAJ7c6TPtOp6%2BkFX5QX3fH1SVr7v65uHr-7yEJ%3DGMGQi5uhGtcA%40mail.gmail.com

BUMP CATVERSION
---
 doc/src/sgml/func.sgml                   |  15 +++
 src/backend/utils/adt/varlena.c          |  84 ++++++++++++++++
 src/include/catalog/pg_cast.dat          |  14 +++
 src/include/catalog/pg_proc.dat          |  18 ++++
 src/test/regress/expected/opr_sanity.out |   3 +
 src/test/regress/expected/strings.out    | 120 +++++++++++++++++++++++
 src/test/regress/sql/strings.sql         |  34 +++++++
 7 files changed, 288 insertions(+)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 47370e581ae..d95fa83406b 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -4997,6 +4997,21 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
    </variablelist>
   </para>
 
+   <para>
+    In addition, it is possible to cast integral values to and from type
+    <type>bytea</type>.
+<programlisting>
+''::bytea::int4                <lineannotation>0</lineannotation>
+0x1234::int2::bytea            <lineannotation>\x1234</lineannotation>
+'\x12'::bytea::int4            <lineannotation>0x12</lineannotation>
+'\x8000'::bytea::int2          <lineannotation>-32768</lineannotation>
+'\x123456'::bytea::int2        <lineannotation>ERROR</lineannotation>
+</programlisting>
+    Note that casting <type>bytea</type> to an integer type that is too small
+    to represent given value produces an error. Casting integer types to
+    <type>bytea</type> never fails.
+   </para>
+
   <para>
    See also the aggregate function <function>string_agg</function> in
    <xref linkend="functions-aggregate"/> and the large object functions
diff --git a/src/backend/utils/adt/varlena.c b/src/backend/utils/adt/varlena.c
index 34796f2e27c..eceecb25124 100644
--- a/src/backend/utils/adt/varlena.c
+++ b/src/backend/utils/adt/varlena.c
@@ -3985,6 +3985,90 @@ bytea_sortsupport(PG_FUNCTION_ARGS)
 	PG_RETURN_VOID();
 }
 
+/* Common code for bytea_int2, bytea_int4 and bytea_int8 */
+static int64
+bytea_integer(const bytea *v, int len)
+{
+	int			offset = 0;
+	int64		result = 0;
+
+	while (len--)
+	{
+		result = result << 8;
+		result |= ((unsigned char *) VARDATA_ANY(v))[offset];
+		offset++;
+	}
+
+	return result;
+}
+
+/* Cast bytea -> int2 */
+Datum
+bytea_int2(PG_FUNCTION_ARGS)
+{
+	bytea	   *v = PG_GETARG_BYTEA_PP(0);
+	int			len = VARSIZE_ANY_EXHDR(v);
+
+	if (len > sizeof(int16))
+		ereport(ERROR,
+				(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
+				 errmsg("smallint out of range")));
+
+	PG_RETURN_INT16((int16) bytea_integer(v, len));
+}
+
+/* Cast bytea -> int4 */
+Datum
+bytea_int4(PG_FUNCTION_ARGS)
+{
+	bytea	   *v = PG_GETARG_BYTEA_PP(0);
+	int			len = VARSIZE_ANY_EXHDR(v);
+
+	if (len > sizeof(int32))
+		ereport(ERROR,
+				(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
+				 errmsg("integer out of range")));
+
+	PG_RETURN_INT32((int32) bytea_integer(v, len));
+}
+
+/* Cast bytea -> int8 */
+Datum
+bytea_int8(PG_FUNCTION_ARGS)
+{
+	bytea	   *v = PG_GETARG_BYTEA_PP(0);
+	int			len = VARSIZE_ANY_EXHDR(v);
+
+	if (len > sizeof(int64))
+		ereport(ERROR,
+				(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
+				 errmsg("bigint out of range")));
+
+
+	PG_RETURN_INT64(bytea_integer(v, len));
+}
+
+/* Cast int2 -> bytea; currently just a wrapper for int2send() */
+Datum
+int2_bytea(PG_FUNCTION_ARGS)
+{
+	return int2send(fcinfo);
+}
+
+/* Cast int4 -> bytea; currently just a wrapper for int4send() */
+Datum
+int4_bytea(PG_FUNCTION_ARGS)
+{
+	return int4send(fcinfo);
+}
+
+/* Cast int8 -> bytea; currently just a wrapper for int8send() */
+Datum
+int8_bytea(PG_FUNCTION_ARGS)
+{
+	return int8send(fcinfo);
+}
+
 /*
  * appendStringInfoText
  *
diff --git a/src/include/catalog/pg_cast.dat b/src/include/catalog/pg_cast.dat
index a26ba34e869..ab46be606f0 100644
--- a/src/include/catalog/pg_cast.dat
+++ b/src/include/catalog/pg_cast.dat
@@ -320,6 +320,20 @@
 { castsource => 'varchar', casttarget => 'name', castfunc => 'name(varchar)',
   castcontext => 'i', castmethod => 'f' },
 
+# Allow explicit coercions between bytea and integer types
+{ castsource => 'int2', casttarget => 'bytea', castfunc => 'bytea(int2)',
+  castcontext => 'e', castmethod => 'f' },
+{ castsource => 'int4', casttarget => 'bytea', castfunc => 'bytea(int4)',
+  castcontext => 'e', castmethod => 'f' },
+{ castsource => 'int8', casttarget => 'bytea', castfunc => 'bytea(int8)',
+  castcontext => 'e', castmethod => 'f' },
+{ castsource => 'bytea', casttarget => 'int2', castfunc => 'int2(bytea)',
+  castcontext => 'e', castmethod => 'f' },
+{ castsource => 'bytea', casttarget => 'int4', castfunc => 'int4(bytea)',
+  castcontext => 'e', castmethod => 'f' },
+{ castsource => 'bytea', casttarget => 'int8', castfunc => 'int8(bytea)',
+  castcontext => 'e', castmethod => 'f' },
+
 # Allow explicit coercions between int4 and "char"
 { castsource => 'char', casttarget => 'int4', castfunc => 'int4(char)',
   castcontext => 'e', castmethod => 'f' },
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 18560755d26..ce3b979d676 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -1164,6 +1164,24 @@
 { oid => '409', descr => 'convert char(n) to name',
   proname => 'name', proleakproof => 't', prorettype => 'name',
   proargtypes => 'bpchar', prosrc => 'bpchar_name' },
+{ oid => '8577', descr => 'convert int2 to bytea',
+  proname => 'bytea', proleakproof => 't', prorettype => 'bytea',
+  proargtypes => 'int2', prosrc => 'int2_bytea' },
+{ oid => '8578', descr => 'convert int4 to bytea',
+  proname => 'bytea', proleakproof => 't', prorettype => 'bytea',
+  proargtypes => 'int4', prosrc => 'int4_bytea' },
+{ oid => '8579', descr => 'convert int8 to bytea',
+  proname => 'bytea', proleakproof => 't', prorettype => 'bytea',
+  proargtypes => 'int8', prosrc => 'int8_bytea' },
+{ oid => '8580', descr => 'convert bytea to int2',
+  proname => 'int2', proleakproof => 'f', prorettype => 'int2',
+  proargtypes => 'bytea', prosrc => 'bytea_int2' },
+{ oid => '8581', descr => 'convert bytea to int4',
+  proname => 'int4', proleakproof => 'f', prorettype => 'int4',
+  proargtypes => 'bytea', prosrc => 'bytea_int4' },
+{ oid => '8582', descr => 'convert bytea to int8',
+  proname => 'int8', proleakproof => 'f', prorettype => 'int8',
+  proargtypes => 'bytea', prosrc => 'bytea_int8' },
 
 { oid => '449', descr => 'hash',
   proname => 'hashint2', prorettype => 'int4', proargtypes => 'int2',
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..69f1e66aa98 100644
--- a/src/test/regress/expected/strings.out
+++ b/src/test/regress/expected/strings.out
@@ -2690,3 +2690,123 @@ ERROR:  invalid Unicode code point: 2FFFFF
 SELECT unistr('wrong: \xyz');
 ERROR:  invalid Unicode escape
 HINT:  Unicode escapes must be \XXXX, \+XXXXXX, \uXXXX, or \UXXXXXXXX.
+--
+-- Test coercions between bytea and integer types
+--
+SET bytea_output TO hex;
+SELECT 0x1234::int2::bytea;
+ bytea  
+--------
+ \x1234
+(1 row)
+
+SELECT 0x12345678::int4::bytea;
+   bytea    
+------------
+ \x12345678
+(1 row)
+
+SELECT 0x1122334455667788::int8::bytea;
+       bytea        
+--------------------
+ \x1122334455667788
+(1 row)
+
+SELECT ''::bytea::int2 = 0;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x12'::bytea::int2 = 0x12;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x1234'::bytea::int2 = 0x1234;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x123456'::bytea::int2; -- error
+ERROR:  smallint out of range
+SELECT ''::bytea::int4 = 0;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x12'::bytea::int4 = 0x12;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x12345678'::bytea::int4 = 0x12345678;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x123456789A'::bytea::int4; -- error
+ERROR:  integer out of range
+SELECT ''::bytea::int8 = 0;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x12'::bytea::int8 = 0x12;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x1122334455667788'::bytea::int8 = 0x1122334455667788;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x112233445566778899'::bytea::int8; -- error
+ERROR:  bigint out of range
+-- max integer values
+SELECT '\x7FFF'::bytea::int2 = 0x7FFF;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x7FFFFFFF'::bytea::int4 = 0x7FFFFFFF;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x7FFFFFFFFFFFFFFF'::bytea::int8 = 0x7FFFFFFFFFFFFFFF;
+ ?column? 
+----------
+ t
+(1 row)
+
+-- min integer values
+SELECT '\x8000'::bytea::int2 = -0x8000;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x80000000'::bytea::int4 = -0x80000000;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x8000000000000000'::bytea::int8 = -0x8000000000000000;
+ ?column? 
+----------
+ t
+(1 row)
+
diff --git a/src/test/regress/sql/strings.sql b/src/test/regress/sql/strings.sql
index 8e0f3a0e75f..16cceb5c746 100644
--- a/src/test/regress/sql/strings.sql
+++ b/src/test/regress/sql/strings.sql
@@ -848,3 +848,37 @@ SELECT unistr('wrong: \udb99\u0061');
 SELECT unistr('wrong: \U0000db99\U00000061');
 SELECT unistr('wrong: \U002FFFFF');
 SELECT unistr('wrong: \xyz');
+
+--
+-- Test coercions between bytea and integer types
+--
+SET bytea_output TO hex;
+
+SELECT 0x1234::int2::bytea;
+SELECT 0x12345678::int4::bytea;
+SELECT 0x1122334455667788::int8::bytea;
+
+SELECT ''::bytea::int2 = 0;
+SELECT '\x12'::bytea::int2 = 0x12;
+SELECT '\x1234'::bytea::int2 = 0x1234;
+SELECT '\x123456'::bytea::int2; -- error
+
+SELECT ''::bytea::int4 = 0;
+SELECT '\x12'::bytea::int4 = 0x12;
+SELECT '\x12345678'::bytea::int4 = 0x12345678;
+SELECT '\x123456789A'::bytea::int4; -- error
+
+SELECT ''::bytea::int8 = 0;
+SELECT '\x12'::bytea::int8 = 0x12;
+SELECT '\x1122334455667788'::bytea::int8 = 0x1122334455667788;
+SELECT '\x112233445566778899'::bytea::int8; -- error
+
+-- max integer values
+SELECT '\x7FFF'::bytea::int2 = 0x7FFF;
+SELECT '\x7FFFFFFF'::bytea::int4 = 0x7FFFFFFF;
+SELECT '\x7FFFFFFFFFFFFFFF'::bytea::int8 = 0x7FFFFFFFFFFFFFFF;
+
+-- min integer values
+SELECT '\x8000'::bytea::int2 = -0x8000;
+SELECT '\x80000000'::bytea::int4 = -0x80000000;
+SELECT '\x8000000000000000'::bytea::int8 = -0x8000000000000000;
-- 
2.48.1

From 731dadc5f87d4e62d4f064c7d0ce91bdbafd1888 Mon Sep 17 00:00:00 2001
From: Aleksander Alekseev <aleksander@timescale.com>
Date: Mon, 26 Aug 2024 12:09:59 +0300
Subject: [PATCH v9] Allow casting between bytea and integer types.

For instance:

SELECT '\x12345678'::bytea::integer;
SELECT 0x12345678::bytea;

This works with int2's, int4's and int8's.

Author: Aleksander Alekseev
Reviewed-by: Peter Eisentraut, Michael Paquier, Dean Rasheed
Discussion: https://postgr.es/m/CAJ7c6TPtOp6%2BkFX5QX3fH1SVr7v65uHr-7yEJ%3DGMGQi5uhGtcA%40mail.gmail.com

BUMP CATVERSION
---
 doc/src/sgml/func.sgml                   |  18 ++++
 src/backend/utils/adt/varlena.c          |  84 ++++++++++++++++
 src/include/catalog/pg_cast.dat          |  14 +++
 src/include/catalog/pg_proc.dat          |  18 ++++
 src/test/regress/expected/opr_sanity.out |   3 +
 src/test/regress/expected/strings.out    | 120 +++++++++++++++++++++++
 src/test/regress/sql/strings.sql         |  34 +++++++
 7 files changed, 291 insertions(+)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 5678e7621a5..0c1790a5e46 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -4997,6 +4997,24 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
    </variablelist>
   </para>
 
+   <para>
+    In addition, it is possible to cast integral values to and from type
+    <type>bytea</type>. Casting an integer to a <type>bytea</type> produces
+    2, 4, or 8 bytes, depending on the width of the integer type. The result
+    is the two's complement representation of the integer, with the most
+    significant byte first. Examples:
+<programlisting>
+''::bytea::int4                <lineannotation>0</lineannotation>
+0x1234::int2::bytea            <lineannotation>\x1234</lineannotation>
+'\x12'::bytea::int4            <lineannotation>0x12</lineannotation>
+'\x8000'::bytea::int2          <lineannotation>-32768</lineannotation>
+'\x123456'::bytea::int2        <lineannotation>ERROR</lineannotation>
+</programlisting>
+    Note that casting <type>bytea</type> to an integer type that is too small
+    to represent given value produces an error. Casting integer types to
+    <type>bytea</type> never fails.
+   </para>
+
   <para>
    See also the aggregate function <function>string_agg</function> in
    <xref linkend="functions-aggregate"/> and the large object functions
diff --git a/src/backend/utils/adt/varlena.c b/src/backend/utils/adt/varlena.c
index 34796f2e27c..eceecb25124 100644
--- a/src/backend/utils/adt/varlena.c
+++ b/src/backend/utils/adt/varlena.c
@@ -3985,6 +3985,90 @@ bytea_sortsupport(PG_FUNCTION_ARGS)
 	PG_RETURN_VOID();
 }
 
+/* Common code for bytea_int2, bytea_int4 and bytea_int8 */
+static int64
+bytea_integer(const bytea *v, int len)
+{
+	int			offset = 0;
+	int64		result = 0;
+
+	while (len--)
+	{
+		result = result << 8;
+		result |= ((unsigned char *) VARDATA_ANY(v))[offset];
+		offset++;
+	}
+
+	return result;
+}
+
+/* Cast bytea -> int2 */
+Datum
+bytea_int2(PG_FUNCTION_ARGS)
+{
+	bytea	   *v = PG_GETARG_BYTEA_PP(0);
+	int			len = VARSIZE_ANY_EXHDR(v);
+
+	if (len > sizeof(int16))
+		ereport(ERROR,
+				(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
+				 errmsg("smallint out of range")));
+
+	PG_RETURN_INT16((int16) bytea_integer(v, len));
+}
+
+/* Cast bytea -> int4 */
+Datum
+bytea_int4(PG_FUNCTION_ARGS)
+{
+	bytea	   *v = PG_GETARG_BYTEA_PP(0);
+	int			len = VARSIZE_ANY_EXHDR(v);
+
+	if (len > sizeof(int32))
+		ereport(ERROR,
+				(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
+				 errmsg("integer out of range")));
+
+	PG_RETURN_INT32((int32) bytea_integer(v, len));
+}
+
+/* Cast bytea -> int8 */
+Datum
+bytea_int8(PG_FUNCTION_ARGS)
+{
+	bytea	   *v = PG_GETARG_BYTEA_PP(0);
+	int			len = VARSIZE_ANY_EXHDR(v);
+
+	if (len > sizeof(int64))
+		ereport(ERROR,
+				(errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
+				 errmsg("bigint out of range")));
+
+
+	PG_RETURN_INT64(bytea_integer(v, len));
+}
+
+/* Cast int2 -> bytea; currently just a wrapper for int2send() */
+Datum
+int2_bytea(PG_FUNCTION_ARGS)
+{
+	return int2send(fcinfo);
+}
+
+/* Cast int4 -> bytea; currently just a wrapper for int4send() */
+Datum
+int4_bytea(PG_FUNCTION_ARGS)
+{
+	return int4send(fcinfo);
+}
+
+/* Cast int8 -> bytea; currently just a wrapper for int8send() */
+Datum
+int8_bytea(PG_FUNCTION_ARGS)
+{
+	return int8send(fcinfo);
+}
+
 /*
  * appendStringInfoText
  *
diff --git a/src/include/catalog/pg_cast.dat b/src/include/catalog/pg_cast.dat
index a26ba34e869..ab46be606f0 100644
--- a/src/include/catalog/pg_cast.dat
+++ b/src/include/catalog/pg_cast.dat
@@ -320,6 +320,20 @@
 { castsource => 'varchar', casttarget => 'name', castfunc => 'name(varchar)',
   castcontext => 'i', castmethod => 'f' },
 
+# Allow explicit coercions between bytea and integer types
+{ castsource => 'int2', casttarget => 'bytea', castfunc => 'bytea(int2)',
+  castcontext => 'e', castmethod => 'f' },
+{ castsource => 'int4', casttarget => 'bytea', castfunc => 'bytea(int4)',
+  castcontext => 'e', castmethod => 'f' },
+{ castsource => 'int8', casttarget => 'bytea', castfunc => 'bytea(int8)',
+  castcontext => 'e', castmethod => 'f' },
+{ castsource => 'bytea', casttarget => 'int2', castfunc => 'int2(bytea)',
+  castcontext => 'e', castmethod => 'f' },
+{ castsource => 'bytea', casttarget => 'int4', castfunc => 'int4(bytea)',
+  castcontext => 'e', castmethod => 'f' },
+{ castsource => 'bytea', casttarget => 'int8', castfunc => 'int8(bytea)',
+  castcontext => 'e', castmethod => 'f' },
+
 # Allow explicit coercions between int4 and "char"
 { castsource => 'char', casttarget => 'int4', castfunc => 'int4(char)',
   castcontext => 'e', castmethod => 'f' },
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index 18560755d26..ce3b979d676 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -1164,6 +1164,24 @@
 { oid => '409', descr => 'convert char(n) to name',
   proname => 'name', proleakproof => 't', prorettype => 'name',
   proargtypes => 'bpchar', prosrc => 'bpchar_name' },
+{ oid => '8577', descr => 'convert int2 to bytea',
+  proname => 'bytea', proleakproof => 't', prorettype => 'bytea',
+  proargtypes => 'int2', prosrc => 'int2_bytea' },
+{ oid => '8578', descr => 'convert int4 to bytea',
+  proname => 'bytea', proleakproof => 't', prorettype => 'bytea',
+  proargtypes => 'int4', prosrc => 'int4_bytea' },
+{ oid => '8579', descr => 'convert int8 to bytea',
+  proname => 'bytea', proleakproof => 't', prorettype => 'bytea',
+  proargtypes => 'int8', prosrc => 'int8_bytea' },
+{ oid => '8580', descr => 'convert bytea to int2',
+  proname => 'int2', proleakproof => 'f', prorettype => 'int2',
+  proargtypes => 'bytea', prosrc => 'bytea_int2' },
+{ oid => '8581', descr => 'convert bytea to int4',
+  proname => 'int4', proleakproof => 'f', prorettype => 'int4',
+  proargtypes => 'bytea', prosrc => 'bytea_int4' },
+{ oid => '8582', descr => 'convert bytea to int8',
+  proname => 'int8', proleakproof => 'f', prorettype => 'int8',
+  proargtypes => 'bytea', prosrc => 'bytea_int8' },
 
 { oid => '449', descr => 'hash',
   proname => 'hashint2', prorettype => 'int4', proargtypes => 'int2',
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..69f1e66aa98 100644
--- a/src/test/regress/expected/strings.out
+++ b/src/test/regress/expected/strings.out
@@ -2690,3 +2690,123 @@ ERROR:  invalid Unicode code point: 2FFFFF
 SELECT unistr('wrong: \xyz');
 ERROR:  invalid Unicode escape
 HINT:  Unicode escapes must be \XXXX, \+XXXXXX, \uXXXX, or \UXXXXXXXX.
+--
+-- Test coercions between bytea and integer types
+--
+SET bytea_output TO hex;
+SELECT 0x1234::int2::bytea;
+ bytea  
+--------
+ \x1234
+(1 row)
+
+SELECT 0x12345678::int4::bytea;
+   bytea    
+------------
+ \x12345678
+(1 row)
+
+SELECT 0x1122334455667788::int8::bytea;
+       bytea        
+--------------------
+ \x1122334455667788
+(1 row)
+
+SELECT ''::bytea::int2 = 0;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x12'::bytea::int2 = 0x12;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x1234'::bytea::int2 = 0x1234;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x123456'::bytea::int2; -- error
+ERROR:  smallint out of range
+SELECT ''::bytea::int4 = 0;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x12'::bytea::int4 = 0x12;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x12345678'::bytea::int4 = 0x12345678;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x123456789A'::bytea::int4; -- error
+ERROR:  integer out of range
+SELECT ''::bytea::int8 = 0;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x12'::bytea::int8 = 0x12;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x1122334455667788'::bytea::int8 = 0x1122334455667788;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x112233445566778899'::bytea::int8; -- error
+ERROR:  bigint out of range
+-- max integer values
+SELECT '\x7FFF'::bytea::int2 = 0x7FFF;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x7FFFFFFF'::bytea::int4 = 0x7FFFFFFF;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x7FFFFFFFFFFFFFFF'::bytea::int8 = 0x7FFFFFFFFFFFFFFF;
+ ?column? 
+----------
+ t
+(1 row)
+
+-- min integer values
+SELECT '\x8000'::bytea::int2 = -0x8000;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x80000000'::bytea::int4 = -0x80000000;
+ ?column? 
+----------
+ t
+(1 row)
+
+SELECT '\x8000000000000000'::bytea::int8 = -0x8000000000000000;
+ ?column? 
+----------
+ t
+(1 row)
+
diff --git a/src/test/regress/sql/strings.sql b/src/test/regress/sql/strings.sql
index 8e0f3a0e75f..16cceb5c746 100644
--- a/src/test/regress/sql/strings.sql
+++ b/src/test/regress/sql/strings.sql
@@ -848,3 +848,37 @@ SELECT unistr('wrong: \udb99\u0061');
 SELECT unistr('wrong: \U0000db99\U00000061');
 SELECT unistr('wrong: \U002FFFFF');
 SELECT unistr('wrong: \xyz');
+
+--
+-- Test coercions between bytea and integer types
+--
+SET bytea_output TO hex;
+
+SELECT 0x1234::int2::bytea;
+SELECT 0x12345678::int4::bytea;
+SELECT 0x1122334455667788::int8::bytea;
+
+SELECT ''::bytea::int2 = 0;
+SELECT '\x12'::bytea::int2 = 0x12;
+SELECT '\x1234'::bytea::int2 = 0x1234;
+SELECT '\x123456'::bytea::int2; -- error
+
+SELECT ''::bytea::int4 = 0;
+SELECT '\x12'::bytea::int4 = 0x12;
+SELECT '\x12345678'::bytea::int4 = 0x12345678;
+SELECT '\x123456789A'::bytea::int4; -- error
+
+SELECT ''::bytea::int8 = 0;
+SELECT '\x12'::bytea::int8 = 0x12;
+SELECT '\x1122334455667788'::bytea::int8 = 0x1122334455667788;
+SELECT '\x112233445566778899'::bytea::int8; -- error
+
+-- max integer values
+SELECT '\x7FFF'::bytea::int2 = 0x7FFF;
+SELECT '\x7FFFFFFF'::bytea::int4 = 0x7FFFFFFF;
+SELECT '\x7FFFFFFFFFFFFFFF'::bytea::int8 = 0x7FFFFFFFFFFFFFFF;
+
+-- min integer values
+SELECT '\x8000'::bytea::int2 = -0x8000;
+SELECT '\x80000000'::bytea::int4 = -0x80000000;
+SELECT '\x8000000000000000'::bytea::int8 = -0x8000000000000000;
-- 
2.48.1

From 5c0572a0930067f7244606384542d670cd1e4aa6 Mon Sep 17 00:00:00 2001
From: Dean Rasheed <dean.a.rasheed@gmail.com>
Date: Sat, 1 Mar 2025 10:49:24 +0000
Subject: [PATCH v10] 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
---
 doc/src/sgml/func.sgml                   |  17 ++++
 src/backend/utils/adt/varlena.c          |  90 ++++++++++++++++++++
 src/include/catalog/pg_cast.dat          |  14 ++++
 src/include/catalog/pg_proc.dat          |  19 +++++
 src/test/regress/expected/opr_sanity.out |   3 +
 src/test/regress/expected/strings.out    | 102 +++++++++++++++++++++++
 src/test/regress/sql/strings.sql         |  29 +++++++
 7 files changed, 274 insertions(+)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 0e6c534965..6b15f167d0 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -5035,6 +5035,23 @@ SELECT format('Testing %3$s, %2$s, %s', 'one', 'two', 'three');
    </variablelist>
   </para>
 
+  <para>
+   In addition, it is possible to cast integral values to and from type
+   <type>bytea</type>. Casting an integer to <type>bytea</type> produces
+   2, 4, or 8 bytes, depending on the width of the integer type. The result
+   is the two's complement representation of the integer, with the most
+   significant byte first. Some examples:
+<programlisting>
+1234::smallint::bytea          <lineannotation>\x04d2</lineannotation>
+cast(1234 as bytea)            <lineannotation>\x000004d2</lineannotation>
+cast(-1234 as bytea)           <lineannotation>\xfffffb2e</lineannotation>
+'\x8000'::bytea::smallint      <lineannotation>-32768</lineannotation>
+'\x8000'::bytea::integer       <lineannotation>32768</lineannotation>
+</programlisting>
+   Casting a <type>bytea</type> to an integer will raise an error if the
+   length of the <type>bytea</type> exceeds the width of the integer type.
+  </para>
+
   <para>
    See also the aggregate function <function>string_agg</function> in
    <xref linkend="functions-aggregate"/> and the large object functions
diff --git a/src/backend/utils/adt/varlena.c b/src/backend/utils/adt/varlena.c
index e455657170..d22648a7e4 100644
--- a/src/backend/utils/adt/varlena.c
+++ b/src/backend/utils/adt/varlena.c
@@ -4057,6 +4057,96 @@ bytea_sortsupport(PG_FUNCTION_ARGS)
 	PG_RETURN_VOID();
 }
 
+/* Cast bytea -> int2 */
+Datum
+bytea_int2(PG_FUNCTION_ARGS)
+{
+	bytea	   *v = PG_GETARG_BYTEA_PP(0);
+	int			len = VARSIZE_ANY_EXHDR(v);
+	uint16		result;
+
+	if (len > sizeof(result))
+		ereport(ERROR,
+				errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
+				errmsg("smallint out of range"));
+
+	result = 0;
+	for (int i = 0; i < len; i++)
+	{
+		result <<= BITS_PER_BYTE;
+		result |= ((unsigned char *) VARDATA_ANY(v))[i];
+	}
+
+	PG_RETURN_INT16(result);
+}
+
+/* Cast bytea -> int4 */
+Datum
+bytea_int4(PG_FUNCTION_ARGS)
+{
+	bytea	   *v = PG_GETARG_BYTEA_PP(0);
+	int			len = VARSIZE_ANY_EXHDR(v);
+	uint32		result;
+
+	if (len > sizeof(result))
+		ereport(ERROR,
+				errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
+				errmsg("integer out of range"));
+
+	result = 0;
+	for (int i = 0; i < len; i++)
+	{
+		result <<= BITS_PER_BYTE;
+		result |= ((unsigned char *) VARDATA_ANY(v))[i];
+	}
+
+	PG_RETURN_INT32(result);
+}
+
+/* Cast bytea -> int8 */
+Datum
+bytea_int8(PG_FUNCTION_ARGS)
+{
+	bytea	   *v = PG_GETARG_BYTEA_PP(0);
+	int			len = VARSIZE_ANY_EXHDR(v);
+	uint64		result;
+
+	if (len > sizeof(result))
+		ereport(ERROR,
+				errcode(ERRCODE_NUMERIC_VALUE_OUT_OF_RANGE),
+				errmsg("bigint out of range"));
+
+	result = 0;
+	for (int i = 0; i < len; i++)
+	{
+		result <<= BITS_PER_BYTE;
+		result |= ((unsigned char *) VARDATA_ANY(v))[i];
+	}
+
+	PG_RETURN_INT64(result);
+}
+
+/* Cast int2 -> bytea; can just use int2send() */
+Datum
+int2_bytea(PG_FUNCTION_ARGS)
+{
+	return int2send(fcinfo);
+}
+
+/* Cast int4 -> bytea; can just use int4send() */
+Datum
+int4_bytea(PG_FUNCTION_ARGS)
+{
+	return int4send(fcinfo);
+}
+
+/* Cast int8 -> bytea; can just use int8send() */
+Datum
+int8_bytea(PG_FUNCTION_ARGS)
+{
+	return int8send(fcinfo);
+}
+
 /*
  * appendStringInfoText
  *
diff --git a/src/include/catalog/pg_cast.dat b/src/include/catalog/pg_cast.dat
index a26ba34e86..ab46be606f 100644
--- a/src/include/catalog/pg_cast.dat
+++ b/src/include/catalog/pg_cast.dat
@@ -320,6 +320,20 @@
 { castsource => 'varchar', casttarget => 'name', castfunc => 'name(varchar)',
   castcontext => 'i', castmethod => 'f' },
 
+# Allow explicit coercions between bytea and integer types
+{ castsource => 'int2', casttarget => 'bytea', castfunc => 'bytea(int2)',
+  castcontext => 'e', castmethod => 'f' },
+{ castsource => 'int4', casttarget => 'bytea', castfunc => 'bytea(int4)',
+  castcontext => 'e', castmethod => 'f' },
+{ castsource => 'int8', casttarget => 'bytea', castfunc => 'bytea(int8)',
+  castcontext => 'e', castmethod => 'f' },
+{ castsource => 'bytea', casttarget => 'int2', castfunc => 'int2(bytea)',
+  castcontext => 'e', castmethod => 'f' },
+{ castsource => 'bytea', casttarget => 'int4', castfunc => 'int4(bytea)',
+  castcontext => 'e', castmethod => 'f' },
+{ castsource => 'bytea', casttarget => 'int8', castfunc => 'int8(bytea)',
+  castcontext => 'e', castmethod => 'f' },
+
 # Allow explicit coercions between int4 and "char"
 { castsource => 'char', casttarget => 'int4', castfunc => 'int4(char)',
   castcontext => 'e', castmethod => 'f' },
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index cd9422d0ba..e25dd0bb0a 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -1165,6 +1165,25 @@
   proname => 'name', proleakproof => 't', prorettype => 'name',
   proargtypes => 'bpchar', prosrc => 'bpchar_name' },
 
+{ oid => '8577', descr => 'convert int2 to bytea',
+  proname => 'bytea', proleakproof => 't', prorettype => 'bytea',
+  proargtypes => 'int2', prosrc => 'int2_bytea' },
+{ oid => '8578', descr => 'convert int4 to bytea',
+  proname => 'bytea', proleakproof => 't', prorettype => 'bytea',
+  proargtypes => 'int4', prosrc => 'int4_bytea' },
+{ oid => '8579', descr => 'convert int8 to bytea',
+  proname => 'bytea', proleakproof => 't', prorettype => 'bytea',
+  proargtypes => 'int8', prosrc => 'int8_bytea' },
+{ oid => '8580', descr => 'convert bytea to int2',
+  proname => 'int2', prorettype => 'int2',
+  proargtypes => 'bytea', prosrc => 'bytea_int2' },
+{ oid => '8581', descr => 'convert bytea to int4',
+  proname => 'int4', prorettype => 'int4',
+  proargtypes => 'bytea', prosrc => 'bytea_int4' },
+{ oid => '8582', descr => 'convert bytea to int8',
+  proname => 'int8', prorettype => 'int8',
+  proargtypes => 'bytea', prosrc => 'bytea_int8' },
+
 { oid => '449', descr => 'hash',
   proname => 'hashint2', prorettype => 'int4', proargtypes => 'int2',
   prosrc => 'hashint2' },
diff --git a/src/test/regress/expected/opr_sanity.out b/src/test/regress/expected/opr_sanity.out
index b673642ad1..20bf9ea9cd 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 b65bb2d536..f8cba9f5b2 100644
--- a/src/test/regress/expected/strings.out
+++ b/src/test/regress/expected/strings.out
@@ -2358,6 +2358,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
 --
diff --git a/src/test/regress/sql/strings.sql b/src/test/regress/sql/strings.sql
index 8e0f3a0e75..4deb0683d5 100644
--- a/src/test/regress/sql/strings.sql
+++ b/src/test/regress/sql/strings.sql
@@ -751,6 +751,35 @@ SELECT get_byte('\x1234567890abcdef00'::bytea, 99);  -- error
 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
 --
-- 
2.43.0