PostgreSQL:将UUID转换为OID [英] PostgreSQL: Convert UUID into OID
问题描述
PostgreSQL中是否有将UUID(RFC 4122)转换为OID(ISO 8824)的函数?
Is there a function in PostgreSQL to convert a UUID (RFC 4122) into a OID (ISO 8824) ?
"2.25"之后的值.是UUID的直接十进制编码，为整数.它必须是所有128位的单个整数的直接十进制编码.一定不能将其分解为多个部分.
The value after "2.25." is the straight decimal encoding of the UUID as an integer. It MUST be a direct decimal encoding of the single integer, all 128 bits. It must not be broken up into parts.
例如，该函数将采用UUID "f81d4fae-7dec-11d0-a765-00a0c91e6bf6" 并返回OID "2.25.329800735698586629295641978511506172918" .
For example the function would take UUID "f81d4fae-7dec-11d0-a765-00a0c91e6bf6" and return the OID "2.25.329800735698586629295641978511506172918".
参考:
推荐答案
理想情况，我们将拥有一个无符号的16字节整数( uint16 )和一个已注册的强制转换<代码> uuid->uint16 (内部可能会或可能不会与二进制兼容，这使其非常便宜).在现有的PostgreSQL中都没有实现这一切.
Ideally, we would have an unsigned 16-byte integer (uint16) and a registered cast uuid --> uint16 (which may or may not be binary compatible internally, making it super-cheap). None of this is implemented in stock PostgreSQL.
您可能希望使用(非官方的！)附加模块 pg_bignum 或 Evan Caroll(甚至是非官方的)叉子接受十六进制直接输入.(免责声明:未经测试.)
You might look to the (unofficial!) additional module pg_bignum or Evan Caroll's (even more unofficial) fork to accept hex input directly. (Disclaimer: untested.)
这些模块在大多数托管安装中不可用.这是使用标准PostgreSQL内置工具的穷人的实现方式:
These modules are not available on most hosted installations. Here is a poor man's implementation with built-in tools of standard 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://tools.ietf.org/html/rfc3061
More explanation:
- https://stackoverflow.com/questions/8316164/convert-hex-in-text-representation-to-decimal-number/8335376#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<>小提琴此处
根据 https://tools.ietf.org/html/rfc3061.
我没有阅读所有各种标准: http://www.oid-info.com/faq.htm#1
据我所知，我利用从 bit(n)到 bigint /的内置(非常快)二进制强制转换优化了性能整数.要了解我在那做什么，请先阅读:
I optimized performance to the best of my knowledge, leveraging the built-in (very fast) binary coercible cast from bit(n) to bigint /integer. To understand what I am doing there, first read:
Postgres整数类型是 signed .因此，为了避免溢出到负数，我们不能使用完整的64位(8字节/16个十六进制数字)，而必须将32个十六进制数字转换为三位垃圾，而不是仅转换为两位.我随意将其切成15 + 15 + 2个十六进制数字.
Postgres integer types are signed. So - to avoid overflowing to negative numbers - we cannot use the full 64 bit (8 byte / 16 hex digits) and we have to convert the 32 hex digits in three junks instead of just two. I arbitrarily slice it up as 15 + 15 + 2 hex digits.
使用 left()和 right()，因为通常比 substring()快一点.
Using left() and right() as that's typically a tiny bit faster than substring().
还要考虑对该功能的注释.
Also consider the comment to the function.
这篇关于PostgreSQL:将UUID转换为OID的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
