Sample Header Ad - 728x90

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