Postgres Pro
Downloads
Home   >   mailing lists

Thread: Random Number SKU Generator

Random Number SKU Generator

From
"Peter Dabrowski"
Date:
I use a Luhn algorithm to generete random number, inserted below sql code to column 'SKU"

 

ALTER TABLE m_product ALTER sku SET DEFAULT ltrim(to_char(luhn_generate(round(random()*10000)::int), '00000'))

 

as a result on record saving random number is generated, but my problem is the numbers are not unique.

 

Somone sugest that I  should write a function to encapsulate "ltrim(to_char(luhn_generate(

round(random()*10000)::int), '00000'))"  and at the same time, check if the value is already used in
thetable. 

 

in pseudo code it would look something like this:

 

generateMProductSKU(){
    skuGen=""
    needToGenerate = true
    while(needToGenerate){
      skuGen = ltrim(to_char(luhn_generate(round(random()*10000)::int), '00000'))
      if((Select count(*) from M_Product where sku =skuGen) = 0
         needToGenerate =  false
   }
   return skuGen
}

 

Culd somebody help me tu structure code into right sql format so it could be inserted into database.

 

Thank you very much.

Peter

Re: Random Number SKU Generator

From
Roxanne Reid-Bennett
Date:
On 2/13/2015 4:46 AM, Peter Dabrowski wrote:
I use a Luhn algorithm to generete random number, inserted below sql code to column 'SKU"
 
ALTER TABLE m_product ALTER sku SET DEFAULT ltrim(to_char(luhn_generate(round(random()*10000)::int), '00000'))
 
as a result on record saving random number is generated, but my problem is the numbers are not unique.
 
Somone sugest that I  should write a function to encapsulate "ltrim(to_char(luhn_generate(
round(random()*10000)::int), '00000'))"  and at the same time, check if the value is already used in the table.
 
in pseudo code it would look something like this:
 
generateMProductSKU(){
    skuGen=""
    needToGenerate = true
    while(needToGenerate){
      skuGen = ltrim(to_char(luhn_generate(round(random()*10000)::int), '00000'))
      if((Select count(*) from M_Product where sku =skuGen) = 0
         needToGenerate =  false
   }
   return skuGen
}
 
Culd somebody help me tu structure code into right sql format so it could be inserted into database.
 
Thank you very much.
Peter
perhaps this?

create or replace function generateMProductSKU()
RETURNS text AS
$BODY$

DECLARE
    skuGen text;
    needToGenerate boolean;
BEGIN
    skuGen := '';
    needToGenerate := true;

    WHILE needToGenerate LOOP
        skuGen := ltrim(luhn_generate(round(random()*10000)::int)::text, '00000');
        SELECT count(*) != 0 into needToGenerate from m_product where sku = skuGen;
    END LOOP;

    return skuGen;
END
$BODY$
  LANGUAGE 'plpgsql' STABLE;

ALTER TABLE m_product ALTER sku SET DEFAULT generateMProductSKU();

-- 
[At other schools] I think the most common fault in general is to teach students how to pass exams instead of teaching them the science.
Donald Knuth

Re: Random Number SKU Generator

From
"Peter Dabrowski"
Date:
Thank you Roxane,

Applied the code against db, it is generating Random SKU and I did not to have any errors to report.

So far i'ts very good.

Best Regards

Peter

 

Sent: Saturday, February 14, 2015 at 2:20 AM
From: "Roxanne Reid-Bennett" <rox@tara-lu.com>
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Random Number SKU Generator



On 2/13/2015 4:46 AM, Peter Dabrowski wrote:



I use a Luhn algorithm to generete random number, inserted below sql code to column 'SKU"

 

ALTER TABLE m_product ALTER sku SET DEFAULT ltrim(to_char(luhn_generate(round(random()*10000)::int), '00000'))

 

as a result on record saving random number is generated, but my problem is the numbers are not unique.

 

Somone sugest that I  should write a function to encapsulate "ltrim(to_char(luhn_generate(

round(random()*10000)::int), '00000'))"  and at the same time, check if the value is already used in
thetable. 

 

in pseudo code it would look something like this:

 

generateMProductSKU(){
    skuGen=""
    needToGenerate = true
    while(needToGenerate){
      skuGen = ltrim(to_char(luhn_generate(round(random()*10000)::int), '00000'))
      if((Select count(*) from M_Product where sku =skuGen) = 0
         needToGenerate =  false
   }
   return skuGen
}

 

Culd somebody help me tu structure code into right sql format so it could be inserted into database.

 

Thank you very much.

Peter



perhaps this?

create or replace function generateMProductSKU()
RETURNS text AS
$BODY$

DECLARE
    skuGen text;
    needToGenerate boolean;
BEGIN
    skuGen := '';
    needToGenerate := true;

    WHILE needToGenerate LOOP
        skuGen := ltrim(luhn_generate(round(random()*10000)::int)::text,
'00000');
        SELECT count(*) != 0 into needToGenerate from m_product where sku = skuGen;
    END LOOP;

    return skuGen;
END
$BODY$
  LANGUAGE 'plpgsql' STABLE;

ALTER TABLE m_product ALTER sku SET DEFAULT generateMProductSKU();
 
--
[At other schools] I think the most common fault in general is to teach students how to pass exams instead of teaching
themthe science. 
Donald Knuth

Re: Random Number SKU Generator

From
"Peter Dabrowski"
Date:
I found one issue,
The leading zeros are removed.  expected number "00042"  printed  "42"

Regards
Peter
 
 

Sent: Saturday, February 14, 2015 at 9:23 PM
From: "Peter Dabrowski" <meritage@mail.com>
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Random Number SKU Generator

Thank you Roxane,
Applied the code against db, it is generating Random SKU and I did not to have any errors to report.
So far i'ts very good.
Best Regards
Peter
 

Sent: Saturday, February 14, 2015 at 2:20 AM
From: "Roxanne Reid-Bennett" <rox@tara-lu.com>
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Random Number SKU Generator

On 2/13/2015 4:46 AM, Peter Dabrowski wrote:

I use a Luhn algorithm to generete random number, inserted below sql code to column 'SKU"
 
ALTER TABLE m_product ALTER sku SET DEFAULT ltrim(to_char(luhn_generate(round(random()*10000)::int), '00000'))
 
as a result on record saving random number is generated, but my problem is the numbers are not unique.
 
Somone sugest that I  should write a function to encapsulate "ltrim(to_char(luhn_generate(
round(random()*10000)::int), '00000'))"  and at the same time, check if the value is already used in the table.
 
in pseudo code it would look something like this:
 
generateMProductSKU(){
    skuGen=""
    needToGenerate = true
    while(needToGenerate){
      skuGen = ltrim(to_char(luhn_generate(round(random()*10000)::int), '00000'))
      if((Select count(*) from M_Product where sku =skuGen) = 0
         needToGenerate =  false
   }
   return skuGen
}
 
Culd somebody help me tu structure code into right sql format so it could be inserted into database.
 
Thank you very much.
Peterperhaps this?

create or replace function generateMProductSKU()
RETURNS text AS
$BODY$

DECLARE
    skuGen text;
    needToGenerate boolean;
BEGIN
    skuGen := '';
    needToGenerate := true;

    WHILE needToGenerate LOOP
        skuGen := ltrim(luhn_generate(round(random()*10000)::int)::text, '00000');
        SELECT count(*) != 0 into needToGenerate from m_product where sku = skuGen;
    END LOOP;

    return skuGen;
END
$BODY$
  LANGUAGE 'plpgsql' STABLE;

ALTER TABLE m_product ALTER sku SET DEFAULT generateMProductSKU();
 
--
[At other schools] I think the most common fault in general is to teach students how to pass exams instead of teaching
themthe science. 
Donald Knuth

Re: Random Number SKU Generator

From
Roxanne Reid-Bennett
Date:
On 2/14/2015 6:48 AM, Peter Dabrowski wrote:
I found one issue,
The leading zeros are removed.  expected number "00042"  printed  "42"

Regards
Peter

That would be the ltrim(string,characters) function.
        e.g. "Remove the longest string containing only characters from characters from the start of string"
 For example, taking the example from the docs:

select ltrim('zzzytrim', 'xyz')

returns trim

 - so the ltrim(x,'00000') from your original ALTER will remove all leading zeros. 

What was the intended purpose of ltrim in your pseudo code?

Roxanne 
 
 

Sent: Saturday, February 14, 2015 at 9:23 PM
From: "Peter Dabrowski" <meritage@mail.com>
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Random Number SKU Generator

Thank you Roxane,
Applied the code against db, it is generating Random SKU and I did not to have any errors to report.
So far i'ts very good.
Best Regards
Peter
 

Sent: Saturday, February 14, 2015 at 2:20 AM
From: "Roxanne Reid-Bennett" <rox@tara-lu.com>
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Random Number SKU Generator

On 2/13/2015 4:46 AM, Peter Dabrowski wrote:

I use a Luhn algorithm to generete random number, inserted below sql code to column 'SKU"
 
ALTER TABLE m_product ALTER sku SET DEFAULT ltrim(to_char(luhn_generate(round(random()*10000)::int), '00000'))
 
as a result on record saving random number is generated, but my problem is the numbers are not unique.
 
Somone sugest that I  should write a function to encapsulate "ltrim(to_char(luhn_generate(
round(random()*10000)::int), '00000'))"  and at the same time, check if the value is already used in the table.
 
in pseudo code it would look something like this:
 
generateMProductSKU(){
    skuGen=""
    needToGenerate = true
    while(needToGenerate){
      skuGen = ltrim(to_char(luhn_generate(round(random()*10000)::int), '00000'))
      if((Select count(*) from M_Product where sku =skuGen) = 0
         needToGenerate =  false
   }
   return skuGen
}
 
Culd somebody help me tu structure code into right sql format so it could be inserted into database.
 
Thank you very much.
Peterperhaps this?

create or replace function generateMProductSKU()
RETURNS text AS
$BODY$

DECLARE
    skuGen text;
    needToGenerate boolean;
BEGIN
    skuGen := '';
    needToGenerate := true;

    WHILE needToGenerate LOOP
        skuGen := ltrim(luhn_generate(round(random()*10000)::int)::text, '00000');
        SELECT count(*) != 0 into needToGenerate from m_product where sku = skuGen;
    END LOOP;

    return skuGen;
END
$BODY$
  LANGUAGE 'plpgsql' STABLE;

ALTER TABLE m_product ALTER sku SET DEFAULT generateMProductSKU();
 



-- 
[At other schools] I think the most common fault in general is to teach students how to pass exams instead of teaching them the science.
Donald Knuth

Re: Random Number SKU Generator

From
Roxanne Reid-Bennett
Date:
On 2/14/2015 10:12 AM, Roxanne Reid-Bennett wrote:
On 2/14/2015 6:48 AM, Peter Dabrowski wrote:
I found one issue,
The leading zeros are removed.  expected number "00042"  printed  "42"

Regards
Peter

hmm.. belay that.
I stripped the to_char  out - which was using the '00000'.  My draft code to develop the routine was generating a numeric that was 9 digits in length.  to_char(x,'00000') was returning '#####'

But my question still stands - what is the purpose of the ltrim? 
What is the maximum number of digits that your luhn_generate function is creating?

Roxanne

That would be the ltrim(string,characters) function.
        e.g. "Remove the longest string containing only characters from characters from the start of string"
 For example, taking the example from the docs:

select ltrim('zzzytrim', 'xyz')

returns trim

 - so the ltrim(x,'00000') from your original ALTER will remove all leading zeros. 

What was the intended purpose of ltrim in your pseudo code?

Roxanne 
 
 

Sent: Saturday, February 14, 2015 at 9:23 PM
From: "Peter Dabrowski" <meritage@mail.com>
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Random Number SKU Generator

Thank you Roxane,
Applied the code against db, it is generating Random SKU and I did not to have any errors to report.
So far i'ts very good.
Best Regards
Peter
 

Sent: Saturday, February 14, 2015 at 2:20 AM
From: "Roxanne Reid-Bennett" <rox@tara-lu.com>
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Random Number SKU Generator

On 2/13/2015 4:46 AM, Peter Dabrowski wrote:

I use a Luhn algorithm to generete random number, inserted below sql code to column 'SKU"
 
ALTER TABLE m_product ALTER sku SET DEFAULT ltrim(to_char(luhn_generate(round(random()*10000)::int), '00000'))
 
as a result on record saving random number is generated, but my problem is the numbers are not unique.
 
Somone sugest that I  should write a function to encapsulate "ltrim(to_char(luhn_generate(
round(random()*10000)::int), '00000'))"  and at the same time, check if the value is already used in the table.
 
in pseudo code it would look something like this:
 
generateMProductSKU(){
    skuGen=""
    needToGenerate = true
    while(needToGenerate){
      skuGen = ltrim(to_char(luhn_generate(round(random()*10000)::int), '00000'))
      if((Select count(*) from M_Product where sku =skuGen) = 0
         needToGenerate =  false
   }
   return skuGen
}
 
Culd somebody help me tu structure code into right sql format so it could be inserted into database.
 
Thank you very much.
Peterperhaps this?

create or replace function generateMProductSKU()
RETURNS text AS
$BODY$

DECLARE
    skuGen text;
    needToGenerate boolean;
BEGIN
    skuGen := '';
    needToGenerate := true;

    WHILE needToGenerate LOOP
        skuGen := ltrim(luhn_generate(round(random()*10000)::int)::text, '00000');
        SELECT count(*) != 0 into needToGenerate from m_product where sku = skuGen;
    END LOOP;

    return skuGen;
END
$BODY$
  LANGUAGE 'plpgsql' STABLE;

ALTER TABLE m_product ALTER sku SET DEFAULT generateMProductSKU();
 



-- 
[At other schools] I think the most common fault in general is to teach students how to pass exams instead of teaching them the science.
Donald Knuth


-- 
[At other schools] I think the most common fault in general is to teach students how to pass exams instead of teaching them the science.
Donald Knuth

Re: Random Number SKU Generator

From
"Peter Dabrowski"
Date:
The purpose is to have 5 digit unique random SKU number  "00000" which could be "56713" 
or"00001" 

 initially i got this inserted into sku as default:


ltrim(to_char(luhn_generate(round(random()*10000)::int), '00000'))


and the luhn_generate function is creating  number that was 5 digit in length "00000" so if a number was
smallerthan 5 digits always was returning with leading zero's "00042" 

 

Sent: Sunday, February 15, 2015 at 3:10 AM
From: "Roxanne Reid-Bennett" <rox@tara-lu.com>
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Random Number SKU Generator



On 2/14/2015 10:12 AM, Roxanne Reid-Bennett wrote:


On 2/14/2015 6:48 AM, Peter Dabrowski wrote:


I found one issue,
The leading zeros are removed.  expected number "00042"  printed  "42"

Regards
Peter


hmm.. belay that.
I stripped the to_char  out - which was using the '00000'.  My draft code to develop the routine was
generatinga numeric that was 9 digits in length.  to_char(x,'00000') was returning '#####' 

But my question still stands - what is the purpose of the ltrim? 
What is the maximum number of digits that your luhn_generate function is creating?

Roxanne
 
That would be the ltrim(string,characters) function.
        e.g. "Remove the longest string containing only characters from
charactersfrom the start of string" 
 For example, taking the example from the docs:

select ltrim('zzzytrim', 'xyz')

returns trim

 - so the ltrim(x,'00000') from your original ALTER will remove all leading zeros. 

What was the intended purpose of ltrim in your pseudo code?

Roxanne

 
 

Sent: Saturday, February 14, 2015 at 9:23 PM
From: "Peter Dabrowski" <meritage@mail.com>
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Random Number SKU Generator

Thank you Roxane,
Applied the code against db, it is generating Random SKU and I did not to have any errors to report.
So far i'ts very good.
Best Regards
Peter
 

Sent: Saturday, February 14, 2015 at 2:20 AM
From: "Roxanne Reid-Bennett" <rox@tara-lu.com>
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Random Number SKU Generator

On 2/13/2015 4:46 AM, Peter Dabrowski wrote:

I use a Luhn algorithm to generete random number, inserted below sql code to column 'SKU"
 
ALTER TABLE m_product ALTER sku SET DEFAULT ltrim(to_char(luhn_generate(round(random()*10000)::int), '00000'))
 
as a result on record saving random number is generated, but my problem is the numbers are not unique.
 
Somone sugest that I  should write a function to encapsulate "ltrim(to_char(luhn_generate(
round(random()*10000)::int), '00000'))"  and at the same time, check if the value is already used in
thetable. 
 
in pseudo code it would look something like this:
 
generateMProductSKU(){
    skuGen=""
    needToGenerate = true
    while(needToGenerate){
      skuGen = ltrim(to_char(luhn_generate(round(random()*10000)::int), '00000'))
      if((Select count(*) from M_Product where sku =skuGen) = 0
         needToGenerate =  false
   }
   return skuGen
}
 
Culd somebody help me tu structure code into right sql format so it could be inserted into database.
 
Thank you very much.
Peterperhaps this?

create or replace function generateMProductSKU()
RETURNS text AS
$BODY$

DECLARE
    skuGen text;
    needToGenerate boolean;
BEGIN
    skuGen := '';
    needToGenerate := true;

    WHILE needToGenerate LOOP
        skuGen := ltrim(luhn_generate(round(random()*10000)::int)::text,
'00000');
        SELECT count(*) != 0 into needToGenerate from m_product where sku = skuGen;
    END LOOP;

    return skuGen;
END
$BODY$
  LANGUAGE 'plpgsql' STABLE;

ALTER TABLE m_product ALTER sku SET DEFAULT generateMProductSKU();
 


 

--
[At other schools] I think the most common fault in general is to teach students how to pass exams instead of teaching
themthe science. 
Donald Knuth

 

--
[At other schools] I think the most common fault in general is to teach students how to pass exams instead of teaching
themthe science. 
Donald Knuth

Re: Random Number SKU Generator

From
Roxanne Reid-Bennett
Date:
On 2/14/2015 6:34 PM, Peter Dabrowski wrote:
The purpose is to have 5 digit unique random SKU number  "00000" which could be "56713"  or "00001"
 initially i got this inserted into sku as default:
ltrim(to_char(luhn_generate(round(random()*10000)::int), '00000'))
and the luhn_generate function is creating  number that was 5 digit in length "00000" so if a number was smaller than 5 digits always was returning with leading zero's "00042"

change the "skuGen :="  line to:

skuGen := to_char(luhn_generate(round(random()*10000)::int), '00000');

You don't need the ltrim, but you do need the to_char.

Roxanne
 
Sent: Sunday, February 15, 2015 at 3:10 AM
From: "Roxanne Reid-Bennett" <rox@tara-lu.com>
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Random Number SKU Generator
On 2/14/2015 10:12 AM, Roxanne Reid-Bennett wrote:
On 2/14/2015 6:48 AM, Peter Dabrowski wrote:
I found one issue,
The leading zeros are removed.  expected number "00042"  printed  "42"

Regards
Peter
hmm.. belay that.
I stripped the to_char  out - which was using the '00000'.  My draft code to develop the routine was generating a numeric that was 9 digits in length.  to_char(x,'00000') was returning '#####'

But my question still stands - what is the purpose of the ltrim? 
What is the maximum number of digits that your luhn_generate function is creating?

Roxanne
 
That would be the ltrim(string,characters) function.
        e.g. "Remove the longest string containing only characters from characters from the start of string"
 For example, taking the example from the docs:

select ltrim('zzzytrim', 'xyz')

returns trim

 - so the ltrim(x,'00000') from your original ALTER will remove all leading zeros. 

What was the intended purpose of ltrim in your pseudo code?

Roxanne 
 
 

Sent: Saturday, February 14, 2015 at 9:23 PM
From: "Peter Dabrowski" <meritage@mail.com>
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Random Number SKU Generator

Thank you Roxane,
Applied the code against db, it is generating Random SKU and I did not to have any errors to report.
So far i'ts very good.
Best Regards
Peter
 

Sent: Saturday, February 14, 2015 at 2:20 AM
From: "Roxanne Reid-Bennett" <rox@tara-lu.com>
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Random Number SKU Generator

On 2/13/2015 4:46 AM, Peter Dabrowski wrote:

I use a Luhn algorithm to generete random number, inserted below sql code to column 'SKU"
 
ALTER TABLE m_product ALTER sku SET DEFAULT ltrim(to_char(luhn_generate(round(random()*10000)::int), '00000'))
 
as a result on record saving random number is generated, but my problem is the numbers are not unique.
 
Somone sugest that I  should write a function to encapsulate "ltrim(to_char(luhn_generate(
round(random()*10000)::int), '00000'))"  and at the same time, check if the value is already used in the table.
 
in pseudo code it would look something like this:
 
generateMProductSKU(){
    skuGen=""
    needToGenerate = true
    while(needToGenerate){
      skuGen = ltrim(to_char(luhn_generate(round(random()*10000)::int), '00000'))
      if((Select count(*) from M_Product where sku =skuGen) = 0
         needToGenerate =  false
   }
   return skuGen
}
 
Culd somebody help me tu structure code into right sql format so it could be inserted into database.
 
Thank you very much.
Peterperhaps this?

create or replace function generateMProductSKU()
RETURNS text AS
$BODY$

DECLARE
    skuGen text;
    needToGenerate boolean;
BEGIN
    skuGen := '';
    needToGenerate := true;

    WHILE needToGenerate LOOP
        skuGen := ltrim(luhn_generate(round(random()*10000)::int)::text, '00000');
        SELECT count(*) != 0 into needToGenerate from m_product where sku = skuGen;
    END LOOP;

    return skuGen;
END
$BODY$
  LANGUAGE 'plpgsql' STABLE;

ALTER TABLE m_product ALTER sku SET DEFAULT generateMProductSKU();
 
  
-- 
[At other schools] I think the most common fault in general is to teach students how to pass exams instead of teaching them the science.
Donald Knuth
  
-- 
[At other schools] I think the most common fault in general is to teach students how to pass exams instead of teaching them the science.
Donald Knuth


-- 
[At other schools] I think the most common fault in general is to teach students how to pass exams instead of teaching them the science.
Donald Knuth

Re: Random Number SKU Generator

From
"Peter Dabrowski"
Date:
Thank you Roxane,

 

Tested it with db and returnig as expected.

 

Best Regards

Peter

 

Sent: Sunday, February 15, 2015 at 1:50 PM
From: "Roxanne Reid-Bennett" <rox@tara-lu.com>
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Random Number SKU Generator



On 2/14/2015 6:34 PM, Peter Dabrowski wrote:




The purpose is to have 5 digit unique random SKU number  "00000" which could be "56713" 
or"00001" 

 initially i got this inserted into sku as default:


ltrim(to_char(luhn_generate(round(random()*10000)::int), '00000'))


and the luhn_generate function is creating  number that was 5 digit in length "00000" so if a number was
smallerthan 5 digits always was returning with leading zero's "00042" 




change the "skuGen :="  line to:

skuGen := to_char(luhn_generate(round(random()*10000)::int), '00000');

You don't need the ltrim, but you do need the to_char.

Roxanne



 

Sent: Sunday, February 15, 2015 at 3:10 AM
From: "Roxanne Reid-Bennett" <rox@tara-lu.com>
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Random Number SKU Generator



On 2/14/2015 10:12 AM, Roxanne Reid-Bennett wrote:


On 2/14/2015 6:48 AM, Peter Dabrowski wrote:


I found one issue,
The leading zeros are removed.  expected number "00042"  printed  "42"

Regards
Peter


hmm.. belay that.
I stripped the to_char  out - which was using the '00000'.  My draft code to develop the routine was
generatinga numeric that was 9 digits in length.  to_char(x,'00000') was returning '#####' 

But my question still stands - what is the purpose of the ltrim? 
What is the maximum number of digits that your luhn_generate function is creating?

Roxanne
 
That would be the ltrim(string,characters) function.
        e.g. "Remove the longest string containing only characters from
charactersfrom the start of string" 
 For example, taking the example from the docs:

select ltrim('zzzytrim', 'xyz')

returns trim

 - so the ltrim(x,'00000') from your original ALTER will remove all leading zeros. 

What was the intended purpose of ltrim in your pseudo code?

Roxanne

 
 

Sent: Saturday, February 14, 2015 at 9:23 PM
From: "Peter Dabrowski" <meritage@mail.com>
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Random Number SKU Generator

Thank you Roxane,
Applied the code against db, it is generating Random SKU and I did not to have any errors to report.
So far i'ts very good.
Best Regards
Peter
 

Sent: Saturday, February 14, 2015 at 2:20 AM
From: "Roxanne Reid-Bennett" <rox@tara-lu.com>
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Random Number SKU Generator

On 2/13/2015 4:46 AM, Peter Dabrowski wrote:

I use a Luhn algorithm to generete random number, inserted below sql code to column 'SKU"
 
ALTER TABLE m_product ALTER sku SET DEFAULT ltrim(to_char(luhn_generate(round(random()*10000)::int), '00000'))
 
as a result on record saving random number is generated, but my problem is the numbers are not unique.
 
Somone sugest that I  should write a function to encapsulate "ltrim(to_char(luhn_generate(
round(random()*10000)::int), '00000'))"  and at the same time, check if the value is already used in
thetable. 
 
in pseudo code it would look something like this:
 
generateMProductSKU(){
    skuGen=""
    needToGenerate = true
    while(needToGenerate){
      skuGen = ltrim(to_char(luhn_generate(round(random()*10000)::int), '00000'))
      if((Select count(*) from M_Product where sku =skuGen) = 0
         needToGenerate =  false
   }
   return skuGen
}
 
Culd somebody help me tu structure code into right sql format so it could be inserted into database.
 
Thank you very much.
Peterperhaps this?

create or replace function generateMProductSKU()
RETURNS text AS
$BODY$

DECLARE
    skuGen text;
    needToGenerate boolean;
BEGIN
    skuGen := '';
    needToGenerate := true;

    WHILE needToGenerate LOOP
        skuGen := ltrim(luhn_generate(round(random()*10000)::int)::text,
'00000');
        SELECT count(*) != 0 into needToGenerate from m_product where sku = skuGen;
    END LOOP;

    return skuGen;
END
$BODY$
  LANGUAGE 'plpgsql' STABLE;

ALTER TABLE m_product ALTER sku SET DEFAULT generateMProductSKU();
 


 

--
[At other schools] I think the most common fault in general is to teach students how to pass exams instead of teaching
themthe science. 
Donald Knuth

 

--
[At other schools] I think the most common fault in general is to teach students how to pass exams instead of teaching
themthe science. 
Donald Knuth







 

--
[At other schools] I think the most common fault in general is to teach students how to pass exams instead of teaching
themthe science. 
Donald Knuth

Re: Random Number SKU Generator

From
David G Johnston
Date:
Peter Dabrowski wrote
> I found one issue,
> The leading zeros are removed.  expected number "00042"  printed  "42"

My unscientific suggestion is to combine a leading random number between 1
and 9 with the trailing 4 digit (With leading zeros) number so that you
never have the first digit of the SKU be a zero.

Too many technical issues with all numeric strings containing leading zeros;
and I think human ones too.

David J.




--
View this message in context: http://postgresql.nabble.com/Random-Number-SKU-Generator-tp5837824p5838022.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

Re: Random Number SKU Generator

From
David G Johnston
Date:
Roxanne Reid-Bennett wrote
>      WHILE needToGenerate LOOP
>          skuGen :=
> ltrim(luhn_generate(round(random()*10000)::int)::text, '00000');
>          SELECT count(*) != 0 into needToGenerate from m_product where
> sku = skuGen;
>      END LOOP;

The other solution is to maintain a sequential table of unused SKU codes.
Keep track of its size (or a close approximation) and generate a random
number to use as an offset into that table.  Remove rows as you use them.

Or, in a similar fashion, create the table with a random but fixed order and
simply traverse it serially; removing each row as you consume it so that it,
and the matching index, only have unassigned codes in the correct order.

The choices have trade-offs between each other so you are well off wrapping
the API in a function no matter what you do.  And possibly be prepared to
switch from the loop to the master table approach as your consumption of
codes increases.

David J.




--
View this message in context: http://postgresql.nabble.com/Random-Number-SKU-Generator-tp5837824p5838023.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.

Re: Random Number SKU Generator

From
"Peter Dabrowski"
Date:

Yes, I agree with this.
I believe  that Luhn algorithm function got check digit, so if this check digit could be placed in front this could be
achieved.

regards
Peter


Sent: Sunday, February 15, 2015 at 8:22 PM
From: "David G Johnston" <david.g.johnston@gmail.com>
To: pgsql-novice@postgresql.org
Subject: Re: [NOVICE] Random Number SKU Generator
Peter Dabrowski wrote
> I found one issue,
> The leading zeros are removed. expected number "00042" printed "42"

My unscientific suggestion is to combine a leading random number between 1
and 9 with the trailing 4 digit (With leading zeros) number so that you
never have the first digit of the SKU be a zero.

Too many technical issues with all numeric strings containing leading zeros;
and I think human ones too.

David J.




--
View this message in context: http://postgresql.nabble.com/Random-Number-SKU-Generator-tp5837824p5838022.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.


--
Sent via pgsql-novice mailing list (pgsql-novice@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-novice[http://www.postgresql.org/mailpref/pgsql-novice]
Attachment