From: Nahuel Alejandro Ramos <nahuelon@gmail.com>
To: Rob Sargent <robjsargent@gmail.com>
Cc: pgsql-sql@postgresql.org
Subject: Re: Random Unique Id
Date: Tue, 20 Oct 2009 17:14:25 -0300
Message-ID: <80df75370910201314x6b267a03wc04cb164c75af781@mail.gmail.com> (raw)
In-Reply-To: <4ADE17A2.6010003@gmail.com>
References: <80df75370910201249k40e308dbh668872e0e2ed2419@mail.gmail.com>
<4ADE1520.5080203@gmail.com>
<80df75370910201257m2d64ad7tad0636f9b664bdeb@mail.gmail.com>
<4ADE17A2.6010003@gmail.com>
:D, Yeah, i know. Its only an example. We are using this algorithm to give a
random unique Id to our clients. The need was to give a PK absolute
independent of time. Thanks to Ivan, for the pseudo-random posted, I am
looking it.
Regards...
Nahuel Alejandro Ramos.
On Tue, Oct 20, 2009 at 5:03 PM, Rob Sargent <robjsargent@gmail.com> wrote:
> Suit yourself, of course, but the numbers on my credit cards are far,
> far from random :)
>
>
> Nahuel Alejandro Ramos wrote:
> > Yes. I looked this solution but it is not a "only numbers" ID. I would
> > like a random unique "number" Id. For example: generate a credit number
> > randomly (like the example I post).
> > I used to insert an MD5 field but this time I need "only numbers" Id.
> > Regards...
> >
> > Nahuel Alejandro Ramos.
> >
> >
> > On Tue, Oct 20, 2009 at 4:53 PM, Rob Sargent <robjsargent@gmail.com
> > <mailto:robjsargent@gmail.com>> wrote:
> >
> >
> >
> > Nahuel Alejandro Ramos wrote:
> > > 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.
> > >
> >
> > You didn't like UUID?
> >
> >
>
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, robjsargent@gmail.com
Subject: Re: Random Unique Id
In-Reply-To: <80df75370910201314x6b267a03wc04cb164c75af781@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