Stored procedures e functions no PostgreSQL

1. Introdução às Stored Procedures e Functions

Stored procedures e functions são blocos de código armazenados no banco de dados que encapsulam lógica de negócio diretamente no PostgreSQL. Embora ambos ofereçam reutilização e desempenho, existem diferenças conceituais fundamentais.

Uma function sempre retorna um valor (ou um conjunto) e pode ser usada em expressões SQL, como em cláusulas SELECT, WHERE ou JOIN. Uma procedure foi introduzida no PostgreSQL 11 e não exige retorno, sendo ideal para operações que envolvem transações, DML sem retorno ou chamadas que precisam de controle transacional explícito.

As vantagens são significativas: redução de tráfego entre aplicação e banco, centralização da lógica, maior segurança (usuários executam procedures sem acesso direto às tabelas) e desempenho superior, pois o código é compilado e otimizado pelo planner do PostgreSQL.

2. Sintaxe Básica e Estrutura

Bloco CREATE FUNCTION

CREATE OR REPLACE FUNCTION calcular_desconto(valor NUMERIC, percentual NUMERIC)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN valor * (1 - percentual / 100);
END;
$$;

Bloco CREATE PROCEDURE

CREATE OR REPLACE PROCEDURE atualizar_estoque(produto_id INT, quantidade INT)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE produtos SET estoque = estoque - quantidade WHERE id = produto_id;
    COMMIT;
END;
$$;

Parâmetros IN, OUT e INOUT

CREATE OR REPLACE FUNCTION dividir_numeros(
    IN a NUMERIC,
    IN b NUMERIC,
    OUT quociente NUMERIC,
    OUT resto NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    quociente := a / b;
    resto := a % b;
END;
$$;

Retorno com RETURNS TABLE

CREATE OR REPLACE FUNCTION clientes_por_cidade(cidade_nome TEXT)
RETURNS TABLE(id INT, nome TEXT, email TEXT)
LANGUAGE plpgsql
AS $$
BEGIN
    RETURN QUERY SELECT id, nome, email FROM clientes WHERE cidade = cidade_nome;
END;
$$;

3. Controle de Fluxo e Variáveis

Declaração e Condicionais

CREATE OR REPLACE FUNCTION classificar_produto(preco NUMERIC)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
    categoria TEXT;
BEGIN
    IF preco < 50 THEN
        categoria := 'Econômico';
    ELSIF preco BETWEEN 50 AND 200 THEN
        categoria := 'Padrão';
    ELSE
        categoria := 'Premium';
    END IF;
    RETURN categoria;
END;
$$;

Laços de Repetição

CREATE OR REPLACE FUNCTION gerar_sequencia(inicio INT, fim INT)
RETURNS TABLE(numero INT)
LANGUAGE plpgsql
AS $$
DECLARE
    contador INT := inicio;
BEGIN
    WHILE contador <= fim LOOP
        numero := contador;
        RETURN NEXT;
        contador := contador + 1;
    END LOOP;
END;
$$;

Tratamento de Exceções

CREATE OR REPLACE FUNCTION inserir_cliente_seguro(nome TEXT, email TEXT)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO clientes(nome, email) VALUES (nome, email);
    RETURN 'Sucesso';
EXCEPTION
    WHEN unique_violation THEN
        RETURN 'Email já cadastrado';
    WHEN OTHERS THEN
        RETURN 'Erro desconhecido: ' || SQLERRM;
END;
$$;

4. Trabalhando com Dados: DML e Cursores

DML dentro de Functions/Procedures

CREATE OR REPLACE PROCEDURE transferir_saldo(
    conta_origem INT,
    conta_destino INT,
    valor NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE contas SET saldo = saldo - valor WHERE id = conta_origem;
    UPDATE contas SET saldo = saldo + valor WHERE id = conta_destino;
END;
$$;

Cursores Explícitos

CREATE OR REPLACE FUNCTION processar_pedidos_pendentes()
RETURNS VOID
LANGUAGE plpgsql
AS $$
DECLARE
    pedido_cursor CURSOR FOR SELECT id, total FROM pedidos WHERE status = 'pendente';
    pedido_id INT;
    total_pedido NUMERIC;
BEGIN
    OPEN pedido_cursor;
    LOOP
        FETCH pedido_cursor INTO pedido_id, total_pedido;
        EXIT WHEN NOT FOUND;
        UPDATE pedidos SET status = 'processado' WHERE id = pedido_id;
    END LOOP;
    CLOSE pedido_cursor;
END;
$$;

Cursores Implícitos com FOR

CREATE OR REPLACE FUNCTION listar_pedidos_por_cliente(cliente_id INT)
RETURNS TABLE(pedido_id INT, data_pedido DATE, valor_total NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
    FOR pedido_id, data_pedido, valor_total IN
        SELECT id, data, total FROM pedidos WHERE cliente_id = cliente_id
    LOOP
        RETURN NEXT;
    END LOOP;
END;
$$;

5. Transações e Controle de Commit

Em functions, transações são atômicas: ou todo o bloco é executado ou nada é persistido. Procedures permitem controle transacional explícito com COMMIT e ROLLBACK.

CREATE OR REPLACE PROCEDURE processar_lote_com_commit()
LANGUAGE plpgsql
AS $$
BEGIN
    INSERT INTO log_operacoes(descricao) VALUES ('Início do lote');
    COMMIT;

    -- Operações parciais
    UPDATE produtos SET preco = preco * 1.1 WHERE categoria = 'eletrônicos';
    COMMIT;

    -- Se algo falhar, rollback parcial
    BEGIN
        INSERT INTO pedidos(cliente_id, total) VALUES (999, 1000);
    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK;
            INSERT INTO log_erros(mensagem) VALUES ('Falha ao inserir pedido');
            COMMIT;
    END;
END;
$$;

6. Funções Avançadas: Agregação e Trigger Functions

Função como Gatilho (Trigger Function)

CREATE OR REPLACE FUNCTION auditoria_alteracoes()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
    IF TG_OP = 'INSERT' THEN
        INSERT INTO log_auditoria(tabela, acao, dados_antigos, dados_novos, usuario)
        VALUES (TG_TABLE_NAME, TG_OP, NULL, row_to_json(NEW), current_user);
    ELSIF TG_OP = 'UPDATE' THEN
        INSERT INTO log_auditoria(tabela, acao, dados_antigos, dados_novos, usuario)
        VALUES (TG_TABLE_NAME, TG_OP, row_to_json(OLD), row_to_json(NEW), current_user);
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO log_auditoria(tabela, acao, dados_antigos, dados_novos, usuario)
        VALUES (TG_TABLE_NAME, TG_OP, row_to_json(OLD), NULL, current_user);
    END IF;
    RETURN NEW;
END;
$$;

Aplicando o Gatilho

CREATE TRIGGER trg_auditoria_clientes
AFTER INSERT OR UPDATE OR DELETE ON clientes
FOR EACH ROW EXECUTE FUNCTION auditoria_alteracoes();

7. Performance, Manutenção e Segurança

Cache de Planos de Execução

O PostgreSQL compila e armazena em cache o plano de execução de procedures e functions. Para evitar planos obsoletos, use EXPLAIN ANALYZE periodicamente.

EXPLAIN ANALYZE SELECT * FROM clientes_por_cidade('São Paulo');

Segurança com SECURITY DEFINER

CREATE OR REPLACE FUNCTION alterar_senha(usuario_id INT, nova_senha TEXT)
RETURNS BOOLEAN
SECURITY DEFINER
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE usuarios SET senha_hash = crypt(nova_senha, gen_salt('bf')) WHERE id = usuario_id;
    RETURN FOUND;
END;
$$;

SECURITY DEFINER executa com privilégios do criador (DBA), permitindo que usuários comuns alterem senhas sem acesso direto à tabela. SECURITY INVOKER (padrão) executa com privilégios do chamador.

Boas Práticas de Manutenção

  • Versionamento: use CREATE OR REPLACE FUNCTION e mantenha scripts em controle de versão.
  • Documentação: adicione comentários com COMMENT ON FUNCTION.
  • Evite lógica complexa dentro do banco se a aplicação puder fazer de forma mais eficiente.

8. Exemplos Práticos e Casos de Uso

Procedure para Inserção em Lote com Validação

CREATE OR REPLACE PROCEDURE inserir_pedidos_lote(pedidos_json JSONB)
LANGUAGE plpgsql
AS $$
DECLARE
    pedido RECORD;
BEGIN
    FOR pedido IN SELECT * FROM jsonb_to_recordset(pedidos_json)
        AS (cliente_id INT, valor NUMERIC, data_pedido DATE)
    LOOP
        IF pedido.valor <= 0 THEN
            RAISE EXCEPTION 'Valor inválido para pedido do cliente %', pedido.cliente_id;
        END IF;
        INSERT INTO pedidos(cliente_id, total, data) VALUES (pedido.cliente_id, pedido.valor, pedido.data_pedido);
    END LOOP;
    COMMIT;
END;
$$;

Function para Cálculo de Totais com Cursor

CREATE OR REPLACE FUNCTION calcular_total_vendas_periodo(data_inicio DATE, data_fim DATE)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
DECLARE
    total NUMERIC := 0;
    pedido_cursor CURSOR FOR SELECT total FROM pedidos WHERE data BETWEEN data_inicio AND data_fim AND status = 'concluído';
    valor_pedido NUMERIC;
BEGIN
    OPEN pedido_cursor;
    LOOP
        FETCH pedido_cursor INTO valor_pedido;
        EXIT WHEN NOT FOUND;
        total := total + valor_pedido;
    END LOOP;
    CLOSE pedido_cursor;
    RETURN total;
END;
$$;

Comparação: Function vs Procedure vs View Materializada

Recurso Function Procedure View Materializada
Retorno Obrigatório Opcional Dados armazenados
Transações Atômicas Controle explícito Atualização periódica
Uso em SELECT Sim Não Sim
DML complexo Limitado Completo Limitado

Escolha function quando precisar de um valor computado em consultas, procedure para operações transacionais complexas, e view materializada para dados agregados que mudam com pouca frequência.

Referências