I am trying to create sequence that stored in db.
So just only with one service call I should get new sequence by order like AAB.
Next call should return AAC, next AAD…. AA9, ABA…
I tried to create three number sequences 0<=first_seq<36, also like this second_seq, third_seq.
I am using spring hibernate, postgresql.
Question posted in PostgreSQL
The official documentation can be found here.
The official documentation can be found here.
3
Answers
Try this.
and
output
Well your question is ambiguous, you should indicate wanting a Java or a Postgres solution at a minimum. The following presents a fully generic Postgres solution given only the previous sequence and a string with the ordered set of digits. It creates 2 CTEs the first defines a the digits then the second ‘builds’ a working set of variables. Finally the main sql builds the next sequence. This is handled is a single statement, nested into a sql function. (see demo)
NOTE: As a SQL function it can be extracted and run as an independent statement. You will just need to pass the current sequence as a parameter.
EDIT: Reply to Vérace. I did not get the generate the entire sequence because as stated Next call should return AAC, next (call) AAD …. Further the sequence from
... AA9, ABA...
has no ending. But without reworking the function you could use it within a recursive query.Firstly, I did this (all of the code below is available here).
Result:
But, you want it sorted
AAA
,AAB
… but1
is beforeA
in the ASCII table, so we have to do this:Result:
So, now we have the correct sort order.
We can put all of this in a function as follows:
and the run:
Result:
With thanks to @Belayer’s code for the inspiration on the function!