pg.ddx.io pgsql-sql@postgresql.org mailing list archive
help / color / mirror / Atom feedRandom Unique Id
7+ messages / 4 participants
[nested] [flat]
* Random Unique Id
@ 2009-10-20 19:49 Nahuel Alejandro Ramos <nahuelon@gmail.com>
2009-10-20 19:53 ` Re: Random Unique Id Rob Sargent <robjsargent@gmail.com>
2009-10-20 20:30 ` Re: Random Unique Id Ivan Sergio Borgonovo <mail@webthatworks.it>
2009-10-20 23:51 ` Re: Random Unique Id Craig Ringer <craig@postnewspapers.com.au>
0 siblings, 3 replies; 7+ messages in thread
From: Nahuel Alejandro Ramos @ 2009-10-20 19:49 UTC (permalink / raw)
To: pgsql-sql
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.
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Random Unique Id
2009-10-20 19:49 Random Unique Id Nahuel Alejandro Ramos <nahuelon@gmail.com>
@ 2009-10-20 19:53 ` Rob Sargent <robjsargent@gmail.com>
2009-10-20 19:57 ` Re: Random Unique Id Nahuel Alejandro Ramos <nahuelon@gmail.com>
2 siblings, 1 reply; 7+ messages in thread
From: Rob Sargent @ 2009-10-20 19:53 UTC (permalink / raw)
To: Nahuel Alejandro Ramos <nahuelon@gmail.com>; +Cc: pgsql-sql
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?
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Random Unique Id
2009-10-20 19:49 Random Unique Id Nahuel Alejandro Ramos <nahuelon@gmail.com>
2009-10-20 19:53 ` Re: Random Unique Id Rob Sargent <robjsargent@gmail.com>
@ 2009-10-20 19:57 ` Nahuel Alejandro Ramos <nahuelon@gmail.com>
2009-10-20 20:03 ` Re: Random Unique Id Rob Sargent <robjsargent@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Nahuel Alejandro Ramos @ 2009-10-20 19:57 UTC (permalink / raw)
To: Rob Sargent <robjsargent@gmail.com>; +Cc: pgsql-sql
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?
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Random Unique Id
2009-10-20 19:49 Random Unique Id Nahuel Alejandro Ramos <nahuelon@gmail.com>
2009-10-20 19:53 ` Re: Random Unique Id Rob Sargent <robjsargent@gmail.com>
2009-10-20 19:57 ` Re: Random Unique Id Nahuel Alejandro Ramos <nahuelon@gmail.com>
@ 2009-10-20 20:03 ` Rob Sargent <robjsargent@gmail.com>
2009-10-20 20:14 ` Re: Random Unique Id Nahuel Alejandro Ramos <nahuelon@gmail.com>
0 siblings, 1 reply; 7+ messages in thread
From: Rob Sargent @ 2009-10-20 20:03 UTC (permalink / raw)
To: Nahuel Alejandro Ramos <nahuelon@gmail.com>; +Cc: pgsql-sql
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?
>
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Random Unique Id
2009-10-20 19:49 Random Unique Id Nahuel Alejandro Ramos <nahuelon@gmail.com>
2009-10-20 19:53 ` Re: Random Unique Id Rob Sargent <robjsargent@gmail.com>
2009-10-20 19:57 ` Re: Random Unique Id Nahuel Alejandro Ramos <nahuelon@gmail.com>
2009-10-20 20:03 ` Re: Random Unique Id Rob Sargent <robjsargent@gmail.com>
@ 2009-10-20 20:14 ` Nahuel Alejandro Ramos <nahuelon@gmail.com>
0 siblings, 0 replies; 7+ messages in thread
From: Nahuel Alejandro Ramos @ 2009-10-20 20:14 UTC (permalink / raw)
To: Rob Sargent <robjsargent@gmail.com>; +Cc: pgsql-sql
: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?
> >
> >
>
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Random Unique Id
2009-10-20 19:49 Random Unique Id Nahuel Alejandro Ramos <nahuelon@gmail.com>
@ 2009-10-20 20:30 ` Ivan Sergio Borgonovo <mail@webthatworks.it>
2 siblings, 0 replies; 7+ messages in thread
From: Ivan Sergio Borgonovo @ 2009-10-20 20:30 UTC (permalink / raw)
To: pgsql-sql
On Tue, 20 Oct 2009 16:49:17 -0300
Nahuel Alejandro Ramos <nahuelon@gmail.com> 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:
http://www.webthatworks.it/d1/node/page/pseudo_random_sequences_postgresql
Adapted from Daniel Verite suggestion.
--
Ivan Sergio Borgonovo
http://www.webthatworks.it
^ permalink raw reply [nested|flat] 7+ messages in thread
* Re: Random Unique Id
2009-10-20 19:49 Random Unique Id Nahuel Alejandro Ramos <nahuelon@gmail.com>
@ 2009-10-20 23:51 ` Craig Ringer <craig@postnewspapers.com.au>
2 siblings, 0 replies; 7+ messages in thread
From: Craig Ringer @ 2009-10-20 23:51 UTC (permalink / raw)
To: Nahuel Alejandro Ramos <nahuelon@gmail.com>; +Cc: pgsql-sql
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:
Here's what I'm using:
http://wiki.postgresql.org/wiki/Pseudo_encrypt
thanks to the incredibly helpful folks on this list, in this case
particularly Daniel Verite.
--
Craig Ringer
^ permalink raw reply [nested|flat] 7+ messages in thread
end of thread, other threads:[~2009-10-20 23:51 UTC | newest]
Thread overview: 7+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2009-10-20 19:49 Random Unique Id Nahuel Alejandro Ramos <nahuelon@gmail.com>
2009-10-20 19:53 ` Rob Sargent <robjsargent@gmail.com>
2009-10-20 19:57 ` Nahuel Alejandro Ramos <nahuelon@gmail.com>
2009-10-20 20:03 ` Rob Sargent <robjsargent@gmail.com>
2009-10-20 20:14 ` Nahuel Alejandro Ramos <nahuelon@gmail.com>
2009-10-20 20:30 ` Ivan Sergio Borgonovo <mail@webthatworks.it>
2009-10-20 23:51 ` Craig Ringer <craig@postnewspapers.com.au>
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