From: | "Daniel Verite" <daniel(at)manitou-mail(dot)org> |
---|---|

To: | pgsql-general(at)postgresql(dot)org |

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

Date: | 2009-05-02 09:26:28 |

Message-ID: | 448163db-cac5-4e99-8c4c-57cbc6f6af78@mm |

Views: | Raw Message | Whole Thread | Download mbox | Resend email |

Thread: | |

Lists: | pgsql-general |

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

From | Date | Subject | |
---|---|---|---|

Next Message | Alban Hertroys | 2009-05-02 10:14:53 | Re: Difference between array column type and separate table |

Previous Message | Mike Christensen | 2009-05-02 07:33:59 | Difference between array column type and separate table |