Re: 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