[PATCH] Add reverse(bytea)

From 223ce1438acf1b415677e509ef9ea02b96dcb1fc Mon Sep 17 00:00:00 2001
From: Aleksander Alekseev <aleksander@timescale.com>
Date: Mon, 10 Mar 2025 09:35:28 +0300
Subject: [PATCH v1] Add reverse(bytea) function

This allows converting between big-endian and little-endian binary strings,
which works nicely with previous commits 760162fedb4f and 6da469badaff.

Aleksander Alekseev, reviewed by TODO FIXME
Discussion: TODO FIXME

!!! BUMP CATVERSION !!!
---
 doc/src/sgml/func.sgml                | 17 +++++++++++++++++
 src/backend/utils/adt/varlena.c       | 24 ++++++++++++++++++++++++
 src/include/catalog/pg_proc.dat       |  3 +++
 src/test/regress/expected/strings.out | 21 +++++++++++++++++++++
 src/test/regress/sql/strings.sql      |  8 ++++++++
 5 files changed, 73 insertions(+)

diff --git a/doc/src/sgml/func.sgml b/doc/src/sgml/func.sgml
index 51dd8ad6571..2dde6e22799 100644
--- a/doc/src/sgml/func.sgml
+++ b/doc/src/sgml/func.sgml
@@ -4660,6 +4660,23 @@ 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>reverse</primary>
+        </indexterm>
+        <function>reverse</function> ( <type>bytea</type> )
+        <returnvalue>bytea</returnvalue>
+       </para>
+       <para>
+        Reverses the order of the bytes in the binary string.
+       </para>
+       <para>
+        <literal>reverse('\xAABBCC' :: bytea)</literal>
+        <returnvalue>\xCCBBAA</returnvalue>
+       </para></entry>
+      </row>
+
       <row>
        <entry role="func_table_entry"><para role="func_signature">
         <indexterm>
diff --git a/src/backend/utils/adt/varlena.c b/src/backend/utils/adt/varlena.c
index cdf185ea00b..4597e80e37c 100644
--- a/src/backend/utils/adt/varlena.c
+++ b/src/backend/utils/adt/varlena.c
@@ -4153,6 +4153,30 @@ int8_bytea(PG_FUNCTION_ARGS)
 	return int8send(fcinfo);
 }
 
+/*
+ * Return reversed bytea
+ */
+Datum
+bytea_reverse(PG_FUNCTION_ARGS)
+{
+	bytea	   *v = PG_GETARG_BYTEA_PP(0);
+	int			len = VARSIZE_ANY_EXHDR(v);
+	const char *p = VARDATA_ANY(v);
+	const char *endp = p + len;
+	bytea	   *result;
+	char	   *dst;
+
+	result = palloc(len + VARHDRSZ);
+	dst = (char *) VARDATA(result) + len;
+	SET_VARSIZE(result, len + VARHDRSZ);
+
+	while (p < endp)
+		*(--dst) = *p++;
+
+	PG_RETURN_BYTEA_P(result);
+}
+
+
 /*
  * appendStringInfoText
  *
diff --git a/src/include/catalog/pg_proc.dat b/src/include/catalog/pg_proc.dat
index cede992b6e2..d460c55776f 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -1183,6 +1183,9 @@
 { oid => '8582', descr => 'convert bytea to int8',
   proname => 'int8', prorettype => 'int8',
   proargtypes => 'bytea', prosrc => 'bytea_int8' },
+{ oid => '8583', descr => 'reverse bytea',
+  proname => 'reverse', prorettype => 'bytea', proargtypes => 'bytea',
+  prosrc => 'bytea_reverse' },
 
 { oid => '449', descr => 'hash',
   proname => 'hashint2', prorettype => 'int4', proargtypes => 'int2',
diff --git a/src/test/regress/expected/strings.out b/src/test/regress/expected/strings.out
index f8cba9f5b24..c9f761f8332 100644
--- a/src/test/regress/expected/strings.out
+++ b/src/test/regress/expected/strings.out
@@ -2460,6 +2460,27 @@ SELECT '\x8000000000000000'::bytea::int8 AS "-9223372036854775808",
  -9223372036854775808 | 9223372036854775807
 (1 row)
 
+--
+-- reversing a bytea
+--
+SELECT reverse(''::bytea);
+ reverse 
+---------
+ \x
+(1 row)
+
+SELECT reverse('\xAA'::bytea);
+ reverse 
+---------
+ \xaa
+(1 row)
+
+SELECT reverse('\xAABBCC'::bytea);
+ reverse  
+----------
+ \xccbbaa
+(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 4deb0683d57..5f1ca98edad 100644
--- a/src/test/regress/sql/strings.sql
+++ b/src/test/regress/sql/strings.sql
@@ -780,6 +780,14 @@ SELECT '\x80000000'::bytea::int4 AS "-2147483648", '\x7FFFFFFF'::bytea::int4 AS
 SELECT '\x8000000000000000'::bytea::int8 AS "-9223372036854775808",
        '\x7FFFFFFFFFFFFFFF'::bytea::int8 AS "9223372036854775807";
 
+
+--
+-- reversing a bytea
+--
+SELECT reverse(''::bytea);
+SELECT reverse('\xAA'::bytea);
+SELECT reverse('\xAABBCC'::bytea);
+
 --
 -- test behavior of escape_string_warning and standard_conforming_strings options
 --
-- 
2.48.1