Random Unique Id

From: Nahuel Alejandro Ramos <nahuelon(at)gmail(dot)com>
To: pgsql-sql(at)postgresql(dot)org
Subject: Random Unique Id
Date: 2009-10-20 19:49:17
Message-ID: 80df75370910201249k40e308dbh668872e0e2ed2419@mail.gmail.com
Views: Whole Thread | Raw Message | Download mbox | Resend email
Thread:
Lists: pgsql-sql

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.

Responses

Browse pgsql-sql by date

  From Date Subject
Next Message Rob Sargent 2009-10-20 19:53:04 Re: Random Unique Id
Previous Message Oliveiros C, 2009-10-20 15:54:47 Re: How to order varchar data by word