| From: | "Peter Dabrowski" <meritage(at)mail(dot)com> |
|---|---|
| To: | pgsql-novice(at)postgresql(dot)org |
| Subject: | Re: Random Number SKU Generator |
| Date: | 2015-02-14 10:23:56 |
| Message-ID: | trinity-4bcedd90-4820-49b2-a6e8-2cbb03de3b6c-1423909435945@3capp-mailcom-lxa08 |
| Views: | Raw Message | Whole Thread | Download mbox | Resend email |
| Thread: | |
| Lists: | pgsql-novice |
<html><head></head><body><div style="font-family: Verdana;font-size: 12.0px;"><div>
<div>Thank you Roxane,</div>
<div>Applied the code against db, it is generating Random SKU and I did not to have any errors to report.</div>
<div>So far i'ts very good.</div>
<div>Best Regards</div>
<div>Peter</div>
<div>
<div name="quote" style="margin:10px 5px 5px 10px; padding: 10px 0 10px 10px; border-left:2px solid #C3D9E5; word-wrap: break-word; -webkit-nbsp-mode: space; -webkit-line-break: after-white-space;">
<div style="margin:0 0 10px 0;"><b>Sent:</b> Saturday, February 14, 2015 at 2:20 AM<br/>
<b>From:</b> "Roxanne Reid-Bennett" <rox(at)tara-lu(dot)com><br/>
<b>To:</b> pgsql-novice(at)postgresql(dot)org<br/>
<b>Subject:</b> Re: [NOVICE] Random Number SKU Generator</div>
<div name="quoted-content">
<div style="background-color: rgb(255,255,255);">
<div class="moz-cite-prefix">On 2/13/2015 4:46 AM, Peter Dabrowski wrote:</div>
<blockquote>
<div style="font-family: Verdana;font-size: 12.0px;">
<div>I use a Luhn algorithm to generete random number, inserted below sql code to column 'SKU"</div>
<div> </div>
<div>ALTER TABLE m_product ALTER sku SET DEFAULT ltrim(to_char(luhn_generate(round(random()*10000)::int), '00000'))</div>
<div> </div>
<div>as a result on record saving random number is generated, but my problem is the numbers are not unique.</div>
<div> </div>
<div><font color="#000000">Somone sugest that I should write a function to en<font face="arial, sans-serif" size="2">capsulate "ltrim(to_char(luhn_generate(</font></font>
<div><font color="#000000" face="arial, sans-serif" size="2">round(random()*10000)::int), '00000'))" and at the same time, check if the value is already used in the table.</font></div>
<div> </div>
<div><font color="#000000" face="arial, sans-serif" size="2">in pseudo code it would look something like this:</font></div>
<div> </div>
<div>generateMProductSKU(){<br/>
skuGen=""<br/>
needToGenerate = true<br/>
while(needToGenerate){<br/>
skuGen = ltrim(to_char(luhn_generate(round(random()*10000)::int), '00000'))<br/>
if((Select count(*) from M_Product where sku =skuGen) = 0<br/>
needToGenerate = false<br/>
}<br/>
return skuGen<br/>
}</div>
<div> </div>
<div>Culd somebody help me tu structure code into right sql format so it could be inserted into database.</div>
<div> </div>
<div>Thank you very much.</div>
<div>Peter</div>
</div>
</div>
</blockquote>
perhaps this?<br/>
<br/>
create or replace function generateMProductSKU()<br/>
RETURNS text AS<br/>
$BODY$<br/>
<br/>
DECLARE<br/>
skuGen text;<br/>
needToGenerate boolean;<br/>
BEGIN<br/>
skuGen := '';<br/>
needToGenerate := true;<br/>
<br/>
WHILE needToGenerate LOOP<br/>
skuGen := ltrim(luhn_generate(round(random()*10000)::int)::text, '00000');<br/>
SELECT count(*) != 0 into needToGenerate from m_product where sku = skuGen;<br/>
END LOOP;<br/>
<br/>
return skuGen;<br/>
END<br/>
$BODY$<br/>
LANGUAGE 'plpgsql' STABLE;<br/>
<br/>
ALTER TABLE m_product ALTER sku SET DEFAULT generateMProductSKU();<br/>
<pre class="moz-signature">--
[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</pre>
</div>
</div>
</div>
</div>
</div></div></body></html>
| Attachment | Content-Type | Size |
|---|---|---|
| unknown_filename | text/html | 3.8 KB |
| From | Date | Subject | |
|---|---|---|---|
| Next Message | Peter Dabrowski | 2015-02-14 11:48:32 | Re: Random Number SKU Generator |
| Previous Message | Roxanne Reid-Bennett | 2015-02-13 15:20:45 | Re: Random Number SKU Generator |