How to create an alphanumeric sequence like AAAA0000 and so on

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?

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

StackExchange Database Administrators Q#185200, answer score: 2