Received: from malur.postgresql.org ([217.196.149.56]) by arkaria.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1vCifq-00HNul-7B for pgsql-hackers@arkaria.postgresql.org; Sat, 25 Oct 2025 18:07:30 +0000 Received: from localhost ([127.0.0.1] helo=malur.postgresql.org) by malur.postgresql.org with esmtp (Exim 4.94.2) (envelope-from ) id 1vCifp-003uDD-1C for pgsql-hackers@arkaria.postgresql.org; Sat, 25 Oct 2025 18:07:28 +0000 Received: from magus.postgresql.org ([2a02:c0:301:0:ffff::29]) by malur.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.94.2) (envelope-from ) id 1vCifo-003uD4-G4 for pgsql-hackers@lists.postgresql.org; Sat, 25 Oct 2025 18:07:27 +0000 Received: from forwardcorp1b.mail.yandex.net ([178.154.239.136]) by magus.postgresql.org with esmtps (TLS1.3) tls TLS_ECDHE_RSA_WITH_AES_256_GCM_SHA384 (Exim 4.96) (envelope-from ) id 1vCifk-004CSJ-1N for pgsql-hackers@postgresql.org; Sat, 25 Oct 2025 18:07:26 +0000 Received: from mail-nwsmtp-smtp-corp-main-80.iva.yp-c.yandex.net (mail-nwsmtp-smtp-corp-main-80.iva.yp-c.yandex.net [IPv6:2a02:6b8:c0c:7888:0:640:a8fd:0]) by forwardcorp1b.mail.yandex.net (Yandex) with ESMTPS id 04A5380807; Sat, 25 Oct 2025 21:07:22 +0300 (MSK) Received: from smtpclient.apple (unknown [2a02:6bf:8080:b86::1:39]) by mail-nwsmtp-smtp-corp-main-80.iva.yp-c.yandex.net (smtpcorp/Yandex) with ESMTPSA id K7fSph0FA8c0-5QWvcCPW; Sat, 25 Oct 2025 21:07:21 +0300 X-Yandex-Fwd: 1 DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=yandex-team.ru; s=default; t=1761415641; bh=tRFrcKtVaf7+5q7F5cotw+uUe1UUFyrSWT/fr+0140s=; h=References:To:Cc:In-Reply-To:Date:From:Message-Id:Subject; b=c5yzkl59pGIu+5Gvq0brWdbTPG7WiDj2aAFPWtpTqTaBKV0ZuPlv9/x3NXdodWNtZ rgN5cZOvPU7+BVhPbG3p+BMiKwJ8N+6kLqqE9cW6geH6gWVNHo3CpiqA/A6cM+BmuH gmY6MYbZslTqOCkNNm5y3wLwuwdQNolqjb8epSvI= Authentication-Results: mail-nwsmtp-smtp-corp-main-80.iva.yp-c.yandex.net; dkim=pass header.i=@yandex-team.ru From: Andrey Borodin Message-Id: <6F76FA61-E2DC-44EF-9504-889D9BDB4EBD@yandex-team.ru> Content-Type: multipart/mixed; boundary="Apple-Mail=_436DA7CD-98E5-4D1E-8187-2F36890C78C9" Mime-Version: 1.0 (Mac OS X Mail 16.0 \(3826.700.81\)) Subject: Re: Add uuid_to_base32hex() and base32hex_to_uuid() built-in functions Date: Sat, 25 Oct 2025 23:07:10 +0500 In-Reply-To: Cc: Sergey Prokhorenko , pgsql-hackers To: Masahiko Sawada References: <1791665551.452444.1761209220211.ref@mail.yahoo.com> <1791665551.452444.1761209220211@mail.yahoo.com> <18022523-0F8F-4C07-AFF5-57DC9086D78E@yandex-team.ru> <1895971769.8343.1761240853939@mail.yahoo.com> <574624399.175025.1761290201491@mail.yahoo.com> <953203149.383019.1761345585325@mail.yahoo.com> X-Mailer: Apple Mail (2.3826.700.81) List-Id: List-Help: List-Subscribe: List-Post: List-Owner: List-Archive: Archived-At: Precedence: bulk --Apple-Mail=_436DA7CD-98E5-4D1E-8187-2F36890C78C9 Content-Transfer-Encoding: quoted-printable Content-Type: text/plain; charset=us-ascii > On 25 Oct 2025, at 04:31, Masahiko Sawada = wrote: >=20 > Or providing > 'uuid_encode(uuid, format text) -> text' and 'uuid_decode(text, format > text) -> uuid' might make sense too, but I'm not sure. I like the idea, so I drafted a prototype for discussion. Though I do not see what else methods should be provided along with = added one... Best regards, Andrey Borodin. --Apple-Mail=_436DA7CD-98E5-4D1E-8187-2F36890C78C9 Content-Disposition: attachment; filename=v2-0001-Add-uuid_encode-and-uuid_decode-functions.patch Content-Type: application/octet-stream; x-unix-mode=0644; name="v2-0001-Add-uuid_encode-and-uuid_decode-functions.patch" Content-Transfer-Encoding: quoted-printable =46rom=203fb70e9e6e0a3faa5b0e467ee460aae3223e0bef=20Mon=20Sep=2017=20= 00:00:00=202001=0AFrom:=20Andrey=20Borodin=20=0ADate:=20= Thu,=209=20Oct=202025=2018:13:11=20+0500=0ASubject:=20[PATCH=20v2]=20Add=20= uuid_encode()=20and=20uuid_decode()=20functions=0A=0AAdd=20functions=20= for=20encoding=20UUIDs=20to=20alternative=20text=20formats,=0Afollowing=20= the=20encode()/decode()=20pattern.=20Initial=20support=20includes=0A= base32hex=20format=20(RFC=204648)=20producing=2026-character=20unpadded=20= strings.=0ADecoding=20accepts=20both=20padded=20and=20unpadded=20input=20= and=20is=20case-insensitive.=0A=0AThe=20extensible=20design=20allows=20= easy=20addition=20of=20more=20formats.=0A---=0A=20= doc/src/sgml/func/func-uuid.sgml=20=20=20|=20=2075=20++++++++=0A=20= src/backend/utils/adt/uuid.c=20=20=20=20=20=20=20|=20265=20= +++++++++++++++++++++++++++++=0A=20src/include/catalog/pg_proc.dat=20=20=20= =20|=20=20=206=20+=0A=20src/test/regress/expected/uuid.out=20|=20=2098=20= +++++++++++=0A=20src/test/regress/sql/uuid.sql=20=20=20=20=20=20|=20=20= 34=20++++=0A=205=20files=20changed,=20478=20insertions(+)=0A=0Adiff=20= --git=20a/doc/src/sgml/func/func-uuid.sgml=20= b/doc/src/sgml/func/func-uuid.sgml=0Aindex=202638e2bf855..93d11b8341b=20= 100644=0A---=20a/doc/src/sgml/func/func-uuid.sgml=0A+++=20= b/doc/src/sgml/func/func-uuid.sgml=0A@@=20-26,6=20+26,14=20@@=0A=20=20=20= =20uuid_extract_version=0A=20=20=20=0A=20=0A= +=20=20=0A+=20=20=20uuid_to_base32hex=0A+=20= =20=0A+=0A+=20=20=0A+=20=20=20= base32hex_to_uuid=0A+=20=20=0A+=0A=20=20=20= =0A=20=20=20=20=20shows=20= the=20PostgreSQL=0A=20=20=20=20functions=20= that=20can=20be=20used=20to=20generate=20UUIDs.=0A@@=20-167,6=20+175,73=20= @@=0A=20=20=20=20=0A=20=20=20=0A=20=0A+=20=20=0A+=20= =20=20=20shows=20the=20= PostgreSQL=0A+=20=20=20functions=20that=20can=20= be=20used=20to=20convert=20UUIDs=20to=20and=20from=20alternative=20text=20= representations.=0A+=20=20=0A+=0A+=20=20=0A+=20=20=20= <acronym>UUID</acronym>=20Conversion=20Functions=0A+=20=20= =20=0A+=20=20=20=20=0A+=20=20=20=20=20=0A= +=20=20=20=20=20=20=0A+=20=20=20=20=20=20=20=20Function=0A+=20=20=20= =20=20=20=20=0A+=20=20=20=20=20=20=20=0A+=20=20=20=20=20=20=20= =20Description=0A+=20=20=20=20=20=20=20=0A+=20=20=20=20=20=20=20= =0A+=20=20=20=20=20=20=20=20Example(s)=0A+=20=20=20=20=20=20=20= =0A+=20=20=20=20=20=0A+=20=20=20=20=0A+=0A+=20= =20=20=20=0A+=20=20=20=20=20=0A+=20=20=20=20=20=20=0A+=20=20=20=20= =20=20=20=20uuid_encode=0A+=20=20=20=20=20=20=20=20= (=20value=20uuid,=0A+=20=20=20=20=20=20= =20=20format=20text=20)=0A+=20=20=20=20= =20=20=20=20text=0A+=20=20=20=20=20=20=20= =0A+=20=20=20=20=20=20=20=0A+=20=20=20=20=20=20=20=20= Converts=20a=20UUID=20to=20an=20encoded=20text=20string=20using=20the=20= specified=20format.=0A+=20=20=20=20=20=20=20=20Currently=20supported=20= formats:=0A+=20=20=20=20=20=20=20=20base32hex=20-=20= Encodes=20the=20UUID=20as=20a=2026-character=0A+=20=20=20=20=20=20=20=20= base32hex=20string=20(0-9,=20A-V)=20without=20padding,=20as=20defined=20= in=0A+=20=20=20=20=20=20=20=20RFC=20= 4648.=0A+=20=20=20=20=20=20=20=20This=20produces=20a=20more=20= compact=20representation=20than=20the=20standard=20UUID=20format=0A+=20=20= =20=20=20=20=20=20and=20is=20case-insensitive,=20making=20it=20suitable=20= for=20URLs=20and=20identifiers.=0A+=20=20=20=20=20=20=20=0A+=20=20= =20=20=20=20=20=0A+=20=20=20=20=20=20=20=20= uuid_encode('123e4567-e89b-12d3-a456-&zwsp;426614174000'::uuid,=20= 'base32hex')=0A+=20=20=20=20=20=20=20=20= 28V4APV8JC9D792M89J185Q000=0A+=20=20=20=20=20=20= =20=0A+=20=20=20=20=20=0A+=20=20=20=20=20=0A+=20= =20=20=20=20=20=0A+=20=20=20=20=20=20=20=20= uuid_decode=0A+=20=20=20=20=20=20=20=20(=20= string=20text,=0A+=20=20=20=20=20=20=20= =20format=20text=20)=0A+=20=20=20=20=20= =20=20=20uuid=0A+=20=20=20=20=20=20=20=0A= +=20=20=20=20=20=20=20=0A+=20=20=20=20=20=20=20=20Converts=20an=20= encoded=20text=20string=20to=20a=20UUID=20using=20the=20specified=20= format.=0A+=20=20=20=20=20=20=20=20For=20base32hex=20= format,=20the=20input=20can=20be=20either=0A+=20=20=20=20=20=20=20=2026=20= characters=20(unpadded)=20or=2032=20characters=20(padded=20with=20= =3D),=0A+=20=20=20=20=20=20=20=20and=20must=20contain=20= only=20valid=20base32hex=20characters=0A+=20=20=20=20=20=20=20=20(0-9,=20= A-V,=20case-insensitive).=0A+=20=20=20=20=20=20=20=0A+=20=20=20=20= =20=20=20=0A+=20=20=20=20=20=20=20=20= uuid_decode('28V4APV8JC9D792M89J185Q000',=20= 'base32hex')=0A+=20=20=20=20=20=20=20=20= 123e4567-e89b-12d3-a456-426614174000=0A+=20=20= =20=20=20=20=20=0A+=20=20=20=20=20=0A+=20=20=20=20= =0A+=20=20=20=0A+=20=20=0A+=0A=20=20=20=0A= =20=20=20=20PostgreSQL=20also=20provides=20= the=20usual=20comparison=0A=20=20=20=20operators=20shown=20in=20=20for=0Adiff=20--git=20= a/src/backend/utils/adt/uuid.c=20b/src/backend/utils/adt/uuid.c=0Aindex=20= e5f27ff892b..f4e34d6f795=20100644=0A---=20a/src/backend/utils/adt/uuid.c=0A= +++=20b/src/backend/utils/adt/uuid.c=0A@@=20-20,6=20+20,7=20@@=0A=20= #include=20"lib/hyperloglog.h"=0A=20#include=20"libpq/pqformat.h"=0A=20= #include=20"port/pg_bswap.h"=0A+#include=20"utils/builtins.h"=0A=20= #include=20"utils/fmgrprotos.h"=0A=20#include=20"utils/guc.h"=0A=20= #include=20"utils/skipsupport.h"=0A@@=20-777,3=20+778,267=20@@=20= uuid_extract_version(PG_FUNCTION_ARGS)=0A=20=0A=20=09= PG_RETURN_UINT16(version);=0A=20}=0A+=0A+/*=0A+=20*=20UUID=20encoding=20= conversion=20API.=0A+=20*/=0A+struct=20uuid_encoding=0A+{=0A+=09uint64=09= =09(*encode_len)=20(void);=0A+=09uint64=09=09(*decode_len)=20(void);=0A+=09= uint64=09=09(*encode)=20(const=20pg_uuid_t=20*uuid,=20char=20*res);=0A+=09= uint64=09=09(*decode)=20(const=20char=20*data,=20size_t=20dlen,=20= pg_uuid_t=20*res);=0A+};=0A+=0A+static=20const=20struct=20uuid_encoding=20= *uuid_find_encoding(const=20char=20*name);=0A+=0A+/*=0A+=20*=20BASE32HEX=20= encoding=20for=20UUID=0A+=20*=0A+=20*=20Base32hex=20encoding=20uses=2032=20= characters=20(0-9,=20A-V)=20and=20represents=205=20bits=20per=0A+=20*=20= character.=20For=20a=20128-bit=20UUID=20(16=20bytes),=20we=20need=2026=20= characters=0A+=20*=20(128=20bits=20/=205=20bits=20per=20char=20=3D=20= 25.6,=20rounded=20up=20to=2026).=0A+=20*=20As=20defined=20in=20RFC=20= 4648.=0A+=20*/=0A+static=20const=20char=20base32hex_chars[]=20=3D=20= "0123456789ABCDEFGHIJKLMNOPQRSTUV";=0A+=0A+static=20uint64=0A= +base32hex_encode_len(void)=0A+{=0A+=09/*=20128=20bits=20/=205=20bits=20= per=20character=20=3D=2025.6,=20rounded=20up=20to=2026=20*/=0A+=09return=20= 26;=0A+}=0A+=0A+static=20uint64=0A+base32hex_decode_len(void)=0A+{=0A+=09= /*=20Always=2016=20bytes=20for=20UUID=20*/=0A+=09return=20UUID_LEN;=0A+}=0A= +=0A+static=20uint64=0A+base32hex_encode(const=20pg_uuid_t=20*uuid,=20= char=20*res)=0A+{=0A+=09int=09=09=09i;=0A+=09uint64=09=09bits_buffer=20=3D= =200;=0A+=09int=09=09=09bits_in_buffer=20=3D=200;=0A+=09int=09=09=09= output_pos=20=3D=200;=0A+=0A+=09for=20(i=20=3D=200;=20i=20<=20UUID_LEN;=20= i++)=0A+=09{=0A+=09=09/*=20Add=208=20bits=20to=20the=20buffer=20*/=0A+=09= =09bits_buffer=20=3D=20(bits_buffer=20<<=208)=20|=20uuid->data[i];=0A+=09= =09bits_in_buffer=20+=3D=208;=0A+=0A+=09=09/*=20Extract=205-bit=20chunks=20= while=20we=20have=20enough=20bits=20*/=0A+=09=09while=20(bits_in_buffer=20= >=3D=205)=0A+=09=09{=0A+=09=09=09bits_in_buffer=20-=3D=205;=0A+=09=09=09= /*=20Extract=20top=205=20bits=20*/=0A+=09=09=09res[output_pos++]=20=3D=20= base32hex_chars[(bits_buffer=20>>=20bits_in_buffer)=20&=200x1F];=0A+=09=09= =09/*=20Clear=20the=20extracted=20bits=20by=20masking=20*/=0A+=09=09=09= bits_buffer=20&=3D=20((1ULL=20<<=20bits_in_buffer)=20-=201);=0A+=09=09}=0A= +=09}=0A+=0A+=09/*=20Handle=20remaining=20bits=20(128=20%=205=20=3D=203,=20= so=20we=20have=203=20bits=20left)=20*/=0A+=09if=20(bits_in_buffer=20>=20= 0)=0A+=09{=0A+=09=09res[output_pos++]=20=3D=20= base32hex_chars[(bits_buffer=20<<=20(5=20-=20bits_in_buffer))=20&=20= 0x1F];=0A+=09}=0A+=0A+=09return=20output_pos;=0A+}=0A+=0A+static=20= uint64=0A+base32hex_decode(const=20char=20*src,=20size_t=20srclen,=20= pg_uuid_t=20*uuid)=0A+{=0A+=09int=09=09=09i;=0A+=09uint64=09=09= bits_buffer=20=3D=200;=0A+=09int=09=09=09bits_in_buffer=20=3D=200;=0A+=09= int=09=09=09output_pos=20=3D=200;=0A+=09size_t=09=09decode_len=20=3D=20= srclen;=0A+=0A+=09/*=0A+=09=20*=20Accept=20both=20unpadded=20(26=20= chars)=20and=20padded=20(32=20chars)=20input.=0A+=09=20*=20RFC=204648=20= specifies=20padding=20to=20make=20length=20a=20multiple=20of=208.=0A+=09=20= */=0A+=09if=20(srclen=20=3D=3D=2032)=0A+=09{=0A+=09=09/*=20Verify=20= padding:=20should=20be=20exactly=206=20'=3D'=20characters=20at=20the=20= end=20*/=0A+=09=09for=20(i=20=3D=2026;=20i=20<=2032;=20i++)=0A+=09=09{=0A= +=09=09=09if=20(src[i]=20!=3D=20'=3D')=0A+=09=09=09=09ereport(ERROR,=0A+=09= =09=09=09=09=09(errcode(ERRCODE_INVALID_PARAMETER_VALUE),=0A+=09=09=09=09= =09=09=20errmsg("invalid=20base32hex=20padding=20for=20UUID"),=0A+=09=09=09= =09=09=09=20errdetail("Expected=20'=3D'=20padding=20characters=20at=20= position=20%d.",=20i)));=0A+=09=09}=0A+=09=09decode_len=20=3D=2026;=09/*=20= Only=20decode=20the=20first=2026=20characters=20*/=0A+=09}=0A+=09else=20= if=20(srclen=20!=3D=2026)=0A+=09{=0A+=09=09ereport(ERROR,=0A+=09=09=09=09= (errcode(ERRCODE_INVALID_PARAMETER_VALUE),=0A+=09=09=09=09=20= errmsg("invalid=20base32hex=20length=20for=20UUID"),=0A+=09=09=09=09=20= errdetail("Expected=2026=20or=2032=20characters,=20got=20%zu.",=20= srclen)));=0A+=09}=0A+=0A+=09for=20(i=20=3D=200;=20i=20<=20(int)=20= decode_len;=20i++)=0A+=09{=0A+=09=09unsigned=20char=20c=20=3D=20src[i];=0A= +=09=09int=09=09=09val;=0A+=0A+=09=09/*=20Decode=20base32hex=20character=20= (0-9,=20A-V,=20case-insensitive)=20*/=0A+=09=09if=20(c=20>=3D=20'0'=20&&=20= c=20<=3D=20'9')=0A+=09=09=09val=20=3D=20c=20-=20'0';=0A+=09=09else=20if=20= (c=20>=3D=20'A'=20&&=20c=20<=3D=20'V')=0A+=09=09=09val=20=3D=20c=20-=20= 'A'=20+=2010;=0A+=09=09else=20if=20(c=20>=3D=20'a'=20&&=20c=20<=3D=20= 'v')=0A+=09=09=09val=20=3D=20c=20-=20'a'=20+=2010;=0A+=09=09else=0A+=09=09= =09ereport(ERROR,=0A+=09=09=09=09=09= (errcode(ERRCODE_INVALID_PARAMETER_VALUE),=0A+=09=09=09=09=09=20= errmsg("invalid=20base32hex=20digit:=20\"%c\"",=20c)));=0A+=0A+=09=09/*=20= Add=205=20bits=20to=20buffer=20*/=0A+=09=09bits_buffer=20=3D=20= (bits_buffer=20<<=205)=20|=20val;=0A+=09=09bits_in_buffer=20+=3D=205;=0A= +=0A+=09=09/*=20Extract=208-bit=20bytes=20when=20we=20have=20enough=20= bits=20*/=0A+=09=09while=20(bits_in_buffer=20>=3D=208)=0A+=09=09{=0A+=09=09= =09bits_in_buffer=20-=3D=208;=0A+=09=09=09if=20(output_pos=20<=20= UUID_LEN)=0A+=09=09=09{=0A+=09=09=09=09uuid->data[output_pos++]=20=3D=20= (unsigned=20char)=20(bits_buffer=20>>=20bits_in_buffer);=0A+=09=09=09=09= /*=20Clear=20the=20extracted=20bits=20*/=0A+=09=09=09=09bits_buffer=20&=3D= =20((1ULL=20<<=20bits_in_buffer)=20-=201);=0A+=09=09=09}=0A+=09=09}=0A+=09= }=0A+=0A+=09/*=20Verify=20we=20got=20exactly=2016=20bytes=20*/=0A+=09if=20= (output_pos=20!=3D=20UUID_LEN)=0A+=09=09ereport(ERROR,=0A+=09=09=09=09= (errcode(ERRCODE_INVALID_PARAMETER_VALUE),=0A+=09=09=09=09=20= errmsg("invalid=20base32hex=20data=20for=20UUID"),=0A+=09=09=09=09=20= errdetail("Decoded=20to=20%d=20bytes=20instead=20of=20%d.",=20= output_pos,=20UUID_LEN)));=0A+=0A+=09/*=20Verify=20no=20extra=20bits=20= remain=20(should=20be=20exactly=202=20padding=20bits,=20all=20zeros)=20= */=0A+=09if=20(bits_in_buffer=20!=3D=202=20||=20(bits_buffer=20&=20= ((1ULL=20<<=20bits_in_buffer)=20-=201))=20!=3D=200)=0A+=09=09= ereport(ERROR,=0A+=09=09=09=09(errcode(ERRCODE_INVALID_PARAMETER_VALUE),=0A= +=09=09=09=09=20errmsg("invalid=20base32hex=20padding=20for=20UUID")));=0A= +=0A+=09return=20UUID_LEN;=0A+}=0A+=0A+/*=0A+=20*=20Encoding=20lookup=20= table=0A+=20*/=0A+static=20const=20struct=0A+{=0A+=09const=20char=20= *name;=0A+=09struct=20uuid_encoding=20enc;=0A+}=09=09=09uuid_enclist[]=20= =3D=0A+{=0A+=09{=0A+=09=09"base32hex",=0A+=09=09{=0A+=09=09=09= base32hex_encode_len,=20base32hex_decode_len,=20base32hex_encode,=20= base32hex_decode=0A+=09=09}=0A+=09},=0A+=09{=0A+=09=09NULL,=0A+=09=09{=0A= +=09=09=09NULL,=20NULL,=20NULL,=20NULL=0A+=09=09}=0A+=09}=0A+};=0A+=0A= +static=20const=20struct=20uuid_encoding=20*=0A+uuid_find_encoding(const=20= char=20*name)=0A+{=0A+=09int=09=09=09i;=0A+=0A+=09for=20(i=20=3D=200;=20= uuid_enclist[i].name;=20i++)=0A+=09=09if=20= (pg_strcasecmp(uuid_enclist[i].name,=20name)=20=3D=3D=200)=0A+=09=09=09= return=20&uuid_enclist[i].enc;=0A+=0A+=09return=20NULL;=0A+}=0A+=0A+/*=0A= +=20*=20uuid_encode=20-=20encode=20UUID=20to=20text=20using=20specified=20= format=0A+=20*/=0A+Datum=0A+uuid_encode(PG_FUNCTION_ARGS)=0A+{=0A+=09= pg_uuid_t=20=20*uuid=20=3D=20PG_GETARG_UUID_P(0);=0A+=09Datum=09=09name=20= =3D=20PG_GETARG_DATUM(1);=0A+=09text=09=20=20=20*result;=0A+=09char=09=20= =20=20*namebuf;=0A+=09uint64=09=09resultlen;=0A+=09uint64=09=09res;=0A+=09= const=20struct=20uuid_encoding=20*enc;=0A+=0A+=09namebuf=20=3D=20= TextDatumGetCString(name);=0A+=0A+=09enc=20=3D=20= uuid_find_encoding(namebuf);=0A+=09if=20(enc=20=3D=3D=20NULL)=0A+=09=09= ereport(ERROR,=0A+=09=09=09=09(errcode(ERRCODE_INVALID_PARAMETER_VALUE),=0A= +=09=09=09=09=20errmsg("unrecognized=20encoding:=20\"%s\"",=20= namebuf)));=0A+=0A+=09resultlen=20=3D=20enc->encode_len();=0A+=0A+=09= result=20=3D=20(text=20*)=20palloc(VARHDRSZ=20+=20resultlen);=0A+=0A+=09= res=20=3D=20enc->encode(uuid,=20VARDATA(result));=0A+=0A+=09/*=20Make=20= this=20FATAL=20'cause=20we've=20trodden=20on=20memory=20...=20*/=0A+=09= if=20(res=20>=20resultlen)=0A+=09=09elog(FATAL,=20"overflow=20-=20encode=20= estimate=20too=20small");=0A+=0A+=09SET_VARSIZE(result,=20VARHDRSZ=20+=20= res);=0A+=0A+=09PG_RETURN_TEXT_P(result);=0A+}=0A+=0A+/*=0A+=20*=20= uuid_decode=20-=20decode=20text=20to=20UUID=20using=20specified=20format=0A= +=20*/=0A+Datum=0A+uuid_decode(PG_FUNCTION_ARGS)=0A+{=0A+=09text=09=20=20= =20*data=20=3D=20PG_GETARG_TEXT_PP(0);=0A+=09Datum=09=09name=20=3D=20= PG_GETARG_DATUM(1);=0A+=09pg_uuid_t=20=20*result;=0A+=09char=09=20=20=20= *namebuf;=0A+=09char=09=20=20=20*dataptr;=0A+=09size_t=09=09datalen;=0A+=09= uint64=09=09res;=0A+=09const=20struct=20uuid_encoding=20*enc;=0A+=0A+=09= namebuf=20=3D=20TextDatumGetCString(name);=0A+=0A+=09enc=20=3D=20= uuid_find_encoding(namebuf);=0A+=09if=20(enc=20=3D=3D=20NULL)=0A+=09=09= ereport(ERROR,=0A+=09=09=09=09(errcode(ERRCODE_INVALID_PARAMETER_VALUE),=0A= +=09=09=09=09=20errmsg("unrecognized=20encoding:=20\"%s\"",=20= namebuf)));=0A+=0A+=09dataptr=20=3D=20VARDATA_ANY(data);=0A+=09datalen=20= =3D=20VARSIZE_ANY_EXHDR(data);=0A+=0A+=09result=20=3D=20(pg_uuid_t=20*)=20= palloc(sizeof(pg_uuid_t));=0A+=0A+=09res=20=3D=20enc->decode(dataptr,=20= datalen,=20result);=0A+=0A+=09/*=20Make=20this=20FATAL=20'cause=20we've=20= trodden=20on=20memory=20...=20*/=0A+=09if=20(res=20>=20UUID_LEN)=0A+=09=09= elog(FATAL,=20"overflow=20-=20decode=20estimate=20too=20small");=0A+=0A+=09= PG_RETURN_UUID_P(result);=0A+}=0Adiff=20--git=20= a/src/include/catalog/pg_proc.dat=20b/src/include/catalog/pg_proc.dat=0A= index=20b51d2b17379..9e9d15437d2=20100644=0A---=20= a/src/include/catalog/pg_proc.dat=0A+++=20= b/src/include/catalog/pg_proc.dat=0A@@=20-9549,6=20+9549,12=20@@=0A=20{=20= oid=20=3D>=20'6343',=20descr=20=3D>=20'extract=20version=20from=20RFC=20= 9562=20UUID',=0A=20=20=20proname=20=3D>=20'uuid_extract_version',=20= proleakproof=20=3D>=20't',=20prorettype=20=3D>=20'int2',=0A=20=20=20= proargtypes=20=3D>=20'uuid',=20prosrc=20=3D>=20'uuid_extract_version'=20= },=0A+{=20oid=20=3D>=20'8083',=20descr=20=3D>=20'encode=20UUID=20to=20= text=20using=20specified=20format',=0A+=20=20proname=20=3D>=20= 'uuid_encode',=20prorettype=20=3D>=20'text',=0A+=20=20proargtypes=20=3D>=20= 'uuid=20text',=20prosrc=20=3D>=20'uuid_encode'=20},=0A+{=20oid=20=3D>=20= '8084',=20descr=20=3D>=20'decode=20text=20to=20UUID=20using=20specified=20= format',=0A+=20=20proname=20=3D>=20'uuid_decode',=20prorettype=20=3D>=20= 'uuid',=0A+=20=20proargtypes=20=3D>=20'text=20text',=20prosrc=20=3D>=20= 'uuid_decode'=20},=0A=20=0A=20#=20pg_lsn=0A=20{=20oid=20=3D>=20'3229',=20= descr=20=3D>=20'I/O',=0Adiff=20--git=20= a/src/test/regress/expected/uuid.out=20= b/src/test/regress/expected/uuid.out=0Aindex=2095392003b86..8c9ecae44c3=20= 100644=0A---=20a/src/test/regress/expected/uuid.out=0A+++=20= b/src/test/regress/expected/uuid.out=0A@@=20-305,5=20+305,103=20@@=20= SELECT=20uuid_extract_timestamp('11111111-1111-1111-1111-111111111111');=20= =20--=20null=0A=20=20=0A=20(1=20row)=0A=20=0A+--=20UUID=20= encoding/decoding=20functions=0A+--=20test=20uuid_encode=20with=20= base32hex=0A+SELECT=20= uuid_encode('00000000-0000-0000-0000-000000000000'::uuid,=20= 'base32hex');=0A+=20=20=20=20=20=20=20=20uuid_encode=20=20=20=20=20=20=20= =20=20=0A+----------------------------=0A+=2000000000000000000000000000=0A= +(1=20row)=0A+=0A+SELECT=20= uuid_encode('11111111-1111-1111-1111-111111111111'::uuid,=20= 'base32hex');=0A+=20=20=20=20=20=20=20=20uuid_encode=20=20=20=20=20=20=20= =20=20=0A+----------------------------=0A+=20248H248H248H248H248H248H24=0A= +(1=20row)=0A+=0A+SELECT=20= uuid_encode('ffffffff-ffff-ffff-ffff-ffffffffffff'::uuid,=20= 'base32hex');=0A+=20=20=20=20=20=20=20=20uuid_encode=20=20=20=20=20=20=20= =20=20=0A+----------------------------=0A+=20VVVVVVVVVVVVVVVVVVVVVVVVVS=0A= +(1=20row)=0A+=0A+SELECT=20= uuid_encode('123e4567-e89b-12d3-a456-426614174000'::uuid,=20= 'base32hex');=0A+=20=20=20=20=20=20=20=20uuid_encode=20=20=20=20=20=20=20= =20=20=0A+----------------------------=0A+=2028V4APV8JC9D792M89J185Q000=0A= +(1=20row)=0A+=0A+--=20test=20uuid_decode=20with=20base32hex=0A+SELECT=20= uuid_decode('00000000000000000000000000',=20'base32hex');=0A+=20=20=20=20= =20=20=20=20=20=20=20=20=20uuid_decode=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=0A+--------------------------------------=0A+=20= 00000000-0000-0000-0000-000000000000=0A+(1=20row)=0A+=0A+SELECT=20= uuid_decode('28V4APV8JC9D792M89J185Q000',=20'base32hex');=0A+=20=20=20=20= =20=20=20=20=20=20=20=20=20uuid_decode=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=0A+--------------------------------------=0A+=20= 123e4567-e89b-12d3-a456-426614174000=0A+(1=20row)=0A+=0A+--=20test=20= round-trip=20conversions=0A+SELECT=20= uuid_decode(uuid_encode('00000000-0000-0000-0000-000000000000'::uuid,=20= 'base32hex'),=20'base32hex');=0A+=20=20=20=20=20=20=20=20=20=20=20=20=20= uuid_decode=20=20=20=20=20=20=20=20=20=20=20=20=20=20=0A= +--------------------------------------=0A+=20= 00000000-0000-0000-0000-000000000000=0A+(1=20row)=0A+=0A+SELECT=20= uuid_encode(uuid_decode('28V4APV8JC9D792M89J185Q000',=20'base32hex'),=20= 'base32hex');=0A+=20=20=20=20=20=20=20=20uuid_encode=20=20=20=20=20=20=20= =20=20=0A+----------------------------=0A+=2028V4APV8JC9D792M89J185Q000=0A= +(1=20row)=0A+=0A+SELECT=20= uuid_decode(uuid_encode('123e4567-e89b-12d3-a456-426614174000'::uuid,=20= 'base32hex'),=20'base32hex');=0A+=20=20=20=20=20=20=20=20=20=20=20=20=20= uuid_decode=20=20=20=20=20=20=20=20=20=20=20=20=20=20=0A= +--------------------------------------=0A+=20= 123e4567-e89b-12d3-a456-426614174000=0A+(1=20row)=0A+=0A+--=20test=20= case=20insensitivity=0A+SELECT=20= uuid_decode('28v4apv8jc9d792m89j185q000',=20'base32hex');=0A+=20=20=20=20= =20=20=20=20=20=20=20=20=20uuid_decode=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=0A+--------------------------------------=0A+=20= 123e4567-e89b-12d3-a456-426614174000=0A+(1=20row)=0A+=0A+SELECT=20= uuid_decode('28V4APV8JC9D792M89J185Q000',=20'base32hex');=0A+=20=20=20=20= =20=20=20=20=20=20=20=20=20uuid_decode=20=20=20=20=20=20=20=20=20=20=20=20= =20=20=0A+--------------------------------------=0A+=20= 123e4567-e89b-12d3-a456-426614174000=0A+(1=20row)=0A+=0A+--=20test=20RFC=20= 4648=20padding=20(32=20chars=20with=206=20'=3D'=20signs)=0A+SELECT=20= uuid_decode('28V4APV8JC9D792M89J185Q000=3D=3D=3D=3D=3D=3D',=20= 'base32hex');=0A+=20=20=20=20=20=20=20=20=20=20=20=20=20uuid_decode=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=0A= +--------------------------------------=0A+=20= 123e4567-e89b-12d3-a456-426614174000=0A+(1=20row)=0A+=0A+SELECT=20= uuid_decode('00000000000000000000000000=3D=3D=3D=3D=3D=3D',=20= 'base32hex');=0A+=20=20=20=20=20=20=20=20=20=20=20=20=20uuid_decode=20=20= =20=20=20=20=20=20=20=20=20=20=20=20=0A= +--------------------------------------=0A+=20= 00000000-0000-0000-0000-000000000000=0A+(1=20row)=0A+=0A+--=20test=20= error=20cases=20for=20base32hex=0A+SELECT=20uuid_decode('short',=20= 'base32hex');=20=20--=20too=20short=0A+ERROR:=20=20invalid=20base32hex=20= length=20for=20UUID=0A+DETAIL:=20=20Expected=2026=20or=2032=20= characters,=20got=205.=0A+SELECT=20= uuid_decode('28V4APV8JC9D792M89J185Q000X',=20'base32hex');=20=20--=20too=20= long=0A+ERROR:=20=20invalid=20base32hex=20length=20for=20UUID=0A+DETAIL:=20= =20Expected=2026=20or=2032=20characters,=20got=2027.=0A+SELECT=20= uuid_decode('28V4APV8JC9D792M89J185Q00W',=20'base32hex');=20=20--=20= invalid=20character=20(W)=0A+ERROR:=20=20invalid=20base32hex=20digit:=20= "W"=0A+--=20test=20invalid=20encoding=0A+SELECT=20= uuid_encode('123e4567-e89b-12d3-a456-426614174000'::uuid,=20'invalid');=0A= +ERROR:=20=20unrecognized=20encoding:=20"invalid"=0A+SELECT=20= uuid_decode('00000000000000000000000000',=20'invalid');=0A+ERROR:=20=20= unrecognized=20encoding:=20"invalid"=0A=20--=20clean=20up=0A=20DROP=20= TABLE=20guid1,=20guid2,=20guid3=20CASCADE;=0Adiff=20--git=20= a/src/test/regress/sql/uuid.sql=20b/src/test/regress/sql/uuid.sql=0A= index=20465153a0341..b0385a07672=20100644=0A---=20= a/src/test/regress/sql/uuid.sql=0A+++=20b/src/test/regress/sql/uuid.sql=0A= @@=20-146,6=20+146,40=20@@=20SELECT=20= uuid_extract_timestamp('017F22E2-79B0-7CC3-98C4-DC0C0C07398F')=20=3D=20= 'Tuesday=0A=20SELECT=20uuid_extract_timestamp(gen_random_uuid());=20=20= --=20null=0A=20SELECT=20= uuid_extract_timestamp('11111111-1111-1111-1111-111111111111');=20=20--=20= null=0A=20=0A+--=20UUID=20encoding/decoding=20functions=0A+=0A+--=20test=20= uuid_encode=20with=20base32hex=0A+SELECT=20= uuid_encode('00000000-0000-0000-0000-000000000000'::uuid,=20= 'base32hex');=0A+SELECT=20= uuid_encode('11111111-1111-1111-1111-111111111111'::uuid,=20= 'base32hex');=0A+SELECT=20= uuid_encode('ffffffff-ffff-ffff-ffff-ffffffffffff'::uuid,=20= 'base32hex');=0A+SELECT=20= uuid_encode('123e4567-e89b-12d3-a456-426614174000'::uuid,=20= 'base32hex');=0A+=0A+--=20test=20uuid_decode=20with=20base32hex=0A= +SELECT=20uuid_decode('00000000000000000000000000',=20'base32hex');=0A= +SELECT=20uuid_decode('28V4APV8JC9D792M89J185Q000',=20'base32hex');=0A+=0A= +--=20test=20round-trip=20conversions=0A+SELECT=20= uuid_decode(uuid_encode('00000000-0000-0000-0000-000000000000'::uuid,=20= 'base32hex'),=20'base32hex');=0A+SELECT=20= uuid_encode(uuid_decode('28V4APV8JC9D792M89J185Q000',=20'base32hex'),=20= 'base32hex');=0A+SELECT=20= uuid_decode(uuid_encode('123e4567-e89b-12d3-a456-426614174000'::uuid,=20= 'base32hex'),=20'base32hex');=0A+=0A+--=20test=20case=20insensitivity=0A= +SELECT=20uuid_decode('28v4apv8jc9d792m89j185q000',=20'base32hex');=0A= +SELECT=20uuid_decode('28V4APV8JC9D792M89J185Q000',=20'base32hex');=0A+=0A= +--=20test=20RFC=204648=20padding=20(32=20chars=20with=206=20'=3D'=20= signs)=0A+SELECT=20uuid_decode('28V4APV8JC9D792M89J185Q000=3D=3D=3D=3D=3D=3D= ',=20'base32hex');=0A+SELECT=20= uuid_decode('00000000000000000000000000=3D=3D=3D=3D=3D=3D',=20= 'base32hex');=0A+=0A+--=20test=20error=20cases=20for=20base32hex=0A= +SELECT=20uuid_decode('short',=20'base32hex');=20=20--=20too=20short=0A= +SELECT=20uuid_decode('28V4APV8JC9D792M89J185Q000X',=20'base32hex');=20=20= --=20too=20long=0A+SELECT=20uuid_decode('28V4APV8JC9D792M89J185Q00W',=20= 'base32hex');=20=20--=20invalid=20character=20(W)=0A+=0A+--=20test=20= invalid=20encoding=0A+SELECT=20= uuid_encode('123e4567-e89b-12d3-a456-426614174000'::uuid,=20'invalid');=0A= +SELECT=20uuid_decode('00000000000000000000000000',=20'invalid');=0A+=0A=20= =0A=20--=20clean=20up=0A=20DROP=20TABLE=20guid1,=20guid2,=20guid3=20= CASCADE;=0A--=20=0A2.39.5=20(Apple=20Git-154)=0A=0A= --Apple-Mail=_436DA7CD-98E5-4D1E-8187-2F36890C78C9--