使用bigint的plpgsql中的pseudo_encrypt()函数 [英] pseudo_encrypt() function in plpgsql that takes bigint

问题描述

我正在使用一个生成随机ID的系统，如答案#2

I'm working on a system that generates random ids like in answer #2 here.

我的问题是，提到的 pseudo_encrypt()函数只能用于int而不是bigint.我试图重写它，但是它总是返回相同的结果:

My problem is, that the mentioned pseudo_encrypt() function works with int not bigint. I tried to rewrite it but it returns always the same result:

CREATE OR REPLACE FUNCTION pseudo_encrypt(VALUE bigint) returns bigint AS $$
DECLARE
l1 bigint;
l2 int;
r1 bigint;
r2 int;
i int:=0;
BEGIN
    l1:= (VALUE >> 32) & 4294967296::bigint;
    r1:= VALUE & 4294967296;
    WHILE i < 3 LOOP
        l2 := r1;
        r2 := l1 # ((((1366.0 * r1 + 150889) % 714025) / 714025.0) * 32767)::int;
        l1 := l2;
        r1 := r2;
        i := i + 1;
    END LOOP;
RETURN ((l1::bigint << 32) + r1);
END;
$$ LANGUAGE plpgsql strict immutable;

有人可以检查吗?

推荐答案

4294967295必须用作选择32位(而不是4294967296)的位掩码. 这就是为什么您当前为不同的输入获得相同值的原因.

4294967295 must be used as the bitmask to select 32 bits (instead of 4294967296). That's the reason why currently you get the same value for different inputs.

我还建议将bigint用于l2和r2的类型，它们与r1和l1

I'd also suggest using bigint for the types of l2 and r2, they shouldn't really differ from r1 and l1

为了获得更好的随机性，请在PRNG函数中使用更高的乘法器，以获取真正占用32位的中间块，例如32767 * 32767而不是32767.

And, for better randomness, use a much higher multiplier in the PRNG function to get intermediate block that really occupy 32 bits, like 32767*32767 instead of 32767.

完整的修改版本:

CREATE OR REPLACE FUNCTION pseudo_encrypt(VALUE bigint) returns bigint AS $$
DECLARE
l1 bigint;
l2 bigint;
r1 bigint;
r2 bigint;
i int:=0;
BEGIN
    l1:= (VALUE >> 32) & 4294967295::bigint;
    r1:= VALUE & 4294967295;
    WHILE i < 3 LOOP
        l2 := r1;
        r2 := l1 # ((((1366.0 * r1 + 150889) % 714025) / 714025.0) * 32767*32767)::int;
        l1 := l2;
        r1 := r2;
        i := i + 1;
    END LOOP;
RETURN ((l1::bigint << 32) + r1);
END;
$$ LANGUAGE plpgsql strict immutable;

初步结果:

select x,pseudo_encrypt(x::bigint) from generate_series (1, 10) as x;
 x  |   pseudo_encrypt    
----+---------------------
  1 | 3898573529235304961
  2 | 2034171750778085465
  3 |  169769968641019729
  4 | 2925594765163772086
  5 | 1061193016228543981
  6 | 3808195743949274374
  7 | 1943793931158625313
  8 |   88214277952430814
  9 | 2835217030863818694
 10 |  970815170807835400
(10 rows)

这篇关于使用bigint的plpgsql中的pseudo_encrypt()函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！