PostgreSQL：将UUID转换为OID

Question

在PostgreSQL中，是否有将UUID（RFC 4122）转换为OID（ISO 8824）的函数？

“2.25.”后面的值是UUID作为整数的直接十进制编码。它必须是单个整数的直接十进制编码，所有128位都包含在内。它不能被分成几部分。

例如，该函数将采用UUID“f81d4fae-7dec-11d0-a765-00a0c91e6bf6”并返回OID“2.25.329800735698586629295641978511506172918”。

参考资料：

- 创建和使用唯一ID - UUID - OID - B.2 UUID派生UID

- malat

Answer 1

理想情况下，我们将有一个无符号的16字节整数（uint16），并且有一个已注册的类型转换 uuid --> uint16（可能在内部二进制兼容或不兼容，因此成本非常便宜）。然而，在标准的PostgreSQL中没有实现这些内容。

你可以查看（非官方！）附加模块pg_bignum或Evan Caroll的（更加非正式的）分支，以直接接受十六进制输入。（免责声明：未经测试。）

这些模块在大多数托管的安装中不可用。这里是使用标准PostgreSQL内置工具的“穷人版”实现：

CREATE OR REPLACE FUNCTION f_uuid2oid(_uuid uuid)
  RETURNS text
  LANGUAGE sql IMMUTABLE STRICT PARALLEL SAFE AS
$func$
SELECT '2.25.' ||
       ('x0'      ||       left(hex, 15)     )::bit(64)::int8 * numeric '295147905179352825856' -- 1.
     + ('x0'      || right(left(hex, 30), 15))::bit(64)::int8 * numeric '256'                   -- 2.
     + ('x000000' ||       right(hex,  2)    )::bit(32)::int4                                   -- 3.
FROM   translate(_uuid::text, '-', '') t(hex)
$func$;

COMMENT ON FUNCTION public.f_uuid2oid(uuid) IS '
Convert UUID (RFC 4122) into a OID (ISO 8824) ?
First, get text representation of UUID without hyphens:  
  translate(_uuid::text, '-', '')`
Then:
1.
- take the first 15 hex digits
- prefix with x0
- cast to bit(64)
- cast to int8
- multiply with numeric 295147905179352825856 (= 2^68), which is the same as left-shift the binary representation by 68 bits.
  68 bits because: 1 hex digit represents 4 bit; uuid has 128; 128 - 15*4 = 68; so shift by 68
2.  
- take the next 15 hex digits
- prefix with x0
- cast to bit(64)
- cast to int8
- multiply with numeric 256 (= 2^8) shift by the remaining 2 hex digits / 8 bit
3. 
- take the remaining, rightmost 2 hex digits
- prefix with x000000
- cast to bit(32)
- cast to int4

Add 1. + 2. + 3., convert to text, prefix "2.25." Voila.
No leading zeros, according to https://www.rfc-editor.org/rfc/rfc3061

More explanation:
- https://dev59.com/cmsy5IYBdhLWcg3wyxCi#8335376
- https://dba.stackexchange.com/questions/115271/what-is-the-optimal-data-type-for-an-md5-field/115316#115316
';

调用：

SELECT f_uuid2oid('f81d4fae-7dec-11d0-a765-00a0c91e6bf6');

产生请求的OID 2.25.329800735698586629295641978511506172918

在此处找到db<>fiddle 链接

根据https://www.rfc-editor.org/rfc/rfc3061，不要有前导零。
我没有阅读所有不同规范：http://www.oid-info.com/faq.htm#1

我按照我的最佳知识优化了性能，利用了内置（非常快速）从bit(n)到bigint/integer的二进制强制转换。要理解我在做什么，请先阅读：

• 将文本表示中的十六进制转换为十进制数
• MD5字段的最佳数据类型是什么？

Postgres整数类型是带符号的。因此，为了避免溢出成负数，我们不能使用完整的64位（8字节/ 16个十六进制数字），而必须将32个十六进制数字分成三个块，而不是只有两个。我任意地将其切成15个+ 15个+ 2个十六进制数字。

使用left()和right()，因为这通常比substring()稍微快一点。

还要考虑函数的注释。

- Erwin Brandstetter