Re: Random Number SKU Generator
От | Peter Dabrowski |
---|---|
Тема | Re: Random Number SKU Generator |
Дата | |
Msg-id | trinity-2d790e69-7146-4f4f-881c-add1c354bbe1-1423914512257@3capp-mailcom-lxa08 обсуждение исходный текст |
Ответ на | Re: Random Number SKU Generator ("Peter Dabrowski" <meritage@mail.com>) |
Ответы |
Re: Random Number SKU Generator
(Roxanne Reid-Bennett <rox@tara-lu.com>)
Re: Random Number SKU Generator (David G Johnston <david.g.johnston@gmail.com>) |
Список | pgsql-novice |
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
В списке pgsql-novice по дате отправления: