Re: Re: Mapping output from a SEQUENCE into something non-repeating/colliding but random-looking?

    Craig Ringer wrote:

> What I'm looking for is a function that, given an input within a
> constrained range (say, a 32 bit integer) produces a different
> output within the same range. For any given input, the output
> should be the same each time, and for any given output
> there should only be one input that results in that output.

That's a permutation, as used in symmetric ciphering. A proven way to
build one is to use a Feistel network:
http://en.wikipedia.org/wiki/Feistel_cipher
In principle, the function used to encode the blocks uses a cipher key,
but a pseudo-randomizing of the input is good enough when you're not
interested in making it crypto-secure.
Here is a plpgqsl implementation:

CREATE OR REPLACE FUNCTION pseudo_encrypt(value int) returns bigint 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;

Note that it returns a bigint because we don't have unsigned integers
in PG. If you're OK with getting negative values, the return type can
be changed to int.
Otherwise if you need a positive result that fits in 32 bits, it's
possible to tweak the code to use 15 bits blocks instead of 16, but
then the input will have to be less than 2^30.

Best regards,
--
Daniel
PostgreSQL-powered mail user agent and storage:
http://www.manitou-mail.org