Quick Links

Re: Random Number SKU Generator

From:	"Peter Dabrowski" <meritage(at)mail(dot)com>
To:	pgsql-novice(at)postgresql(dot)org
Subject:	Re: Random Number SKU Generator
Date:	2015-02-15 03:22:55
Message-ID:	trinity-16649c8a-9767-40d2-b838-837271170810-1423970575413@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>Tested it with db and returnig as expected.</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> Sunday, February 15, 2015 at 1:50 PM<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/14/2015 6:34 PM, Peter Dabrowski wrote:</div>

<blockquote>
<div style="font-family: Verdana;font-size: 12.0px;">
<div>
<div>The purpose is to have 5 digit unique random SKU number  "00000" which could be "56713"  or "00001"</div>

<div> initially i got this inserted into sku as default:</div>

<div>
<pre>ltrim(to_char(luhn_generate(round(random()*10000)::int), '00000'))</pre>
</div>

<div>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"</div>
</div>
</div>
</blockquote>
<br/>
change the "skuGen :="  line to:<br/>
<br/>
skuGen := to_char(luhn_generate(round(random()*10000)::int), '00000');<br/>
<br/>
You don't need the ltrim, but you do need the to_char.<br/>
<br/>
Roxanne
<blockquote>
<div style="font-family: Verdana;font-size: 12.0px;">
<div>
<div> 
<div style="margin: 10.0px 5.0px 5.0px 10.0px;padding: 10.0px 0 10.0px 10.0px;border-left: 2.0px solid rgb(195,217,229);">
<div style="margin: 0 0 10.0px 0;"><b>Sent:</b> Sunday, February 15, 2015 at 3:10 AM<br/>
<b>From:</b> "Roxanne Reid-Bennett" <a class="moz-txt-link-rfc2396E" href="rox(at)tara-lu(dot)com" target="_parent"><rox(at)tara-lu(dot)com></a><br/>
<b>To:</b> <a class="moz-txt-link-abbreviated" href="pgsql-novice(at)postgresql(dot)org" target="_parent">pgsql-novice(at)postgresql(dot)org</a><br/>
<b>Subject:</b> Re: [NOVICE] Random Number SKU Generator</div>

<div>
<div style="background-color: rgb(255,255,255);">
<div class="moz-cite-prefix">On 2/14/2015 10:12 AM, Roxanne Reid-Bennett wrote:</div>

<blockquote>
<div class="moz-cite-prefix">On 2/14/2015 6:48 AM, Peter Dabrowski wrote:</div>

<blockquote>
<pre>I found one issue,
The leading zeros are removed. expected number "00042" printed "42"

Regards
Peter </pre>
</blockquote>
</blockquote>
hmm.. belay that.<br/>
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 '#####'<br/>
<br/>
But my question still stands - what is the purpose of the ltrim? <br/>
What is the maximum number of digits that your luhn_generate function is creating?<br/>
<br/>
Roxanne<br/>
 
<blockquote>That would be the ltrim(<tt class="PARAMETER">string</tt>,<tt class="PARAMETER">characters</tt>) function.<br/>
        e.g. "Remove the longest string containing <b>only </b>characters from <tt class="PARAMETER">characters</tt> from the start of <tt class="PARAMETER">string"</tt><br/>
 For example, taking the example from the docs:<br/>
<br/>
select ltrim('zzzytrim', 'xyz')<br/>
<br/>
returns trim<br/>
<br/>
 - so the ltrim(x,'00000') from your original ALTER will remove all leading zeros. <br/>
<br/>
What was the intended purpose of ltrim in your pseudo code?<br/>
<br/>
Roxanne
<blockquote>
<pre> 
 

Sent: Saturday, February 14, 2015 at 9:23 PM
From: "Peter Dabrowski" <a class="moz-txt-link-rfc2396E"><meritage(at)mail(dot)com></a>
To: <a class="moz-txt-link-abbreviated">pgsql-novice(at)postgresql(dot)org</a>
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" <a class="moz-txt-link-rfc2396E"><rox(at)tara-lu(dot)com></a>
To: <a class="moz-txt-link-abbreviated">pgsql-novice(at)postgresql(dot)org</a>
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();
 
</pre>
</blockquote>
 

<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>
</blockquote>
 

<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>
</blockquote>
 

Attachment	Content-Type	Size
unknown_filename	text/html	7.4 KB

In response to

Re: Random Number SKU Generator at 2015-02-15 02:50:44 from Roxanne Reid-Bennett

Browse pgsql-novice by date

	From	Date	Subject
Next Message	David G Johnston	2015-02-15 09:22:58	Re: Random Number SKU Generator
Previous Message	Roxanne Reid-Bennett	2015-02-15 02:50:44	Re: Random Number SKU Generator