Skip site navigation (1)
Skip section navigation (2)
## Re: Re: Mapping output from a SEQUENCE into something
non-repeating/colliding but random-looking?

### In response to

### Responses

### pgsql-general by date

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

- Re: Re: Mapping output from a SEQUENCE into something non-repeating/colliding but random-looking? at 2009-05-01 13:06:14 from Craig Ringer

- Re: Re: Mapping output from a SEQUENCE into something non-repeating/colliding but random-looking? at 2009-05-03 05:13:02 from Craig Ringer
- Re: Re: Mapping output from a SEQUENCE into something non-repeating/colliding but random-looking? at 2009-05-03 07:00:31 from Craig Ringer
- Feistel cipher, shorter string and hex to int at 2009-07-06 16:45:35 from Ivan Sergio Borgonovo

Next: From:Alban HertroysDate:2009-05-02 10:14:53Subject: Re: Difference between array column type and separate tablePrevious: From: Mike ChristensenDate: 2009-05-02 07:33:59Subject: Difference between array column type and separate table