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.