Random Unique Id - Mailing list pgsql-sql

Hi all,<br />  I was searching for a sequence (for serials) that let me use a random unique number ID on a Primary Key
ora simple index.<br />  I have not found a solution so I have done it by myself. I would like to share it so here it
is:<br/>   <br />-- ----------------------------<br />-- Create language "plpgsql"<br />--
----------------------------<br/>CREATE LANGUAGE plpgsql;<br /><br />-- ----------------------------<br />-- Table
structurefor "public"."tarjeta"<br /> -- ----------------------------<br />drop table "public"."tarjeta";<br />CREATE
TABLE"public"."tarjeta"(<br />"idtarjeta" int8 NOT NULL DEFAULT randomuniqueidtarjeta(),<br />"fechaemision" timestamp
NOTNULL DEFAULT now(),<br /> "descripcion" varchar(255) ,<br />PRIMARY KEY ("idtarjeta")<br />)  WITHOUT OIDS;<br /><br
/>------------------------------<br />-- Definition of function  "randomuniqueidtarjeta"<br />--
----------------------------<br/><br />CREATE OR REPLACE FUNCTION randomUniqueIdTarjeta() RETURNS bigint AS $$<br /><br
/>DECLARE<br/><br />    -- SET THE KEY SIZE (IN CHARACTERS)<br />    idSize constant  integer := 10;<br /><br />   
sizeMultiplicatorconstant bigint := 10 ^ idSize;<br /><br />    loopLimit  bigint := sizeMultiplicator * 4;<br />   
randomNumberbigint;<br />    canIUseIt boolean := false;<br /><br />BEGIN<br /><br />    -- LOOP UNTIL FIND AN UNIQUE
RANDOMNUMBER OR FILL THE LOOP LIMIT<br />    WHILE ( not ( canIUseIt ) and ( loopLimit > 0) ) LOOP<br /><br />   
   -- CALCULATE A TEN DIGITS RANDOM NUMBER<br />        randomNumber := CAST ( ( random() * sizeMultiplicator ) AS
bigint);<br /><br />        -- VALIDATE THAT THE NUMBER WON'T START WITH 0<br />        IF ( (randomNumber >=
sizeMultiplicator/ 10 ) and ( randomNumber < sizeMultiplicator ) ) THEN<br /><br />            -- CHECK THAN RANDOM
NUMBERNOT EXISTS ON TABLE<br />            -- IMPORTANT!!! CHANGE WITH TABLE AND FIELD TO SEARCH FOR A DUPLICATATION<br
/>           PERFORM idtarjeta FROM tarjeta WHERE idtarjeta = randomNumber;<br />             IF NOT FOUND THEN<br
/>               canIUseIt = true;<br />            END IF;<br /><br />        END IF;<br /><br />        loopLimit =
loopLimit- 1;<br /><br />    END LOOP;<br /><br />    -- IF LOOP ENDS WITH A UNIQUE RANDOM NUMBER RETURNS THE NUMBER
CONCATENATEDWITH A VERIFICATION NUMBER  ELSE RAISE AN EXCEPTION<br />     IF ( canIUseIt ) THEN<br />        RETURN
CAST( ( CAST ( randomNumber AS text ) || CAST ( verificationNumber( CAST (randomNumber AS text ) ) AS text  ) ) AS 
bigint)  ;<br />    ELSE<br />        RAISE EXCEPTION 'Could not calculate a Random Unique ID on table Tarjeta.';<br />
   END IF;<br /><br />END;<br />$$ LANGUAGE plpgsql;<br /><br />-- ----------------------------<br />-- Definition of
function "randomuniqueidtarjeta"<br />-- ----------------------------<br /><br />CREATE OR REPLACE FUNCTION
verificationNumber(idtext) RETURNS integer AS $$<br /> DECLARE<br /><br />    auxValue integer := 0;<br />   
verifyArrayconstant integer[] :=ARRAY [5,4,3,2,7,6,5,4,3,2,1];<br />    verificationNumber integer := 0;<br /><br
/>BEGIN<br/><br />    -- ASK FOR ID LENGTH EQUAL TO 10 DIGITS. THE ALGORITHM WORKS ONLY FOR <br />     IF ( LENGTH( id
)<> 10 ) THEN<br /><br />        RAISE EXCEPTION 'Could not calculate a verification number. The ID must have 10
digits.';<br/><br />    ELSE<br /><br />        -- LOOP ON THE TEN DIGITS AND MULTIPLY WITH THE SAME POSITION ON
virifyArray<br/>         FOR digit IN 1..10 LOOP<br />            auxValue = auxValue + ( CAST(SUBSTR(id, digit, 1) AS
int)* verifyArray[digit] );<br />        END LOOP;<br /><br />        -- CALCULATE THE VERIFICATION NUMBER<br />       
verificationNumber= 11 - (auxValue % 11);<br /><br />        -- REPLACE THE TWO DIGITS  VERIFICATION NUMBER<br />   
   IF( verificationNumber = 11 ) THEN<br />            RETURN 0;<br />        ELSEIF ( verificationNumber = 10 )
THEN<br/>            RETURN 9;<br />        ELSE<br />             RETURN verificationNumber;<br />        END IF;<br
/><br/>    END IF;<br /><br />END;<br />$$ LANGUAGE plpgsql;<br /><br />-- ----------------------------<br />-- INSERTs
toprobe the functions<br />-- ----------------------------<br /> INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta
1');<br/>INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 2');<br />INSERT INTO tarjeta (descripcion) VALUES
('Tarjeta3');<br />INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 4');<br /> INSERT INTO tarjeta (descripcion)
VALUES('Tarjeta 5');<br />INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 6');<br />INSERT INTO tarjeta
(descripcion)VALUES ('Tarjeta 7');<br />INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 8');<br /> INSERT INTO
tarjeta(descripcion) VALUES ('Tarjeta 9');<br />INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 10');<br /><br /><br
/>     It is my first post so sorry about the format of the SQL Dump and sorry about my english.<br />       Note:
thereis a second function that calculate a verification number like an argentinian code called CUIL (only available for
10digits numbers)<br />      Regards....<br /><br />            Nahuel Alejandro Ramos.<br /><br />