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

In response to Jasen Betts <jasen@xnet.co.nz>:

> On 2009-04-30, Craig Ringer <craig@postnewspapers.com.au> wrote:
> > Hi
> >
> > This must be a fairly common requirement, but either I don't know how to
> > ask Google about it or there's not as much out there as I would've expected.
> >
> > I'm looking for a way to map the output from a monotonically increasing
> > sequence (not necessarily gapless - ie a normal Pg SEQUENCE) into a
> > fairly random different value in the availible space with a 1:1
> > input->output relationship. In other words, for the input "27" the
> > output will always be the same (say 32 bit) number, and no other input
> > will produce that output.
>
> so you want
>
>   DEFAULT magic_func( nextval('foo_id_seq'::regclass) )
>
> where magic_func is the 1:1 mapping and foo_id_seq is the sequence
> that feeds it.

Sounds like you're reinventing message digests ...

> > If I find something good and there aren't any existing Pl/PgSQL
> > implementations I'll post one for others' use, since I'm pretty sure it
> > must come up a lot. You don't want your database to send out "invoice
> > #1" or "customer #1" after all.

Most of the systems I've seen like this do one of a few things:
* Start with an arbitrary # like 1000
* Prepend the date (pretty common for invoice #s) like 20090501001
* Just start with #1 ... I mean, what's the big deal?

--
Bill Moran
http://www.potentialtech.com
http://people.collaborativefusion.com/~wmoran/