Dagfinn Ilmari Mannsåker <[email protected]> writes: > Jelte Fennema-Nio <[email protected]> writes: > >> Then combining that with UUID <-> bytea casting (which also >> seems totally reasonable functionality to me), would give you the >> functionality (but not the defaults you want). > > +1 for adding casts.
Here's a patch for that. I'm not 100% confident about the error code for invalid length, but that was the closest one I could find in errcodes.txt. I might do the base32hex encode/decode later, unless someone else beats me to it. - ilmari
>From 1ef47968fd1852f7a409764db2c4b35f0eb948b6 Mon Sep 17 00:00:00 2001 From: =?UTF-8?q?Dagfinn=20Ilmari=20Manns=C3=A5ker?= <[email protected]> Date: Tue, 28 Oct 2025 16:33:17 +0000 Subject: [PATCH] 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..e51190d2cb2 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_PARAMETER_VALUE), + 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..44f58454d19 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 '11111111-1111-1111-1111-111111111111'::uuid::bytea; + bytea +------------------------------------ + \x11111111111111111111111111111111 +(1 row) + +SELECT '\xffffffffffffffffffffffffffffffff'::bytea::uuid; + uuid +-------------------------------------- + ffffffff-ffff-ffff-ffff-ffffffffffff +(1 row) + +SELECT '\x12345678'::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..ef81be93670 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 '11111111-1111-1111-1111-111111111111'::uuid::bytea; +SELECT '\xffffffffffffffffffffffffffffffff'::bytea::uuid; +SELECT '\x12345678'::bytea::uuid; -- error -- clean up DROP TABLE guid1, guid2, guid3 CASCADE; -- 2.51.1
