Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 9B855632FF3 for ; Tue, 20 Oct 2009 17:14:35 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 99720-03 for ; Tue, 20 Oct 2009 20:14:27 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from qw-out-2122.google.com (qw-out-2122.google.com [74.125.92.26]) by mail.postgresql.org (Postfix) with ESMTP id 2142263281A for ; Tue, 20 Oct 2009 17:14:26 -0300 (ADT) Received: by qw-out-2122.google.com with SMTP id 9so660640qwb.61 for ; Tue, 20 Oct 2009 13:14:25 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:in-reply-to:references :date:message-id:subject:from:to:cc:content-type; bh=262zfavspWJqoSrDBwXNUW7/AyZW1BVNOI5VludAj3w=; b=FzFO4GPxQsvlCmQtDqKNzlOw1MNa2N6374ovwpDlA7kK+K5+xwYEP58jvuCuvyOyJC L9IaeuveIpe5MJimbzzJgs2jJVDr8auZZlwkdgMBcPjsTqTmZrCoTSBK3HZEGe2fIPqU TnrnAPlru0D1UxTvGl0Cb+G6zGln2fte+sO3o= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:in-reply-to:references:date:message-id:subject:from:to :cc:content-type; b=btVTnvZV27HUHOqqE2JHTFv/1RFCQ3I1vWXU3J4loy3Hxq/C/eZatBQ8M73xrgsFjI WgXtO+S5A4DtzIxBmyETiO2Tv7t+lYlvQ5gaRJeIAAnNXLsEjc+QNXndkNIueWQ1Uj92 LqNNZmVNFq2z/5gQSjR21iZpsjCywSOIN2zfg= MIME-Version: 1.0 Received: by 10.229.31.147 with SMTP id y19mr1051891qcc.5.1256069665353; Tue, 20 Oct 2009 13:14:25 -0700 (PDT) In-Reply-To: <4ADE17A2.6010003@gmail.com> References: <80df75370910201249k40e308dbh668872e0e2ed2419@mail.gmail.com> <4ADE1520.5080203@gmail.com> <80df75370910201257m2d64ad7tad0636f9b664bdeb@mail.gmail.com> <4ADE17A2.6010003@gmail.com> Date: Tue, 20 Oct 2009 17:14:25 -0300 Message-ID: <80df75370910201314x6b267a03wc04cb164c75af781@mail.gmail.com> Subject: Re: Random Unique Id From: Nahuel Alejandro Ramos To: Rob Sargent Cc: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=0016364c67233773220476638200 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.739 tagged_above=-10 required=5 tests=BAYES_20=-0.74, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200910/82 X-Sequence-Number: 33281 --0016364c67233773220476638200 Content-Type: text/plain; charset=ISO-8859-1 :D, Yeah, i know. Its only an example. We are using this algorithm to give a random unique Id to our clients. The need was to give a PK absolute independent of time. Thanks to Ivan, for the pseudo-random posted, I am looking it. Regards... Nahuel Alejandro Ramos. On Tue, Oct 20, 2009 at 5:03 PM, Rob Sargent wrote: > Suit yourself, of course, but the numbers on my credit cards are far, > far from random :) > > > Nahuel Alejandro Ramos wrote: > > Yes. I looked this solution but it is not a "only numbers" ID. I would > > like a random unique "number" Id. For example: generate a credit number > > randomly (like the example I post). > > I used to insert an MD5 field but this time I need "only numbers" Id. > > Regards... > > > > Nahuel Alejandro Ramos. > > > > > > On Tue, Oct 20, 2009 at 4:53 PM, Rob Sargent > > wrote: > > > > > > > > Nahuel Alejandro Ramos wrote: > > > Hi all, > > > I was searching for a sequence (for serials) that let me use a > > random > > > unique number ID on a Primary Key or a simple index. > > > I have not found a solution so I have done it by myself. I would > > like > > > to share it so here it is: > > > > > > -- ---------------------------- > > > -- Create language "plpgsql" > > > -- ---------------------------- > > > CREATE LANGUAGE plpgsql; > > > > > > -- ---------------------------- > > > -- Table structure for "public"."tarjeta" > > > -- ---------------------------- > > > drop table "public"."tarjeta"; > > > CREATE TABLE "public"."tarjeta"( > > > "idtarjeta" int8 NOT NULL DEFAULT randomuniqueidtarjeta(), > > > "fechaemision" timestamp NOT NULL DEFAULT now(), > > > "descripcion" varchar(255) , > > > PRIMARY KEY ("idtarjeta") > > > ) WITHOUT OIDS; > > > > > > -- ---------------------------- > > > -- Definition of function "randomuniqueidtarjeta" > > > -- ---------------------------- > > > > > > CREATE OR REPLACE FUNCTION randomUniqueIdTarjeta() RETURNS bigint > > AS $$ > > > > > > DECLARE > > > > > > -- SET THE KEY SIZE (IN CHARACTERS) > > > idSize constant integer := 10; > > > > > > sizeMultiplicator constant bigint := 10 ^ idSize; > > > > > > loopLimit bigint := sizeMultiplicator * 4; > > > randomNumber bigint; > > > canIUseIt boolean := false; > > > > > > BEGIN > > > > > > -- LOOP UNTIL FIND AN UNIQUE RANDOM NUMBER OR FILL THE LOOP > LIMIT > > > WHILE ( not ( canIUseIt ) and ( loopLimit > 0) ) LOOP > > > > > > -- CALCULATE A TEN DIGITS RANDOM NUMBER > > > randomNumber := CAST ( ( random() * sizeMultiplicator ) AS > > bigint ); > > > > > > -- VALIDATE THAT THE NUMBER WON'T START WITH 0 > > > IF ( (randomNumber >= sizeMultiplicator / 10 ) and ( > > > randomNumber < sizeMultiplicator ) ) THEN > > > > > > -- CHECK THAN RANDOM NUMBER NOT EXISTS ON TABLE > > > -- IMPORTANT!!! CHANGE WITH TABLE AND FIELD TO SEARCH > > FOR A > > > DUPLICATATION > > > PERFORM idtarjeta FROM tarjeta WHERE idtarjeta = > > randomNumber; > > > IF NOT FOUND THEN > > > canIUseIt = true; > > > END IF; > > > > > > END IF; > > > > > > loopLimit = loopLimit - 1; > > > > > > END LOOP; > > > > > > -- IF LOOP ENDS WITH A UNIQUE RANDOM NUMBER RETURNS THE NUMBER > > > CONCATENATED WITH A VERIFICATION NUMBER ELSE RAISE AN EXCEPTION > > > IF ( canIUseIt ) THEN > > > RETURN CAST ( ( CAST ( randomNumber AS text ) || CAST ( > > > verificationNumber( CAST (randomNumber AS text ) ) AS text ) ) AS > > > bigint ) ; > > > ELSE > > > RAISE EXCEPTION 'Could not calculate a Random Unique ID on > > table > > > Tarjeta.'; > > > END IF; > > > > > > END; > > > $$ LANGUAGE plpgsql; > > > > > > -- ---------------------------- > > > -- Definition of function "randomuniqueidtarjeta" > > > -- ---------------------------- > > > > > > CREATE OR REPLACE FUNCTION verificationNumber(id text) RETURNS > > integer AS $$ > > > DECLARE > > > > > > auxValue integer := 0; > > > verifyArray constant integer[] :=ARRAY [5,4,3,2,7,6,5,4,3,2,1]; > > > verificationNumber integer := 0; > > > > > > BEGIN > > > > > > -- ASK FOR ID LENGTH EQUAL TO 10 DIGITS. THE ALGORITHM WORKS > > ONLY FOR > > > IF ( LENGTH( id ) <> 10 ) THEN > > > > > > RAISE EXCEPTION 'Could not calculate a verification > > number. The > > > ID must have 10 digits.'; > > > > > > ELSE > > > > > > -- LOOP ON THE TEN DIGITS AND MULTIPLY WITH THE SAME > > POSITION ON > > > virifyArray > > > FOR digit IN 1..10 LOOP > > > auxValue = auxValue + ( CAST(SUBSTR(id, digit, 1) AS > > int) * > > > verifyArray[digit] ); > > > END LOOP; > > > > > > -- CALCULATE THE VERIFICATION NUMBER > > > verificationNumber = 11 - (auxValue % 11); > > > > > > -- REPLACE THE TWO DIGITS VERIFICATION NUMBER > > > IF( verificationNumber = 11 ) THEN > > > RETURN 0; > > > ELSEIF ( verificationNumber = 10 ) THEN > > > RETURN 9; > > > ELSE > > > RETURN verificationNumber; > > > END IF; > > > > > > END IF; > > > > > > END; > > > $$ LANGUAGE plpgsql; > > > > > > -- ---------------------------- > > > -- INSERTs to probe the functions > > > -- ---------------------------- > > > INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 1'); > > > INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 2'); > > > INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 3'); > > > INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 4'); > > > INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 5'); > > > INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 6'); > > > INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 7'); > > > INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 8'); > > > INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 9'); > > > INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 10'); > > > > > > > > > It is my first post so sorry about the format of the SQL > > Dump and > > > sorry about my english. > > > Note: there is a second function that calculate a > verification > > > number like an argentinian code called CUIL (only available for 10 > > > digits numbers) > > > Regards.... > > > > > > Nahuel Alejandro Ramos. > > > > > > > You didn't like UUID? > > > > > --0016364c67233773220476638200 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable :D, Yeah, i know. Its only an example. We are using this algorithm to give = a random unique Id to our clients. The need was to give a PK absolute indep= endent of time. Thanks to Ivan, for the pseudo-random posted, I am looking = it.
Regards...

=A0=A0=A0=A0=A0 Nahuel Alejandro Ramos.

On Tue, Oct 20, 2009 at 5:03 PM, Rob Sargent <robjsargent@gmail.com= > wrote:
Suit yourself, of= course, but the numbers on my credit cards are far,
far from random :)


Nahuel Alejandro Ramos wrote:
> Yes. I looked this solution but it is not a "only numbers" I= D. I would
> like a random unique "number" Id. For example: generate a cr= edit number
> randomly (like the example I post).
> I used to insert an MD5 field but this time I need "only numbers&= quot; Id.
> Regards...
>
> =A0 =A0 =A0 Nahuel Alejandro Ramos.
>
>
> On Tue, Oct 20, 2009 at 4:53 PM, Rob Sargent <robjsargent@gmail.com
> <mailto:robjsargent@gmail.com>> wrote:
>
>
>
> =A0 =A0 Nahuel Alejandro Ramos wrote:
> =A0 =A0 > Hi all,
> =A0 =A0 > =A0 I was searching for a sequence (for serials) that let= me use a
> =A0 =A0 random
> =A0 =A0 > unique number ID on a Primary Key or a simple index.
> =A0 =A0 > =A0 I have not found a solution so I have done it by myse= lf. I would
> =A0 =A0 like
> =A0 =A0 > to share it so here it is:
> =A0 =A0 >
> =A0 =A0 > -- ----------------------------
> =A0 =A0 > -- Create language "plpgsql"
> =A0 =A0 > -- ----------------------------
> =A0 =A0 > CREATE LANGUAGE plpgsql;
> =A0 =A0 >
> =A0 =A0 > -- ----------------------------
> =A0 =A0 > -- Table structure for "public"."tarjeta&q= uot;
> =A0 =A0 > -- ----------------------------
> =A0 =A0 > drop table "public"."tarjeta";
> =A0 =A0 > CREATE TABLE "public"."tarjeta"(
> =A0 =A0 > "idtarjeta" int8 NOT NULL DEFAULT randomuniquei= dtarjeta(),
> =A0 =A0 > "fechaemision" timestamp NOT NULL DEFAULT now()= ,
> =A0 =A0 > "descripcion" varchar(255) ,
> =A0 =A0 > PRIMARY KEY ("idtarjeta")
> =A0 =A0 > ) =A0WITHOUT OIDS;
> =A0 =A0 >
> =A0 =A0 > -- ----------------------------
> =A0 =A0 > -- Definition of function =A0"randomuniqueidtarjeta&= quot;
> =A0 =A0 > -- ----------------------------
> =A0 =A0 >
> =A0 =A0 > CREATE OR REPLACE FUNCTION randomUniqueIdTarjeta() RETURN= S bigint
> =A0 =A0 AS $$
> =A0 =A0 >
> =A0 =A0 > DECLARE
> =A0 =A0 >
> =A0 =A0 > =A0 =A0 -- SET THE KEY SIZE (IN CHARACTERS)
> =A0 =A0 > =A0 =A0 idSize constant =A0integer :=3D 10;
> =A0 =A0 >
> =A0 =A0 > =A0 =A0 sizeMultiplicator constant bigint :=3D 10 ^ idSiz= e;
> =A0 =A0 >
> =A0 =A0 > =A0 =A0 loopLimit =A0bigint :=3D sizeMultiplicator * 4; > =A0 =A0 > =A0 =A0 randomNumber bigint;
> =A0 =A0 > =A0 =A0 canIUseIt boolean :=3D false;
> =A0 =A0 >
> =A0 =A0 > BEGIN
> =A0 =A0 >
> =A0 =A0 > =A0 =A0 -- LOOP UNTIL FIND AN UNIQUE RANDOM NUMBER OR FIL= L THE LOOP LIMIT
> =A0 =A0 > =A0 =A0 WHILE ( not ( canIUseIt ) and ( loopLimit > 0)= ) LOOP
> =A0 =A0 >
> =A0 =A0 > =A0 =A0 =A0 =A0 -- CALCULATE A TEN DIGITS RANDOM NUMBER > =A0 =A0 > =A0 =A0 =A0 =A0 randomNumber :=3D CAST ( ( random() * siz= eMultiplicator ) AS
> =A0 =A0 bigint );
> =A0 =A0 >
> =A0 =A0 > =A0 =A0 =A0 =A0 -- VALIDATE THAT THE NUMBER WON'T STA= RT WITH 0
> =A0 =A0 > =A0 =A0 =A0 =A0 IF ( (randomNumber >=3D sizeMultiplica= tor / 10 ) and (
> =A0 =A0 > randomNumber < sizeMultiplicator ) ) THEN
> =A0 =A0 >
> =A0 =A0 > =A0 =A0 =A0 =A0 =A0 =A0 -- CHECK THAN RANDOM NUMBER NOT E= XISTS ON TABLE
> =A0 =A0 > =A0 =A0 =A0 =A0 =A0 =A0 -- IMPORTANT!!! CHANGE WITH TABLE= AND FIELD TO SEARCH
> =A0 =A0 FOR A
> =A0 =A0 > DUPLICATATION
> =A0 =A0 > =A0 =A0 =A0 =A0 =A0 =A0 PERFORM idtarjeta FROM tarjeta WH= ERE idtarjeta =3D
> =A0 =A0 randomNumber;
> =A0 =A0 > =A0 =A0 =A0 =A0 =A0 =A0 IF NOT FOUND THEN
> =A0 =A0 > =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 canIUseIt =3D true;
> =A0 =A0 > =A0 =A0 =A0 =A0 =A0 =A0 END IF;
> =A0 =A0 >
> =A0 =A0 > =A0 =A0 =A0 =A0 END IF;
> =A0 =A0 >
> =A0 =A0 > =A0 =A0 =A0 =A0 loopLimit =3D loopLimit - 1;
> =A0 =A0 >
> =A0 =A0 > =A0 =A0 END LOOP;
> =A0 =A0 >
> =A0 =A0 > =A0 =A0 -- IF LOOP ENDS WITH A UNIQUE RANDOM NUMBER RETUR= NS THE NUMBER
> =A0 =A0 > CONCATENATED WITH A VERIFICATION NUMBER =A0ELSE RAISE AN = EXCEPTION
> =A0 =A0 > =A0 =A0 IF ( canIUseIt ) THEN
> =A0 =A0 > =A0 =A0 =A0 =A0 RETURN CAST ( ( CAST ( randomNumber AS te= xt ) || CAST (
> =A0 =A0 > verificationNumber( CAST (randomNumber AS text ) ) AS tex= t =A0) ) AS
> =A0 =A0 > bigint ) =A0;
> =A0 =A0 > =A0 =A0 ELSE
> =A0 =A0 > =A0 =A0 =A0 =A0 RAISE EXCEPTION 'Could not calculate = a Random Unique ID on
> =A0 =A0 table
> =A0 =A0 > Tarjeta.';
> =A0 =A0 > =A0 =A0 END IF;
> =A0 =A0 >
> =A0 =A0 > END;
> =A0 =A0 > $$ LANGUAGE plpgsql;
> =A0 =A0 >
> =A0 =A0 > -- ----------------------------
> =A0 =A0 > -- Definition of function =A0"randomuniqueidtarjeta&= quot;
> =A0 =A0 > -- ----------------------------
> =A0 =A0 >
> =A0 =A0 > CREATE OR REPLACE FUNCTION verificationNumber(id text) RE= TURNS
> =A0 =A0 integer AS $$
> =A0 =A0 > DECLARE
> =A0 =A0 >
> =A0 =A0 > =A0 =A0 auxValue integer :=3D 0;
> =A0 =A0 > =A0 =A0 verifyArray constant integer[] :=3DARRAY [5,4,3,2= ,7,6,5,4,3,2,1];
> =A0 =A0 > =A0 =A0 verificationNumber integer :=3D 0;
> =A0 =A0 >
> =A0 =A0 > BEGIN
> =A0 =A0 >
> =A0 =A0 > =A0 =A0 -- ASK FOR ID LENGTH EQUAL TO 10 DIGITS. THE ALGO= RITHM WORKS
> =A0 =A0 ONLY FOR
> =A0 =A0 > =A0 =A0 IF ( LENGTH( id ) <> 10 ) THEN
> =A0 =A0 >
> =A0 =A0 > =A0 =A0 =A0 =A0 RAISE EXCEPTION 'Could not calculate = a verification
> =A0 =A0 number. The
> =A0 =A0 > ID must have 10 digits.';
> =A0 =A0 >
> =A0 =A0 > =A0 =A0 ELSE
> =A0 =A0 >
> =A0 =A0 > =A0 =A0 =A0 =A0 -- LOOP ON THE TEN DIGITS AND MULTIPLY WI= TH THE SAME
> =A0 =A0 POSITION ON
> =A0 =A0 > virifyArray
> =A0 =A0 > =A0 =A0 =A0 =A0 FOR digit IN 1..10 LOOP
> =A0 =A0 > =A0 =A0 =A0 =A0 =A0 =A0 auxValue =3D auxValue + ( CAST(SU= BSTR(id, digit, 1) AS
> =A0 =A0 int) *
> =A0 =A0 > verifyArray[digit] );
> =A0 =A0 > =A0 =A0 =A0 =A0 END LOOP;
> =A0 =A0 >
> =A0 =A0 > =A0 =A0 =A0 =A0 -- CALCULATE THE VERIFICATION NUMBER
> =A0 =A0 > =A0 =A0 =A0 =A0 verificationNumber =3D 11 - (auxValue % 1= 1);
> =A0 =A0 >
> =A0 =A0 > =A0 =A0 =A0 =A0 -- REPLACE THE TWO DIGITS =A0VERIFICATION= NUMBER
> =A0 =A0 > =A0 =A0 =A0 =A0 IF( verificationNumber =3D 11 ) THEN
> =A0 =A0 > =A0 =A0 =A0 =A0 =A0 =A0 RETURN 0;
> =A0 =A0 > =A0 =A0 =A0 =A0 ELSEIF ( verificationNumber =3D 10 ) THEN=
> =A0 =A0 > =A0 =A0 =A0 =A0 =A0 =A0 RETURN 9;
> =A0 =A0 > =A0 =A0 =A0 =A0 ELSE
> =A0 =A0 > =A0 =A0 =A0 =A0 =A0 =A0 RETURN verificationNumber;
> =A0 =A0 > =A0 =A0 =A0 =A0 END IF;
> =A0 =A0 >
> =A0 =A0 > =A0 =A0 END IF;
> =A0 =A0 >
> =A0 =A0 > END;
> =A0 =A0 > $$ LANGUAGE plpgsql;
> =A0 =A0 >
> =A0 =A0 > -- ----------------------------
> =A0 =A0 > -- INSERTs to probe the functions
> =A0 =A0 > -- ----------------------------
> =A0 =A0 > INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 1&= #39;);
> =A0 =A0 > INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 2&= #39;);
> =A0 =A0 > INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 3&= #39;);
> =A0 =A0 > INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 4&= #39;);
> =A0 =A0 > INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 5&= #39;);
> =A0 =A0 > INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 6&= #39;);
> =A0 =A0 > INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 7&= #39;);
> =A0 =A0 > INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 8&= #39;);
> =A0 =A0 > INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 9&= #39;);
> =A0 =A0 > INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 10= ');
> =A0 =A0 >
> =A0 =A0 >
> =A0 =A0 > =A0 =A0 =A0 It is my first post so sorry about the format= of the SQL
> =A0 =A0 Dump and
> =A0 =A0 > sorry about my english.
> =A0 =A0 > =A0 =A0 =A0 Note: there is a second function that calcula= te a verification
> =A0 =A0 > number like an argentinian code called CUIL (only availab= le for 10
> =A0 =A0 > digits numbers)
> =A0 =A0 > =A0 =A0 =A0 Regards....
> =A0 =A0 >
> =A0 =A0 > =A0 =A0 =A0 =A0 =A0 =A0 Nahuel Alejandro Ramos.
> =A0 =A0 >
>
> =A0 =A0 You didn't like UUID?
>
>

--0016364c67233773220476638200--