Thread: function for creating random id

hi,

i've got a table called "user". every user should have a userid, which
is the primary key. this userid is a random number with up to 6 digits.

to get this userid i wrote a function in plpgsql:

CREATE FUNCTION getuid(int) RETURNS int AS '
DECLARE
uid     INTEGER;
BEGIN
SELECT (random() * (pow(10.0, $1::float)-1))::int INTO uid;
RETURN uid;
END;' LANGUAGE 'plpgsql' WITH (isstrict);

user was created with:

CREATE TABLE "user" (id numeric(6,0) DEFAULT getuid(6), username
VARCHAR(20), password VARCHAR(20), name VARCHAR(30), forename
VARCHAR(30), CONSTRAINT "user_pk" PRIMARY KEY("id"));


but now the problem is that the id which is returned by getuid() could
already be in use.
so what is the best method to realize that?
i mean isn't there already a function which returns a boolean, saying if
this id is already in use or not?

thanks,
 moritz

--
Subject: Linux box finds it hard to wake up in the morning

I've heard of dogs being like their owners, but Linux boxen?
    -- Peter Hunter <peter.hunter@blackfriars.oxford.ac.uk>

On Mon, Apr 15, 2002 at 05:51:53PM +0200,
  Moritz Sinn <moritz@freesources.org> wrote:
> hi,
>
> i've got a table called "user". every user should have a userid, which
> is the primary key. this userid is a random number with up to 6 digits.
>
> but now the problem is that the id which is returned by getuid() could
> already be in use.
> so what is the best method to realize that?

Do the IDs really need to be random? Is the order that users were created
in really secret information?

If they don't, you can use a sequence to allocate IDs.

I've gotten intot he habit of using a sequence identifier, and couple
that with the current time then md5 sum that to create unique uids.
 Works our fairly well, and is atleast marginally difficult to guess.

Bruno Wolff III wrote:

>On Mon, Apr 15, 2002 at 05:51:53PM +0200,
>  Moritz Sinn <moritz@freesources.org> wrote:
>
>>hi,
>>
>>i've got a table called "user". every user should have a userid, which
>>is the primary key. this userid is a random number with up to 6 digits.
>>
>>but now the problem is that the id which is returned by getuid() could
>>already be in use.
>>so what is the best method to realize that?
>>
>
>Do the IDs really need to be random? Is the order that users were created
>in really secret information?
>
>If they don't, you can use a sequence to allocate IDs.
>
>---------------------------(end of broadcast)---------------------------
>TIP 5: Have you checked our extensive FAQ?
>
>http://www.postgresql.org/users-lounge/docs/faq.html
>

Bruno Wolff III <bruno@wolff.to> writes:

> Do the IDs really need to be random? Is the order that users were created
> in really secret information?
>
> If they don't, you can use a sequence to allocate IDs.

yes, i think it would be better if they're random.

--
Feel free to contact me (flames about my english and the useless of this
driver will be redirected to /dev/null, oh no, it's full...).
    -- Michael Beck, describing the PC-speaker sound device

Michael Loftis <mloftis@wgops.com> writes:

> I've gotten intot he habit of using a sequence identifier, and couple
> that with the current time then md5 sum that to create unique
> uids. Works our fairly well, and is atleast marginally difficult to
> guess.

that sounds good. could you send me your function definition? because i
don't now how to md5 sum in postgresql.

thanks,
        moritz


--
Go not unto the Usenet for advice, for you will be told both yea and nay (and
quite a few things that just have nothing at all to do with the question).
    -- seen in a .sig somewhere

On 16 Apr 2002 15:14:09 +0200
"Moritz Sinn" <moritz@freesources.org> wrote:
> Michael Loftis <mloftis@wgops.com> writes:
>
> > I've gotten intot he habit of using a sequence identifier, and couple
> > that with the current time then md5 sum that to create unique
> > uids. Works our fairly well, and is atleast marginally difficult to
> > guess.
>
> that sounds good. could you send me your function definition? because i
> don't now how to md5 sum in postgresql.

An implementation of MD5 is part of contrib/pgcrypto.

Cheers,

Neil

P.S. I remember hearing that MD4 has been found to be vulnerable
already, and that there are some theoretical weaknesses in
MD5. Does anyone have more info on this? I personally use
SHA1 anyway...

--
Neil Conway <neilconway@rogers.com>
PGP Key ID: DB3C29FC

Thanks for the quick reply, I was (literally) sleeping :)

USing MD5 for the user-id is just to try to keep it looking fairly
random, in az a well-built system guessing this hash wouldn't mean
getting access.

Neil Conway wrote:

>On 16 Apr 2002 15:14:09 +0200
>"Moritz Sinn" <moritz@freesources.org> wrote:
>
>>Michael Loftis <mloftis@wgops.com> writes:
>>
>>>I've gotten intot he habit of using a sequence identifier, and couple
>>>that with the current time then md5 sum that to create unique
>>>uids. Works our fairly well, and is atleast marginally difficult to
>>>guess.
>>>
>>that sounds good. could you send me your function definition? because i
>>don't now how to md5 sum in postgresql.
>>
>
>An implementation of MD5 is part of contrib/pgcrypto.
>
>Cheers,
>
>Neil
>
>P.S. I remember hearing that MD4 has been found to be vulnerable
>already, and that there are some theoretical weaknesses in
>MD5. Does anyone have more info on this? I personally use
>SHA1 anyway...
>