Re: Random Unique Id - Mailing list pgsql-sql

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?
>
>