as the question says, I need postgresql procedure which will generate Some ids like:
AX0
AX1
AX2
.
.
AX9
AXA
AXB
.
.
AXZ
AY0
AY1
.
.
AY9
AYA
AYB
.
.
AZZ
B00
B01
B02
.
.
etc
And the proceedure can also generate next id when given previous, like send AST and return ASU, or 23X to return 23Y......etc.
Example: SELECT db.stored_proceedure('AST');
As a novice, for now all I have is:
with
letters as
(select chr(i) as letter from generate_series(65,90) i),
digits as
(select lpad(i::text,1,'0') as digit from generate_series(0,9) i)
select l1.letter || l2.letter || l3.letter || l4.letter || d.digit
from letters l1
cross join letters l2
cross join letters l3
cross join letters l4
cross join digits d
limit 2000
Which only generate something like:
AAAA8
AAAA9
AAAB0
AAAB1
AAAB2
AAAB3
.....
AAAB8
AAAB9
AAAC0
AAAC1
AAAC2
Gurus in the house, please help. Thanks
For a 3-char value that includes 0-9 and then A-Z you could produce the next value with the query:
with p (q) as (values ('000'), ('999'), ('99Z'), ('9ZZ'), ('AST'), ('23X'))
select q, m
from (
select q, substr(q, 1, 1) as a, substr(q, 2, 1) as b, substr(q, 3, 1) as c from p
) u,
lateral (
select (ascii(a) - case when a <= '9' then 48 else 55 end) * 36 * 36 +
(ascii(b) - case when b <= '9' then 48 else 55 end) * 36 +
ascii(c) - case when c <= '9' then 48 else 55 end + 1 as n
) v,
lateral (select n / 36 / 36 as d0, mod(n, 36 * 36) as r0) x,
lateral (select r0 / 36 as d1, mod(r0, 36) as d2) y,
lateral (
select chr(case when d0 < 10 then 48 else 55 end + d0) ||
chr(case when d1 < 10 then 48 else 55 end + d1) ||
chr(case when d2 < 10 then 48 else 55 end + d2) as m
) z
Result:
q m
---- ---
000 001
999 99A
99Z 9A0
9ZZ A00
AST ASU
23X 23Y
See db<>fiddle.
Change values in line 1 to see more examples.