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.
