Re: Generating random unique alphanumeric IDs - Mailing list pgsql-general

From Ivan Sergio Borgonovo
Subject Re: Generating random unique alphanumeric IDs
Date
Msg-id 20090820130023.4638ff5d@dawn.webthatworks.it
Whole thread Raw
In response to Re: Generating random unique alphanumeric IDs  ("Daniel Verite" <daniel@manitou-mail.org>)
Responses Re: Generating random unique alphanumeric IDs  (Thom Brown <thombrown@gmail.com>)
List pgsql-general
On Mon, 17 Aug 2009 12:37:33 +0200
"Daniel Verite" <daniel@manitou-mail.org> wrote:

> http://archives.postgresql.org/pgsql-general/2009-07/msg00194.php


As an exercise I wrote the decrypt version

create or replace function feistel_encrypt(value int)
      returns int as
      $$
      declare
        l1 int;
        l2 int;
        r1 int;
        r2 int;
        i int:=0;
      begin
        l1:= (value >> 16) & 65535;
        r1:= value & 65535;
        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<<16) + r1);
      end;
      $$ language plpgsql strict immutable;
create or replace function feistel_decrypt(value int)
      returns int as
      $$
      declare
        l1 int;
        l2 int;
        r1 int;
        r2 int;
        i int:=0;
      begin
        l2:= (value >> 16) & 65535;
        r2:= value & 65535;
        while i<3 loop
          r1=l2;
          l1:=r2#((((1366.0*l2+150889)%714025)/714025.0)*32767)::int;
          l2:=l1;
          r2:=r1;
          i:=i+1;
        end loop;
        return ((l2::bigint<<16) + r2);
      end;
      $$ language plpgsql strict immutable;

so that

10 = feistel_decrypt(feistel_encrypt(10))

Since I'm then converting to_hex to shorten the string I was
thinking to add some more bits of randomness since eg.

to_hex(10) =  'a'

In the line of
select lpad(
  to_hex(feistel_encrypt(10)),7 , to_hex((rand()*2^31)::int)
);

I was wondering if there is any better way to get alphanumeric
random string quickly. Since uniqueness is assured by passing a
sequence to fesitel_encrypt, I just need turning into to
alphanumeric quickly.


--
Ivan Sergio Borgonovo
http://www.webthatworks.it


pgsql-general by date:

Previous
From: Sam Mason
Date:
Subject: Re: Temp table or normal table for performance?
Next
From: Sam Mason
Date:
Subject: Re: R: Field's position in Table