Received: from localhost (unknown [200.46.208.211]) by mail.postgresql.org (Postfix) with ESMTP id 0F80563281A for ; Tue, 20 Oct 2009 16:57:17 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by localhost (mx1.hub.org [200.46.208.211]) (amavisd-maia, port 10024) with ESMTP id 31164-06 for ; Tue, 20 Oct 2009 19:57:02 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-qy0-f203.google.com (mail-qy0-f203.google.com [209.85.221.203]) by mail.postgresql.org (Postfix) with ESMTP id 8BC1F6335D6 for ; Tue, 20 Oct 2009 16:57:05 -0300 (ADT) Received: by qyk42 with SMTP id 42so4722654qyk.28 for ; Tue, 20 Oct 2009 12:57:04 -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=4IclLw1KQZ1dQ5GWQX11MlkyDu936wg3YrYKx3O+lUU=; b=XFv38lzk5PlxMmWw6Ec0N00vKDAONPiyFeyafjqa73o37BNnSQ0CBkvISVWAFtxapQ 3cC2E5D+YoxKiX7h2mWUvFJlCb4G5QZOobnnNhg6egtklhgEoV37WqZ+h2NRY1VT199K cR5SWmjNeLCpv+tVIty8ZBWriit20DUfwsdNM= 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=OzmYjkbGYX8AEG34TA/dMqxD0YFTakSNNLlmFYTInTDKZN+KlXJ2BiAtAcIHkBHylF rY4ORvjlcGJDZ4HezJJg5/qLEpwJnudCmRfjbDmQlX9bcgBYnVGXtuMSxHaipTm7gnpK a3TGCcwcuIYDMA9OhVOstaJnxReT5i3RW4KCY= MIME-Version: 1.0 Received: by 10.229.111.85 with SMTP id r21mr931321qcp.88.1256068624248; Tue, 20 Oct 2009 12:57:04 -0700 (PDT) In-Reply-To: <4ADE1520.5080203@gmail.com> References: <80df75370910201249k40e308dbh668872e0e2ed2419@mail.gmail.com> <4ADE1520.5080203@gmail.com> Date: Tue, 20 Oct 2009 16:57:04 -0300 Message-ID: <80df75370910201257m2d64ad7tad0636f9b664bdeb@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=0023544710142972520476634423 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.234 tagged_above=-10 required=5 tests=AWL=1.343, BAYES_05=-1.11, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200910/80 X-Sequence-Number: 33279 --0023544710142972520476634423 Content-Type: text/plain; charset=ISO-8859-1 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? > --0023544710142972520476634423 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable 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 c= redit number randomly (like the example I post).
I used to insert an MD5= field but this time I need "only numbers" Id.
Regards...

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


On Tue, Oct 20, 2009 at 4:53 PM, Rob Sargent <robjsargent@gmail.com= > wrote:
<= div class=3D"h5">

Nahuel Alejandro Ramos wrote:
> Hi all,
> =A0 I was searching for a sequence (for serials) that let me use a ran= dom
> unique number ID on a Primary Key or a simple index.
> =A0 I have not found a solution so I have done it by myself. I would l= ike
> 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")
> ) =A0WITHOUT OIDS;
>
> -- ----------------------------
> -- Definition of function =A0"randomuniqueidtarjeta"
> -- ----------------------------
>
> CREATE OR REPLACE FUNCTION randomUniqueIdTarjeta() RETURNS bigint AS $= $
>
> DECLARE
>
> =A0 =A0 -- SET THE KEY SIZE (IN CHARACTERS)
> =A0 =A0 idSize constant =A0integer :=3D 10;
>
> =A0 =A0 sizeMultiplicator constant bigint :=3D 10 ^ idSize;
>
> =A0 =A0 loopLimit =A0bigint :=3D sizeMultiplicator * 4;
> =A0 =A0 randomNumber bigint;
> =A0 =A0 canIUseIt boolean :=3D false;
>
> BEGIN
>
> =A0 =A0 -- LOOP UNTIL FIND AN UNIQUE RANDOM NUMBER OR FILL THE LOOP LI= MIT
> =A0 =A0 WHILE ( not ( canIUseIt ) and ( loopLimit > 0) ) LOOP
>
> =A0 =A0 =A0 =A0 -- CALCULATE A TEN DIGITS RANDOM NUMBER
> =A0 =A0 =A0 =A0 randomNumber :=3D CAST ( ( random() * sizeMultiplicato= r ) AS bigint );
>
> =A0 =A0 =A0 =A0 -- VALIDATE THAT THE NUMBER WON'T START WITH 0
> =A0 =A0 =A0 =A0 IF ( (randomNumber >=3D sizeMultiplicator / 10 ) an= d (
> randomNumber < sizeMultiplicator ) ) THEN
>
> =A0 =A0 =A0 =A0 =A0 =A0 -- CHECK THAN RANDOM NUMBER NOT EXISTS ON TABL= E
> =A0 =A0 =A0 =A0 =A0 =A0 -- IMPORTANT!!! CHANGE WITH TABLE AND FIELD TO= SEARCH FOR A
> DUPLICATATION
> =A0 =A0 =A0 =A0 =A0 =A0 PERFORM idtarjeta FROM tarjeta WHERE idtarjeta= =3D randomNumber;
> =A0 =A0 =A0 =A0 =A0 =A0 IF NOT FOUND THEN
> =A0 =A0 =A0 =A0 =A0 =A0 =A0 =A0 canIUseIt =3D true;
> =A0 =A0 =A0 =A0 =A0 =A0 END IF;
>
> =A0 =A0 =A0 =A0 END IF;
>
> =A0 =A0 =A0 =A0 loopLimit =3D loopLimit - 1;
>
> =A0 =A0 END LOOP;
>
> =A0 =A0 -- IF LOOP ENDS WITH A UNIQUE RANDOM NUMBER RETURNS THE NUMBER=
> CONCATENATED WITH A VERIFICATION NUMBER =A0ELSE RAISE AN EXCEPTION
> =A0 =A0 IF ( canIUseIt ) THEN
> =A0 =A0 =A0 =A0 RETURN CAST ( ( CAST ( randomNumber AS text ) || CAST = (
> verificationNumber( CAST (randomNumber AS text ) ) AS text =A0) ) AS > bigint ) =A0;
> =A0 =A0 ELSE
> =A0 =A0 =A0 =A0 RAISE EXCEPTION 'Could not calculate a Random Uniq= ue ID on table
> Tarjeta.';
> =A0 =A0 END IF;
>
> END;
> $$ LANGUAGE plpgsql;
>
> -- ----------------------------
> -- Definition of function =A0"randomuniqueidtarjeta"
> -- ----------------------------
>
> CREATE OR REPLACE FUNCTION verificationNumber(id text) RETURNS integer= AS $$
> DECLARE
>
> =A0 =A0 auxValue integer :=3D 0;
> =A0 =A0 verifyArray constant integer[] :=3DARRAY [5,4,3,2,7,6,5,4,3,2,= 1];
> =A0 =A0 verificationNumber integer :=3D 0;
>
> BEGIN
>
> =A0 =A0 -- ASK FOR ID LENGTH EQUAL TO 10 DIGITS. THE ALGORITHM WORKS O= NLY FOR
> =A0 =A0 IF ( LENGTH( id ) <> 10 ) THEN
>
> =A0 =A0 =A0 =A0 RAISE EXCEPTION 'Could not calculate a verificatio= n number. The
> ID must have 10 digits.';
>
> =A0 =A0 ELSE
>
> =A0 =A0 =A0 =A0 -- LOOP ON THE TEN DIGITS AND MULTIPLY WITH THE SAME P= OSITION ON
> virifyArray
> =A0 =A0 =A0 =A0 FOR digit IN 1..10 LOOP
> =A0 =A0 =A0 =A0 =A0 =A0 auxValue =3D auxValue + ( CAST(SUBSTR(id, digi= t, 1) AS int) *
> verifyArray[digit] );
> =A0 =A0 =A0 =A0 END LOOP;
>
> =A0 =A0 =A0 =A0 -- CALCULATE THE VERIFICATION NUMBER
> =A0 =A0 =A0 =A0 verificationNumber =3D 11 - (auxValue % 11);
>
> =A0 =A0 =A0 =A0 -- REPLACE THE TWO DIGITS =A0VERIFICATION NUMBER
> =A0 =A0 =A0 =A0 IF( verificationNumber =3D 11 ) THEN
> =A0 =A0 =A0 =A0 =A0 =A0 RETURN 0;
> =A0 =A0 =A0 =A0 ELSEIF ( verificationNumber =3D 10 ) THEN
> =A0 =A0 =A0 =A0 =A0 =A0 RETURN 9;
> =A0 =A0 =A0 =A0 ELSE
> =A0 =A0 =A0 =A0 =A0 =A0 RETURN verificationNumber;
> =A0 =A0 =A0 =A0 END IF;
>
> =A0 =A0 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');
>
>
> =A0 =A0 =A0 It is my first post so sorry about the format of the SQL D= ump and
> sorry about my english.
> =A0 =A0 =A0 Note: there is a second function that calculate a verifica= tion
> number like an argentinian code called CUIL (only available for 10
> digits numbers)
> =A0 =A0 =A0 Regards....
>
> =A0 =A0 =A0 =A0 =A0 =A0 Nahuel Alejandro Ramos.
>

You didn't like UUID?

--0023544710142972520476634423--