Em primeiro lugar, deixo para os comentários dos interessados maiores discussões sobre as possíveis alternativas. Apresentarei uma solução simples que utiliza triggers, mas espero que a mesma possa ser melhorada e questionada por você, caro leitor.
* Solução de lista de códigos
Permite o controle da utilização e reutilização de códigos através de uma lista com dois campos, um sendo o primeiro o valor do código e outro a indicação de sua utilização ou não.
A lista é armazenada em uma tabela e inicializada na sua criação. Esta lista é atualizada por uma trigger na tabela "usuária" dos códigos. O gatilho atualiza a tabela de código em dois momentos: na utilização de um código e na liberação do código para reuso.
* Tabela de Lista de Códigos
Abaixo, coloco o script da nossa lista de códigos. Coloquei a cláusula UNIQUE para o campo de código para evitar repetições que violassem a integridade da lista e ao tempo gerar um índice implícido para o campo de código, a ser utilizado nas consultas. Vamos inicializar a nossa lista com 10 códigos, inteiros com valor entre 1 e 10, com o valor false, equivalente a "desalocado".
CREATE TABLE lista_codigos (codigo integer UNIQUE, flag_utilizado boolean);
INSERT INTO lista_codigos (codigo, flag_utilizado) VALUES (1,false), (2,false), (3,false), (4,false), (5,false), (6,false), (7,false), (8,false), (9,false), (10,false); --INICIALIZACAO
* Tabela Usuária de Códigos
A tabela em que serão armazenados os códigos utilizados apresentará dois gatilhos, sendo um em caso de inserção de código, e outra para exclusão de código. Adicionei também uma restrição UNIQUE no campo código para não ter de validar tentativas de utilizar o código mais de uma vez.
CREATE TABLE usa_codigos (codigo integer UNIQUE);
Abaixo, os códigos das trigers e trigger functions associadas.
CREATE OR REPLACE FUNCTION pegar_codigo() RETURNS trigger AS $pegar_codigo$
BEGIN
UPDATE lista_codigos SET flag_utilizado = true WHERE codigo = new.codigo;
RETURN new;
END;
$pegar_codigo$ LANGUAGE plpgsql;
CREATE TRIGGER pega_codigo BEFORE INSERT ON usa_codigos FOR EACH ROW EXECUTE PROCEDURE pegar_codigo ();
CREATE OR REPLACE FUNCTION liberar_codigo() RETURNS trigger AS $liberar_codigo$
BEGIN
UPDATE lista_codigos SET flag_utilizado = false WHERE lista_codigos.codigo = old.codigo;
RETURN old;
END;
$liberar_codigo$ LANGUAGE plpgsql;
CREATE TRIGGER libera_codigo AFTER DELETE ON usa_codigos FOR EACH ROW EXECUTE PROCEDURE liberar_codigo ();
* Testes da Lista
CENÁRIO 1: Reservando códigos. Neste cenário, basta inserir um registro na tabela usa_codigos e consultar a tabela lista_codigos para verificar se foram reservados os códigos.
INSERT INTO usa_codigos (codigo) VALUES (1), (3), (4), (9), (10);
O resultado obtido está correto.
SELECT codigo, CASE
WHEN flag_utilizado = true THEN 'SIM'
ELSE 'NÃO'
END AS reservado FROM lista_codigos ORDER BY codigo;
codigo | reservado
--------+-----------
1 | SIM
2 | NÃO
3 | SIM
4 | SIM
5 | NÃO
6 | NÃO
7 | NÃO
8 | NÃO
9 | SIM
10 | SIM
(10 registros)
CENÁRIO 2: Liberando códigos. Inicialmente, libero o código 3. A liberação é a exclusão da tabela de códigos.
DELETE FROM usa_codigos WHERE codigo = 3;
O resultado obtido está de acordo com o desejado.
codigo | reservado
--------+-----------
1 | SIM
2 | NÃO
3 | NÃO
4 | SIM
5 | NÃO
6 | NÃO
7 | NÃO
8 | NÃO
9 | SIM
10 | SIM
(10 registros)
CENÁRIO 3: Liberando todos os códigos de uma só vez. O resultado corresponde à expectativa.
DELETE FROM usa_codigos;
codigo | reservado
--------+-----------
1 | NÃO
2 | NÃO
3 | NÃO
4 | NÃO
5 | NÃO
6 | NÃO
7 | NÃO
8 | NÃO
9 | NÃO
10 | NÃO
(10 registros)
CENÁRIO 4: Testando reservar código já reservado. A restrição CHECK garante a integridade e o código é reservado, como esperado.
postgres=# INSERT INTO usa_codigos (codigo) VALUES (1);
INSERT 0 1
postgres=# INSERT INTO usa_codigos (codigo) VALUES (1);
ERRO: duplicar valor da chave viola a restrição de unicidade "usa_codigos_codigo_key"
DETALHE: Chave (codigo)=(1) já existe.
codigo | reservado
--------+-----------
1 | SIM
2 | NÃO
3 | NÃO
4 | NÃO
5 | NÃO
6 | NÃO
7 | NÃO
8 | NÃO
9 | NÃO
10 | NÃO
(10 registros)
CENÁRIO 5: Testando liberar código já liberado. Não ocorre erro e a alocação dos códigos permanece inalterada. Ao menos neste teste inicial, as operações foram realizadas a contento!
postgres=# DELETE FROM usa_codigos WHERE codigo = 3;
DELETE 0
postgres=# SELECT codigo, CASE
WHEN flag_utilizado = true THEN 'SIM'
ELSE 'NÃO'
END AS reservado FROM lista_codigos ORDER BY codigo;
codigo | reservado
--------+-----------
1 | SIM
2 | NÃO
3 | NÃO
4 | NÃO
5 | NÃO
6 | NÃO
7 | NÃO
8 | NÃO
9 | NÃO
10 | NÃO
(10 registros)
* Adicionando Funcionalidades
Podem ser implementadas consultas ou funções que ajudem a emitir as senhas e liberar as senhas para reaproveitamento, além de consultas básicas para:
- Consultar o primeiro código disponível
- Consultar o último código disponível
- Consultar a quantidade de códigos disponíveis
- Consultar a quantidade de códigos não disponíveis
Mas deixo aos leitores estas tarefas como exercício.
* Notas conclusivas
- Uma trigger ou restrição de acesso pode evitar que se utilize a cláusula UPDATE para a tabela de códigos, ou tratar o update, o que eu acho mais trabalhoso!
- Deixo para os leitores a tarefa de implementar esta solução utilizando arrays, e empregando outras formas alternativas e talvez mais eficientes.
