DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma;
	h=message-id:date:from:user-agent:mime-version:to:cc:subject
	:references:in-reply-to:content-type:content-transfer-encoding;
	b=LnH+w5p27tZ/JZbtFZejK7XrNusQVu85WVCMTg5hGmI+8sehKl7S8hU2ulvSToisU2
	VLLREtzPrQ+YGA94p3IyyLtaXBChTVLbvD9lREHgV+IjUIS7iRaHOcscEBhHJW15LtFk
	tQsCKvdyAmKeT+Qa8XUj47Ri+x45nK4/TfIHM=
Message-ID: <4ADE17A2.6010003@gmail.com>
Date: Tue, 20 Oct 2009 14:03:46 -0600
From: Rob Sargent <robjsargent@gmail.com>
User-Agent: Thunderbird 2.0.0.23 (X11/20090817)
MIME-Version: 1.0
To: Nahuel Alejandro Ramos <nahuelon@gmail.com>
CC: pgsql-sql@postgresql.org
Subject: Re: Random Unique Id
References: <80df75370910201249k40e308dbh668872e0e2ed2419@mail.gmail.com>	
	<4ADE1520.5080203@gmail.com>
	<80df75370910201257m2d64ad7tad0636f9b664bdeb@mail.gmail.com>
In-Reply-To: <80df75370910201257m2d64ad7tad0636f9b664bdeb@mail.gmail.com>
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit

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 <robjsargent@gmail.com
> <mailto:robjsargent@gmail.com>> 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?
> 
>