Skip site navigation (1) Skip section navigation (2)

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

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 (view raw or flat)
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

In response to

Responses

pgsql-general by date

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

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group