From: | David G Johnston <david(dot)g(dot)johnston(at)gmail(dot)com> |
---|---|
To: | pgsql-novice(at)postgresql(dot)org |
Subject: | Re: Random Number SKU Generator |
Date: | 2015-02-15 09:35:28 |
Message-ID: | 1423992928848-5838023.post@n5.nabble.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-novice |
Roxanne Reid-Bennett wrote
> 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;
The other solution is to maintain a sequential table of unused SKU codes.
Keep track of its size (or a close approximation) and generate a random
number to use as an offset into that table. Remove rows as you use them.
Or, in a similar fashion, create the table with a random but fixed order and
simply traverse it serially; removing each row as you consume it so that it,
and the matching index, only have unassigned codes in the correct order.
The choices have trade-offs between each other so you are well off wrapping
the API in a function no matter what you do. And possibly be prepared to
switch from the loop to the master table approach as your consumption of
codes increases.
David J.
--
View this message in context: http://postgresql.nabble.com/Random-Number-SKU-Generator-tp5837824p5838023.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.
From | Date | Subject | |
---|---|---|---|
Next Message | Lacey Powers | 2015-02-15 20:41:28 | Re: How to create tables and copy records in blocks? |
Previous Message | David G Johnston | 2015-02-15 09:22:58 | Re: Random Number SKU Generator |