Campo de incremento automático personalizado no postgresql (Fatura / Nº do pedido)
O requisito da linha de base é criar um número de pedido no formato:
(M)M-SSS
Onde MM representa o mês atual e SSSS representa a sequência de ordens para esse mês. Por exemplo, 1-002 representaria o segundo pedido enviado em janeiro.
Usando um TRIGGER eu gostaria que o incremento automático e o insert funcionassem de forma transparente.
Infelizmente, faz muito tempo desde que toquei em um procedimento armazenado e esta é minha primeira incursão no postgresql. Qualquer ajuda apontando na direção certa seria muito apreciada.
Atualização: Aqui está a implementação final usando o código de @ peterm
-- The trigger
CREATE TRIGGER add_order_number
BEFORE INSERT ON orders FOR EACH ROW
EXECUTE PROCEDURE order_number_update();
-- The trigger function
CREATE FUNCTION order_number_update() RETURNS TRIGGER AS $
DECLARE
next_order TEXT;
BEGIN
-- get the next order number
SELECT INTO next_order CONCAT(CAST(DATE_PART('MONTH', CURRENT_DATE) AS VARCHAR(2)),
'-',
LPAD(CAST(COALESCE(CAST(RIGHT(MAX(order_number), 3) AS INT), 0) + 1 AS VARCHAR(3)), 3, '0'))
FROM orders
WHERE CAST(LEFT(order_number, STRPOS(order_number, '-') - 1) AS INT) = DATE_PART('MONTH', CURRENT_DATE);
-- update the field
NEW.order_number = next_order;
RETURN NEW;
END;
$ LANGUAGE plpgsql;
Nota: Adicionando a atualização como um gatilho BEFORE INSERT, ela funciona de uma maneira completamente transparente e previsível, como faria com um campo SERIAL ou BIGSERIAL normal.