Re: Add uuid_to_base32hex() and base32hex_to_uuid() built-in functions - Mailing list pgsql-hackers

Jelte Fennema-Nio <postgres@jeltef.nl> writes:

> On Tue, 28 Oct 2025 at 17:41, Dagfinn Ilmari Mannsåker
> <ilmari@ilmari.org> wrote:
>> Here's a patch for that.
>
> Looks good to me. Maybe add a test where not every byte is the same though.

Good point. I've replaced them with two randomly generated ones.

>> I'm not 100% confident about the error code
>> for invalid length, but that was the closest one I could find in
>> errcodes.txt.
>
> The errorcode you chose seems acceptable to me, but I think a slightly
> more fitting option would be ERRCODE_INVALID_BINARY_REPRESENTATION.
> Error codes in postgres are pretty arbitrary though, so either seems
> fine to me.

That does seem like a better fit. It's used mainly in recv functions,
which this basically is (but user-callable).

Updated patch attaced.

- ilmari

From 7bb7bba8d1be96783ac45df3178fb613e84c39fa Mon Sep 17 00:00:00 2001
From: =?UTF-8?q?Dagfinn=20Ilmari=20Manns=C3=A5ker?= <ilmari@ilmari.org>
Date: Tue, 28 Oct 2025 16:33:17 +0000
Subject: [PATCH v2] Allow explicit casting between bytea and UUID

This enables using encode() and decode() to convert UUIDs to and from
alternative formats, such as base64.
---
 src/backend/utils/adt/bytea.c      | 26 ++++++++++++++++++++++++++
 src/include/catalog/pg_cast.dat    |  6 ++++++
 src/include/catalog/pg_proc.dat    |  7 +++++++
 src/test/regress/expected/uuid.out | 15 +++++++++++++++
 src/test/regress/sql/uuid.sql      |  4 ++++
 5 files changed, 58 insertions(+)

diff --git a/src/backend/utils/adt/bytea.c b/src/backend/utils/adt/bytea.c
index 6e7b914c563..ff9e46f3015 100644
--- a/src/backend/utils/adt/bytea.c
+++ b/src/backend/utils/adt/bytea.c
@@ -26,6 +26,7 @@
 #include "utils/fmgrprotos.h"
 #include "utils/memutils.h"
 #include "utils/sortsupport.h"
+#include "utils/uuid.h"
 #include "utils/varlena.h"
 #include "varatt.h"
 
@@ -1112,3 +1113,28 @@ int8_bytea(PG_FUNCTION_ARGS)
 {
     return int8send(fcinfo);
 }
+
+/* Cast bytea -> uuid */
+Datum
+bytea_uuid(PG_FUNCTION_ARGS)
+{
+    bytea       *v = PG_GETARG_BYTEA_PP(0);
+    int            len = VARSIZE_ANY_EXHDR(v);
+    pg_uuid_t  *uuid;
+
+    if (len != UUID_LEN)
+        ereport(ERROR,
+                errcode(ERRCODE_INVALID_BINARY_REPRESENTATION),
+                errmsg("invalid uuid length"));
+
+    uuid = (pg_uuid_t *) palloc(UUID_LEN);
+    memcpy(uuid->data, VARDATA_ANY(v), UUID_LEN);
+    PG_RETURN_POINTER(uuid);
+}
+
+/* Cast uuid -> bytea; can just use uuid_send() */
+Datum
+uuid_bytea(PG_FUNCTION_ARGS)
+{
+    return uuid_send(fcinfo);
+}
diff --git a/src/include/catalog/pg_cast.dat b/src/include/catalog/pg_cast.dat
index fbfd669587f..913c55ef869 100644
--- a/src/include/catalog/pg_cast.dat
+++ b/src/include/catalog/pg_cast.dat
@@ -348,6 +348,12 @@
 { castsource => 'bytea', casttarget => 'int8', castfunc => 'int8(bytea)',
   castcontext => 'e', castmethod => 'f' },
 
+# Allow explicit coercions between bytea and uuid type
+{ castsource => 'bytea', casttarget => 'uuid', castfunc => 'uuid(bytea)',
+  castcontext => 'e', castmethod => 'f' },
+{ castsource => 'uuid', casttarget => 'bytea', castfunc => 'bytea(uuid)',
+  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 9121a382f76..110593eb399 100644
--- a/src/include/catalog/pg_proc.dat
+++ b/src/include/catalog/pg_proc.dat
@@ -1199,6 +1199,13 @@
   proname => 'int8', prorettype => 'int8', proargtypes => 'bytea',
   prosrc => 'bytea_int8' },
 
+{ oid => '9880', descr => 'convert uuid to bytea',
+  proname => 'bytea', prorettype => 'bytea', proargtypes => 'uuid',
+  prosrc => 'uuid_bytea' },
+{ oid => '9881', descr => 'convert bytea to uuid',
+  proname => 'uuid', prorettype => 'uuid', proargtypes => 'bytea',
+  prosrc => 'bytea_uuid' },
+
 { oid => '449', descr => 'hash',
   proname => 'hashint2', prorettype => 'int4', proargtypes => 'int2',
   prosrc => 'hashint2' },
diff --git a/src/test/regress/expected/uuid.out b/src/test/regress/expected/uuid.out
index 95392003b86..4b635336606 100644
--- a/src/test/regress/expected/uuid.out
+++ b/src/test/regress/expected/uuid.out
@@ -305,5 +305,20 @@ SELECT uuid_extract_timestamp('11111111-1111-1111-1111-111111111111');  -- null
  
 (1 row)
 
+-- casts
+SELECT '5b35380a-7143-4912-9b55-f322699c6770'::uuid::bytea;
+               bytea                
+------------------------------------
+ \x5b35380a714349129b55f322699c6770
+(1 row)
+
+SELECT '\x019a2f859ced7225b99d9c55044a2563'::bytea::uuid;
+                 uuid                 
+--------------------------------------
+ 019a2f85-9ced-7225-b99d-9c55044a2563
+(1 row)
+
+SELECT '\x1234567890abcdef'::bytea::uuid; -- error
+ERROR:  invalid uuid length
 -- clean up
 DROP TABLE guid1, guid2, guid3 CASCADE;
diff --git a/src/test/regress/sql/uuid.sql b/src/test/regress/sql/uuid.sql
index 465153a0341..63520d0b640 100644
--- a/src/test/regress/sql/uuid.sql
+++ b/src/test/regress/sql/uuid.sql
@@ -146,6 +146,10 @@ SELECT uuid_extract_timestamp('017F22E2-79B0-7CC3-98C4-DC0C0C07398F') = 'Tuesday
 SELECT uuid_extract_timestamp(gen_random_uuid());  -- null
 SELECT uuid_extract_timestamp('11111111-1111-1111-1111-111111111111');  -- null
 
+-- casts
+SELECT '5b35380a-7143-4912-9b55-f322699c6770'::uuid::bytea;
+SELECT '\x019a2f859ced7225b99d9c55044a2563'::bytea::uuid;
+SELECT '\x1234567890abcdef'::bytea::uuid; -- error
 
 -- clean up
 DROP TABLE guid1, guid2, guid3 CASCADE;
-- 
2.51.2