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 16:57:04 -0300
Message-ID: <80df75370910201257m2d64ad7tad0636f9b664bdeb@mail.gmail.com> (raw)
In-Reply-To: <4ADE1520.5080203@gmail.com>
References: <80df75370910201249k40e308dbh668872e0e2ed2419@mail.gmail.com>
<4ADE1520.5080203@gmail.com>
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> 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: <80df75370910201257m2d64ad7tad0636f9b664bdeb@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