Re: Random Number SKU Generator

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(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