Re: 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 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(at)tara-lu(dot)com>
> *To:* pgsql-novice(at)postgresql(dot)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(at)mail(dot)com>
> To:pgsql-novice(at)postgresql(dot)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(at)tara-lu(dot)com>
> To:pgsql-novice(at)postgresql(dot)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