Received: from maia.hub.org (unknown [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 8330163338F for ; Tue, 20 Oct 2009 16:49:27 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 96512-05 for ; Tue, 20 Oct 2009 19:49:19 +0000 (UTC) X-Greylist: domain auto-whitelisted by SQLgrey-1.7.6 Received: from mail-qy0-f203.google.com (mail-qy0-f203.google.com [209.85.221.203]) by mail.postgresql.org (Postfix) with ESMTP id 307F663281A for ; Tue, 20 Oct 2009 16:49:19 -0300 (ADT) Received: by qyk42 with SMTP id 42so4719549qyk.28 for ; Tue, 20 Oct 2009 12:49:18 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:mime-version:received:date:message-id:subject :from:to:content-type; bh=4Qyc+YvUG1GXfcdjwYOzW9dRJ47hf1Rx5sDBZ+O9iKQ=; b=sv7OjWEYs2B9lT2uvCMPjY3z1w+pPMVFbmnISdky1k7bkJSd1gwJxHmrKrovs1t0bw 97JjR99O721NyqMZBO1xGCSnqSuJip0nFtDMeloF8vrNTUPKjYc9UP2NrSq+4nbjd9wE oZOtXh35W/xBWYAeO5WbeTBNbiH2TI3B/zLfE= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=mime-version:date:message-id:subject:from:to:content-type; b=ARFHnm0XfnxRCM0rzEZYrJOdEC2i3TTQcHjvcFeMLYDhjBPh+0Z3Pv0w/27EgkWsn6 SxSWNO7r8Npn6250xwoRalSEV5mXuA3BtQF1DktUAtgmDtHVyqvhTccZN5LcQYPawClg 9NKlbUVVZ8fiTI9HoUyjLcoGGSLmlsMoAZp4Y= MIME-Version: 1.0 Received: by 10.229.31.147 with SMTP id y19mr1047329qcc.5.1256068157911; Tue, 20 Oct 2009 12:49:17 -0700 (PDT) Date: Tue, 20 Oct 2009 16:49:17 -0300 Message-ID: <80df75370910201249k40e308dbh668872e0e2ed2419@mail.gmail.com> Subject: Random Unique Id From: Nahuel Alejandro Ramos To: pgsql-sql@postgresql.org Content-Type: multipart/alternative; boundary=0016364c67235db24f04766328d3 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.739 tagged_above=-10 required=5 tests=BAYES_20=-0.74, HTML_MESSAGE=0.001 X-Spam-Level: X-Archive-Number: 200910/78 X-Sequence-Number: 33277 --0016364c67235db24f04766328d3 Content-Type: text/plain; charset=ISO-8859-1 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. --0016364c67235db24f04766328d3 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable Hi all,
=A0 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.
=A0 I hav= e not found a solution so I have done it by myself. I would like to share i= t so here it is:
=A0
-- ----------------------------
-- Create language "plpgsql= "
-- ----------------------------
CREATE LANGUAGE plpgsql;
-- ----------------------------
-- Table structure for "public&qu= ot;."tarjeta"
-- ----------------------------
drop table "public"."tarj= eta";
CREATE TABLE "public"."tarjeta"(
"= ;idtarjeta" int8 NOT NULL DEFAULT randomuniqueidtarjeta(),
"fe= chaemision" timestamp NOT NULL DEFAULT now(),
"descripcion" varchar(255) ,
PRIMARY KEY ("idtarjeta"= ;)
)=A0 WITHOUT OIDS;

-- ----------------------------
-- Defin= ition of function=A0 "randomuniqueidtarjeta"
-- --------------= --------------

CREATE OR REPLACE FUNCTION randomUniqueIdTarjeta() RETURNS bigint AS $$=

DECLARE

=A0=A0=A0 -- SET THE KEY SIZE (IN CHARACTERS)
=A0= =A0=A0 idSize constant=A0 integer :=3D 10;

=A0=A0=A0 sizeMultiplicat= or constant bigint :=3D 10 ^ idSize;

=A0=A0=A0 loopLimit=A0 bigint :=3D sizeMultiplicator * 4;
=A0=A0=A0 = randomNumber bigint;
=A0=A0=A0 canIUseIt boolean :=3D false;

BEGI= N

=A0=A0=A0 -- LOOP UNTIL FIND AN UNIQUE RANDOM NUMBER OR FILL THE L= OOP LIMIT
=A0=A0=A0 WHILE ( not ( canIUseIt ) and ( loopLimit > 0) ) = LOOP

=A0=A0=A0 =A0=A0=A0 -- CALCULATE A TEN DIGITS RANDOM NUMBER
=A0=A0= =A0 =A0=A0=A0 randomNumber :=3D CAST ( ( random() * sizeMultiplicator ) AS = bigint );

=A0=A0=A0 =A0=A0=A0 -- VALIDATE THAT THE NUMBER WON'T = START WITH 0
=A0=A0=A0 =A0=A0=A0 IF ( (randomNumber >=3D sizeMultipli= cator / 10 ) and ( randomNumber < sizeMultiplicator ) ) THEN

=A0=A0=A0 =A0=A0=A0 =A0=A0=A0 -- CHECK THAN RANDOM NUMBER NOT EXISTS ON= TABLE
=A0=A0=A0 =A0=A0=A0 =A0=A0=A0 -- IMPORTANT!!! CHANGE WITH TABLE A= ND FIELD TO SEARCH FOR A DUPLICATATION
=A0=A0=A0 =A0=A0=A0 =A0=A0=A0 PER= FORM idtarjeta FROM tarjeta WHERE idtarjeta =3D randomNumber;
=A0=A0=A0 =A0=A0=A0 =A0=A0=A0 IF NOT FOUND THEN
=A0=A0=A0 =A0=A0=A0 =A0= =A0=A0 =A0=A0=A0 canIUseIt =3D true;
=A0=A0=A0 =A0=A0=A0 =A0=A0=A0 END I= F;

=A0=A0=A0 =A0=A0=A0 END IF;

=A0=A0=A0 =A0=A0=A0 loopLimit = =3D loopLimit - 1;

=A0=A0=A0 END LOOP;

=A0=A0=A0 -- IF LOOP E= NDS WITH A UNIQUE RANDOM NUMBER RETURNS THE NUMBER CONCATENATED WITH A VERI= FICATION NUMBER=A0 ELSE RAISE AN EXCEPTION
=A0=A0=A0 IF ( canIUseIt ) THEN
=A0=A0=A0 =A0=A0=A0 RETURN CAST ( ( CAST= ( randomNumber AS text ) || CAST ( verificationNumber( CAST (randomNumber = AS text ) ) AS text=A0 ) ) AS=A0 bigint )=A0 ;
=A0=A0=A0 ELSE
=A0=A0= =A0 =A0=A0=A0 RAISE EXCEPTION 'Could not calculate a Random Unique ID o= n table Tarjeta.';
=A0=A0=A0 END IF;

END;
$$ LANGUAGE plpgsql;

-- -----------= -----------------
-- Definition of function=A0 "randomuniqueidtarje= ta"
-- ----------------------------

CREATE OR REPLACE FUNCTI= ON verificationNumber(id text) RETURNS integer AS $$
DECLARE

=A0=A0=A0 auxValue integer :=3D 0;
=A0=A0=A0 verifyArray = constant integer[] :=3DARRAY [5,4,3,2,7,6,5,4,3,2,1];
=A0=A0=A0 verifica= tionNumber integer :=3D 0;

BEGIN

=A0=A0=A0 -- ASK FOR ID LENG= TH EQUAL TO 10 DIGITS. THE ALGORITHM WORKS ONLY FOR
=A0=A0=A0 IF ( LENGTH( id ) <> 10 ) THEN

=A0=A0=A0 =A0=A0=A0 R= AISE EXCEPTION 'Could not calculate a verification number. The ID must = have 10 digits.';

=A0=A0=A0 ELSE

=A0=A0=A0 =A0=A0=A0 -- L= OOP ON THE TEN DIGITS AND MULTIPLY WITH THE SAME POSITION ON virifyArray =A0=A0=A0 =A0=A0=A0 FOR digit IN 1..10 LOOP
=A0=A0=A0 =A0=A0=A0 =A0=A0= =A0 auxValue =3D auxValue + ( CAST(SUBSTR(id, digit, 1) AS int) * verifyArr= ay[digit] );
=A0=A0=A0 =A0=A0=A0 END LOOP;

=A0=A0=A0 =A0=A0=A0 --= CALCULATE THE VERIFICATION NUMBER
=A0=A0=A0 =A0=A0=A0 verificationNumbe= r =3D 11 - (auxValue % 11);

=A0=A0=A0 =A0=A0=A0 -- REPLACE THE TWO DIGITS=A0 VERIFICATION NUMBER=A0=A0=A0 =A0=A0=A0 IF( verificationNumber =3D 11 ) THEN
=A0=A0=A0 =A0= =A0=A0 =A0=A0=A0 RETURN 0;
=A0=A0=A0 =A0=A0=A0 ELSEIF ( verificationNumb= er =3D 10 ) THEN
=A0=A0=A0 =A0=A0=A0 =A0=A0=A0 RETURN 9;
=A0=A0=A0 = =A0=A0=A0 ELSE
=A0=A0=A0 =A0=A0=A0 =A0=A0=A0 RETURN verificationNumber;
=A0=A0=A0 =A0= =A0=A0 END IF;

=A0=A0=A0 END IF;

END;
$$ LANGUAGE plpgsql;=

-- ----------------------------
-- INSERTs to probe the function= s
-- ----------------------------
INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 1');
INSERT I= NTO tarjeta (descripcion) VALUES ('Tarjeta 2');
INSERT INTO tarj= eta (descripcion) VALUES ('Tarjeta 3');
INSERT INTO tarjeta (des= cripcion) VALUES ('Tarjeta 4');
INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 5');
INSERT I= NTO tarjeta (descripcion) VALUES ('Tarjeta 6');
INSERT INTO tarj= eta (descripcion) VALUES ('Tarjeta 7');
INSERT INTO tarjeta (des= cripcion) VALUES ('Tarjeta 8');
INSERT INTO tarjeta (descripcion) VALUES ('Tarjeta 9');
INSERT I= NTO tarjeta (descripcion) VALUES ('Tarjeta 10');


=A0=A0= =A0=A0=A0 It is my first post so sorry about the format of the SQL Dump and= sorry about my english.
=A0=A0=A0=A0=A0 Note: there is a second function that calculate a verificat= ion number like an argentinian code called CUIL (only available for 10 digi= ts numbers)
=A0=A0=A0=A0=A0 Regards....

=A0=A0=A0=A0=A0=A0=A0=A0= =A0=A0=A0 Nahuel Alejandro Ramos.

--0016364c67235db24f04766328d3--