How to create an alphanumeric sequence like AAAA0000 and so on

Question

I want to create an alphanumeric sequence like this:

AAAA0000
AAAA0001
AAAA0002
AAAA0003
.
.
.
AAAA9999
AAAB0000
AAAB0001
.
.
.
ZZZZ9999

I have created this store procedure to do that but its too slow:

CREATE OR REPLACE FUNCTION public.fn_batch_seq()
  RETURNS text
  LANGUAGE plpgsql
AS
$body$
DECLARE
  v_sequence TEXT := '';
  v_next_sequence TEXT := '';
  v_existing_id BIGINT := 0;
BEGIN

  /*
  *  VARCHAR BATCH SEQUENCE FOR SIMCARDS
  */
  SELECT "sequence" FROM batch_sequence WHERE id = 1 INTO v_sequence;
  IF v_sequence = '' THEN
    RAISE NOTICE 'Error - No existe ningun registro en batch_sequence almacenado';
    RETURN -500;
  END IF;
  SELECT perl_increment(v_sequence) INTO v_next_sequence;

  IF v_next_sequence = '' THEN
    RAISE NOTICE 'Error - La siguiente secuencia generada devolvio null o vacio';
    RETURN -500;
  END IF;


  UPDATE batch_sequence SET "sequence" = v_next_sequence WHERE id = 1;
  RETURN v_next_sequence;

  EXCEPTION WHEN OTHERS THEN
  /*
  * Other errors
  */
  RAISE NOTICE 'Error General - Posibles causas: No existe la tabla batch_sequence o no existe ningun registro en la misma';
  RETURN -500;

END;
$body$
  VOLATILE
  COST 100;

This procedure uses a table to store the sequence:

CREATE TABLE batch_sequence
(
   id        serial   NOT NULL,
   sequence  text     DEFAULT 'AAAA0000'::text NOT NULL
);

-- Column id is associated with sequence public.batch_sequence_id_seq

ALTER TABLE batch_sequence
   ADD CONSTRAINT batch_sequence_pk
   PRIMARY KEY (id);

And for increment the sequence I use a perl procedure:

CREATE OR REPLACE FUNCTION public.perl_increment(text)
  RETURNS text
  LANGUAGE plperl
AS
$body$
my ($x) = @_;
    if (not defined $x) {
        return undef;
    }
    ++$x;
$body$
  VOLATILE
  COST 100;

It works very slow with large amount of data, because it must be executed before inserting every single row. Is there another way to do that with or without Perl?

Answer 1

We can do this with a couple common table expressions (CTEs) that generate some simple series ...

with 

letters as
(select chr(i) as letter from generate_series(65,90) i),

digits as
(select lpad(i::text,4,'0') as digit from generate_series(0,9999) 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

• our first CTE is named letters
• letters generates a series of numbers from 65 to 90, which happen to be the ascii codes for letters 'A' to 'Z' so ...
• we can generate our characters via the chr(i) function call
• at this point letters represents the set of characters 'A' to 'Z'
• our next CTE is digits
• digits generates a series of numbers from 0 to 9999, and since we're going to be dealing with characters ...
• lpad(i::text,4,'0') allows us to convert this series of numbers to left/zero padded character strings
• at this point digits represents a set of characters '0000', '0001', ... '9999'
• from here we just need to perform a series of cross joins and append our letters/digits together

Here's a dbfiddle for the above.

For the dbfiddle you can remove the limit/offset clause to generate the complete set, alternatively adjust the limit/offset clause to see a range of outputs.

For example, with limit 20 offset 59990 we get:

?column?
--------
AAAF9990
AAAF9991
AAAF9992
AAAF9993
AAAF9994
AAAF9995
AAAF9996
AAAF9997
AAAF9998
AAAF9999
AAAG0000
AAAG0001
AAAG0002
AAAG0003
AAAG0004
AAAG0005
AAAG0006
AAAG0007
AAAG0008
AAAG0009