How to create an alphanumeric sequence like AAAA0000 and so on
5
votes
1
answer
9677
views
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?
Asked by juanpscotto
(163 rep)
Sep 6, 2017, 01:21 PM
Last activity: Sep 8, 2017, 09:24 AM
Last activity: Sep 8, 2017, 09:24 AM