Re: 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.
> Peter
perhaps 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