From: Nahuel Alejandro Ramos <nahuelon@gmail.com>
To: pgsql-sql@postgresql.org
Subject: Random Unique Id
Date: Tue, 20 Oct 2009 16:49:17 -0300
Message-ID: <80df75370910201249k40e308dbh668872e0e2ed2419@mail.gmail.com> (raw)
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.
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-sql@postgresql.org
Cc: nahuelon@gmail.com
Subject: Re: Random Unique Id
In-Reply-To: <80df75370910201249k40e308dbh668872e0e2ed2419@mail.gmail.com>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox