Re: Random Unique Id - Mailing list pgsql-sql

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?