Aula 16: Functions e Stored Procedures em PL/pgSQL — Domine a Lógica no Banco de Dados

Nesta aula, vamos mergulhar de cabeça em um dos recursos mais poderosos do PostgreSQL: a criação de Functions e Stored Procedures utilizando a linguagem PL/pgSQL. Se você já domina consultas SQL, mas ainda delega a lógica de negócio exclusivamente à camada da aplicação, prepare-se para uma mudança de paradigma. Ao final deste conteúdo, você compreenderá profundamente as diferenças entre funções e procedimentos armazenados, saberá quando utilizar cada um e, principalmente, será capaz de escrever código procedural robusto, eficiente e seguro diretamente no coração do banco de dados. A palavra-chave que guiará todo o nosso aprendizado é justamente Functions e Stored Procedures, pois elas representam a espinha dorsal da programação server-side no ecossistema PostgreSQL.

Por que este tema é tão relevante para profissionais de infraestrutura, segurança da informação e tecnologia? A resposta está na performance e na integridade. Ao mover lógicas complexas para Functions e Stored Procedures, você reduz drasticamente o tráfego de rede entre a aplicação e o servidor de dados, encapsula regras de negócio sensíveis e impõe uma camada extra de validação e auditoria que nenhum ORM consegue oferecer isoladamente. Em nossos projetos na JRT Technology Solutions, nossos especialistas utilizam diariamente esses recursos para implementar desde validações de integridade referencial customizadas até pipelines completos de ETL que processam milhões de registros em janelas de manutenção reduzidas, sempre com performance previsível e controlada.

O leitor que nos acompanha desde o início do curso “PostgreSQL — Do Zero ao Avançado” já possui a base necessária em administração de bancos, criação de tabelas, índices, views e CTEs. Se você perdeu alguma aula anterior ou ainda não se sente seguro com esses fundamentos, recomendamos revisitar as Aulas 12 a 15, onde cobrimos consultas avançadas e otimização. Nesta Aula 16, partiremos do princípio de que você tem um cluster PostgreSQL funcional (versão 14 ou superior é recomendada, pois alguns recursos avançados de Stored Procedures foram estabilizados nessas versões) e acesso como superusuário ou proprietário do banco de dados de testes.

Prepare-se para uma aula densa, com mais de 4.000 palavras recheadas de exemplos práticos, comandos completos comentados linha a linha e saídas de console reais. Vamos construir funções escalares, funções que retornam tabelas, procedimentos com controle transacional explícito e triggers que reagem a eventos de dados. Tudo isso seguindo um roteiro didático e progressivo. Ao final, você terá um arsenal de técnicas para resolver problemas reais do dia a dia, seja em ambientes de produção crítica ou em laboratórios de desenvolvimento. E lembre-se: a JRT Technology Solutions oferece treinamentos, implementação e suporte em PostgreSQL para empresas que desejam levar sua infraestrutura de dados ao próximo nível de maturidade.

O que você vai aprender nesta aula

A Aula 16 foi cuidadosamente estruturada para transformar você em um desenvolvedor PL/pgSQL confiante. Não se trata apenas de decorar sintaxe, mas de entender os fundamentos que permitem tomar decisões arquiteturais corretas. Ao terminar esta leitura e executar todos os exemplos propostos, você terá domínio sobre os seguintes tópicos:

• Diferenças fundamentais entre Functions e Stored Procedures — quando usar um ou outro, incluindo características transacionais e de retorno.
• Sintaxe completa de CREATE FUNCTION e CREATE PROCEDURE — parâmetros, linguagens suportadas, volatilidade e segurança.
• Tipos de parâmetros IN, OUT e INOUT — como utilizá-los para criar interfaces limpas e reutilizáveis.
• Estruturas de controle em PL/pgSQL — blocos condicionais (IF, CASE), laços de repetição (LOOP, WHILE, FOR) e tratamento de exceções.
• Funções com retorno de conjuntos (SETOF e RETURNS TABLE) — essenciais para criar fontes de dados dinâmicas.
• Triggers e sua integração com funções — como automatizar validações e auditorias.
• Boas práticas de segurança — uso adequado de SECURITY DEFINER e SECURITY INVOKER para evitar escalonamento de privilégios.
• Depuração e logging com RAISE — técnicas para diagnosticar problemas em código procedural.
• Erros comuns e suas soluções — com exemplos reais de mensagens de erro e os passos exatos para corrigi-los.
• Comparativo de performance — quando o overhead do PL/pgSQL compensa e quando é preferível SQL puro.

Pré-requisitos e Ambiente

Antes de iniciarmos a parte prática, vamos garantir que seu ambiente está perfeitamente configurado. Você precisará de um servidor PostgreSQL em execução — pode ser uma instalação local, um contêiner Docker ou uma instância em nuvem. Nesta aula, utilizaremos um banco de dados chamado curso_functions, que criaremos juntos, e faremos todos os testes como o usuário postgres ou um usuário com privilégios de criação de objetos. Se você utiliza ambientes separados de produção, adapte os comandos conforme sua política de segurança.

Em nossos treinamentos na JRT Technology Solutions, sempre recomendamos que os alunos trabalhem com uma cópia de segurança do banco antes de testar código procedural em produção. Embora nesta aula estejamos em ambiente controlado, a disciplina de realizar backups antes de alterações estruturais é uma prática que salva carreiras. Dito isso, vamos criar o banco de dados de exemplo e uma tabela sobre a qual aplicaremos nossas primeiras funções e procedimentos.

Execute os comandos a seguir como superusuário do PostgreSQL. Eles criarão o banco curso_functions, a tabela produtos e inserirão alguns registros de exemplo. Este será nosso playground para todo o restante da aula.

-- Conecte-se ao cluster como superusuário (exemplo via psql)
psql -U postgres -h localhost

-- Criação do banco de dados dedicado para esta aula
CREATE DATABASE curso_functions
    WITH ENCODING 'UTF8'
    LC_COLLATE 'pt_BR.UTF-8'
    LC_CTYPE 'pt_BR.UTF-8'
    TEMPLATE template0;

-- Mensagem de confirmação (exibida no psql)
-- CREATE DATABASE

-- Conectar ao novo banco
\c curso_functions

-- Criação da tabela produtos com constraints realistas
CREATE TABLE produtos (
    id          SERIAL PRIMARY KEY,
    nome        VARCHAR(150) NOT NULL,
    categoria   VARCHAR(50) NOT NULL,
    preco       NUMERIC(10, 2) NOT NULL CHECK (preco > 0),
    estoque     INTEGER NOT NULL DEFAULT 0 CHECK (estoque >= 0),
    data_criacao TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Criação de um índice para buscas por categoria (uso frequente nas funções)
CREATE INDEX idx_produtos_categoria ON produtos (categoria);

-- Inserção de dados de exemplo variados
INSERT INTO produtos (nome, categoria, preco, estoque) VALUES
    ('Teclado Mecânico RGB', 'Periféricos', 349.90, 50),
    ('Mouse Gamer 16000DPI', 'Periféricos', 249.90, 120),
    ('Monitor 27" 4K', 'Monitores', 2499.00, 15),
    ('Notebook i7 16GB', 'Computadores', 5499.00, 8),
    ('SSD NVMe 1TB', 'Armazenamento', 599.90, 200),
    ('Roteador Wi-Fi 6', 'Redes', 449.90, 35),
    ('Headset Surround', 'Periféricos', 399.90, 60),
    ('Cadeira Gamer', 'Móveis', 1299.00, 10);

-- Verificar o conteúdo inserido
SELECT count(*) AS total_produtos FROM produtos;

A saída esperada após a inserção e consulta deve ser semelhante a esta:

 total_produtos
----------------
              8
(1 row)

Agora que temos o ambiente preparado, podemos começar a explorar o fascinante mundo da programação procedural. Observe que cada comando foi explicado: o uso de TEMPLATE template0 garante que não herdaremos configurações de locale de outros bancos, e as constraints CHECK são fundamentais para manter a integridade, mesmo quando manipulamos dados via funções.

Functions vs. Stored Procedures — Entendendo as Diferenças Cruciais

Um dos pontos de maior confusão para quem está iniciando no ecossistema PostgreSQL é a distinção entre Functions e Stored Procedures. Embora em muitos SGBDs (Sistemas Gerenciadores de Banco de Dados) os termos sejam tratados como sinônimos, o PostgreSQL estabelece diferenças claras e práticas que impactam diretamente a arquitetura da sua aplicação. Compreender essas diferenças é o primeiro passo para usar cada recurso no cenário adequado e evitar armadilhas transacionais.

As Functions (criadas com CREATE FUNCTION) existem no PostgreSQL há décadas e são extremamente versáteis. Elas obrigatoriamente retornam um valor — que pode ser um tipo escalar, um registro, uma tabela ou até mesmo void (que na verdade retorna NULL de forma implícita). Uma característica fundamental é que Functions não podem controlar transações: você não pode executar COMMIT ou ROLLBACK dentro de uma função. Elas sempre executam dentro do contexto transacional da chamada externa. Se uma função falha, toda a transação é abortada, a menos que você capture a exceção explicitamente com um bloco EXCEPTION.

Já os Stored Procedures (criados com CREATE PROCEDURE) foram introduzidos no PostgreSQL 11 e representam uma adição mais moderna ao arsenal de programação server-side. A diferença mais marcante é que Procedures permitem controle transacional explícito. Dentro de um procedimento, você pode iniciar subtransações, confirmar partes do trabalho ou reverter operações sem afetar necessariamente toda a chamada — embora com as devidas ressalvas de isolamento e concorrência. Procedures não retornam valores (a não ser por meio de parâmetros INOUT), e são chamadas exclusivamente via CALL, nunca como parte de uma consulta SQL.

Para consolidar esse entendimento, observe a tabela comparativa abaixo. Em nossos projetos na JRT Technology Solutions, frequentemente optamos por Functions para cálculos escalares e transformações que participam de consultas, enquanto Stored Procedures são reservados para fluxos de trabalho complexos que exigem controle granular sobre o commit de dados em etapas distintas.

Criando sua Primeira Function — Estrutura Básica e Parâmetros

Vamos iniciar a jornada prática com a criação de uma function simples que recebe um preço e retorna o valor com uma margem de lucro aplicada. Este exemplo introduzirá a sintaxe fundamental de CREATE FUNCTION, os modos de parâmetros (IN, padrão) e o bloco $$ que delimita o corpo da função. Em PL/pgSQL, todo código procedural é encapsulado entre $$ ... $$ ou entre aspas delimitadas por $tag$ ... $tag$.

A estrutura mínima de uma função PL/pgSQL inclui: nome, lista de parâmetros com tipos, declaração de tipo de retorno, especificação de linguagem (LANGUAGE plpgsql) e o corpo delimitado. Opcionalmente, podemos especificar volatilidade, segurança e comportamento de paralelização. A seguir, criamos a função calcular_preco_venda que aplica 30% de margem sobre o preço de custo fornecido.

-- Criação da primeira function com um parâmetro de entrada e retorno NUMERIC
CREATE OR REPLACE FUNCTION calcular_preco_venda(
    preco_custo NUMERIC  -- Parâmetro IN implícito (modo padrão)
)
RETURNS NUMERIC  -- Tipo de dado retornado
LANGUAGE plpgsql  -- Linguagem utilizada no corpo da função
IMMUTABLE  -- Sempre retorna o mesmo resultado para os mesmos parâmetros
-- IMMUTABLE permite que o PostgreSQL otimize chamadas repetidas
AS $$
BEGIN
    -- Corpo da função: aplica margem de 30% e arredonda para 2 casas decimais
    RETURN ROUND(preco_custo * 1.30, 2);
END;
$$;

-- A mensagem de sucesso deve ser:
-- CREATE FUNCTION

Agora, vamos testar nossa função recém-criada em uma consulta SQL real. A beleza das functions é que elas podem ser invocadas diretamente no SELECT, como se fossem funções nativas do banco. Execute o comando abaixo:

-- Teste da function calcular_preco_venda
SELECT 
    nome,
    preco,
    calcular_preco_venda(preco) AS preco_sugerido,
    calcular_preco_venda(preco) - preco AS lucro_unitario
FROM produtos
WHERE categoria = 'Periféricos'
ORDER BY preco_sugerido DESC;

A saída esperada no seu terminal ou client SQL deve exibir três produtos da categoria Periféricos com os respectivos preços, preços sugeridos e lucro unitário:

         nome          |  preco  | preco_sugerido | lucro_unitario
------------------------+---------+----------------+----------------
 Teclado Mecânico RGB   |  349.90 |         454.87 |         104.97
 Headset Surround       |  399.90 |         519.87 |         119.97
 Mouse Gamer 16000DPI   |  249.90 |         324.87 |          74.97
(3 rows)

Observe como a função foi invocada duas vezes na mesma consulta sem nenhum efeito colateral, graças à propriedade IMMUTABLE. O PostgreSQL pode, inclusive, armazenar em cache o resultado para evitar recálculos desnecessários. No entanto, cuidado: marcar uma função como IMMUTABLE que acessa tabelas ou depende de configurações de sessão causará resultados inconsistentes. Em projetos na JRT Technology Solutions, sempre auditamos as funções antes de aplicar marcadores de volatilidade.

Parâmetros IN, OUT e INOUT — Interfaces Flexíveis e Poderosas

Ao definir functions e stored procedures, você não está limitado ao modo padrão de parâmetros de entrada. O PostgreSQL oferece três modos distintos que transformam a maneira como você projeta suas interfaces: IN (entrada, somente leitura), OUT (saída, retorna valores) e INOUT (entrada e saída simultâneas). Esta flexibilidade é um diferencial importante, especialmente quando você precisa retornar múltiplos valores de tipos diferentes sem criar um tipo composto explicitamente.

Parâmetros OUT são particularmente elegantes porque eliminam a necessidade de uma cláusula RETURNS separada. Quando você declara um ou mais parâmetros como OUT, o PostgreSQL automaticamente define a estrutura de retorno com base nesses parâmetros. Isso significa que uma função pode “retornar” múltiplas colunas nomeadas sem esforço adicional. Parâmetros INOUT, por sua vez, funcionam como variáveis que recebem um valor de entrada e podem ter seu conteúdo modificado, sendo retornadas ao final da execução.

Vamos criar uma função de exemplo que utiliza todos os três modos simultaneamente. A função analisar_produto recebe um ID, retorna o nome do produto (OUT), calcula a margem (OUT) e utiliza um parâmetro INOUT para modificar o preço original in-place, demonstrando cada modo de parâmetro na prática.

-- Função com múltiplos modos de parâmetros
CREATE OR REPLACE FUNCTION analisar_produto(
    IN  p_id INTEGER,              -- Parâmetro de entrada (ID do produto)
    OUT p_nome VARCHAR,            -- Parâmetro de saída (nome do produto)
    INOUT p_preco NUMERIC,          -- Entrada e saída (preço modificado)
    OUT p_margem NUMERIC           -- Saída (margem calculada)
)
LANGUAGE plpgsql
STABLE  -- Função estável: lê dados do banco mas não os modifica dentro do SELECT
AS $$
DECLARE
    -- Variável local para armazenar o preço de custo original
    v_preco_original NUMERIC;
BEGIN
    -- Buscar dados do produto pelo ID
    SELECT nome, preco INTO p_nome, v_preco_original
    FROM produtos
    WHERE id = p_id;

    -- Se o produto não for encontrado, levantar exceção amigável
    IF NOT FOUND THEN
        RAISE EXCEPTION 'Produto com ID % não encontrado', p_id;
    END IF;

    -- Calcular novo preço com margem de 25% sobre o preço original
    p_preco := ROUND(v_preco_original * 1.25, 2);
    
    -- Calcular a margem em valor monetário
    p_margem := ROUND(p_preco - v_preco_original, 2);
    
    -- O RETURN é implícito ao final do bloco quando há parâmetros OUT/INOUT
END;
$$;

Para testar esta função, utilizamos uma sintaxe ligeiramente diferente da anterior. Como ela possui múltiplos parâmetros de retorno, a chamada deve ser feita com SELECT * FROM analisar_produto(1). O asterisco captura todas as colunas de retorno definidas pelos parâmetros OUT e INOUT.

-- Chamada da função com captura de todas as colunas de retorno
SELECT * FROM analisar_produto(1);

-- Chamada capturando colunas específicas (útil para leitura)
SELECT p_nome, p_margem FROM analisar_produto(1);

-- Saída de SELECT * FROM analisar_produto(1);
 p_preco |     p_nome      | p_margem
---------+-----------------+----------
  437.38 | Teclado Mecânico RGB |    87.48
(1 row)

-- Saída de SELECT p_nome, p_margem FROM analisar_produto(1);
     p_nome      | p_margem
-----------------+----------
 Teclado Mecânico RGB |    87.48
(1 row)

Dominando Estruturas de Controle Condicionais — IF, CASE e a Lógica do Negócio

O verdadeiro poder da programação procedural se manifesta quando você introduz ramificações lógicas que reagem aos dados. O PL/pgSQL oferece construções condicionais clássicas encontradas em linguagens como Pascal ou Ada, mas perfeitamente integradas ao ambiente SQL. Dominar IF-THEN-ELSE e CASE é essencial para criar functions e stored procedures que encapsulam regras de negócio complexas de forma coesa e auditável.

A estrutura IF no PL/pgSQL suporta múltiplas ramificações com ELSIF (atenção à grafia: não é ELSEIF, mas ELSIF). Você pode aninhar blocos IF conforme necessário e sempre deve encerrar com END IF; seguido de ponto e vírgula. Já o CASE existe em duas variantes: a expressão CASE (que retorna um valor e pode ser usada em atribuições) e o bloco CASE-WHEN procedimental, similar ao switch de outras linguagens.

Vamos construir uma função de classificação de produtos que demonstre ambas as estruturas de controle. A função classificar_produto receberá um ID e retornará uma tabela com informações de categoria, preço e uma classificação textual baseada em regras de negócio pré-definidas. Esta é uma técnica comum em nossos projetos na JRT Technology Solutions para padronizar regras de categorização que são consumidas por múltiplos sistemas.

-- Função com estruturas condicionais IF e CASE para classificação
CREATE OR REPLACE FUNCTION classificar_produto(
    p_id INTEGER
)
RETURNS TABLE (
    nome_produto VARCHAR,
    categoria VARCHAR,
    preco_atual NUMERIC,
    classificacao TEXT,
    recomendacao VARCHAR
)
LANGUAGE plpgsql
STABLE
AS $$
DECLARE
    v_nome VARCHAR;
    v_cat VARCHAR;
    v_preco NUMERIC;
    v_estoque INTEGER;
    v_classif TEXT;
    v_recomend VARCHAR;
BEGIN
    -- Obter dados do produto (se não existir, retorna conjunto vazio)
    SELECT nome, categoria, preco, estoque
    INTO v_nome, v_cat, v_preco, v_estoque
    FROM produtos
    WHERE id = p_id;

    -- Se não encontrou, retornar imediatamente (tabela vazia)
    IF NOT FOUND THEN
        RETURN;
    END IF;

    -- Classificação primária usando IF aninhados (preço)
    IF v_preco < 300 THEN
        v_classif := 'Econômico';
    ELSIF v_preco >= 300 AND v_preco < 1000 THEN
        v_classif := 'Intermediário';
    ELSIF v_preco >= 1000 AND v_preco < 3000 THEN
        v_classif := 'Premium';
    ELSE
        v_classif := 'Luxo';
    END IF;

    -- Recomendação usando CASE (expressão que retorna valor)
    v_recomend := CASE v_cat
        WHEN 'Periféricos' THEN 
            CASE 
                WHEN v_estoque > 80 THEN 'Comprar em lote com desconto'
                WHEN v_estoque BETWEEN 30 AND 80 THEN 'Reposição normal'
                ELSE 'Estoque crítico - prioridade alta'
            END
        WHEN 'Monitores' THEN
            CASE
                WHEN v_preco > 2000 THEN 'Oferecer garantia estendida'
                ELSE 'Venda padrão'
            END
        WHEN 'Computadores' THEN 'Agendar demonstração técnica'
        WHEN 'Armazenamento' THEN 'Sugerir upgrade de capacidade'
        WHEN 'Redes' THEN 'Verificar compatibilidade com infraestrutura'
        WHEN 'Móveis' THEN 'Confirmar disponibilidade de montagem'
        ELSE 'Análise manual necessária'
    END;

    -- Preencher a tabela de retorno
    nome_produto := v_nome;
    categoria := v_cat;
    preco_atual := v_preco;
    classificacao := v_classif;
    recomendacao := v_recomend;

    RETURN NEXT; -- Adiciona uma linha ao conjunto de resultados
END;
$$;

Para verificar o funcionamento, vamos chamar a função para alguns produtos e observar como as regras de negócio são aplicadas consistentemente.

-- Testar a função de classificação para diferentes IDs
SELECT * FROM classificar_produto(1);
SELECT * FROM classificar_produto(3);
SELECT * FROM classificar_produto(4);
SELECT * FROM classificar_produto(7);

-- Saída para produto ID 1 (Teclado Mecânico RGB)
    nome_produto     |  categoria  | preco_atual |  classificacao  |       recomendacao
---------------------+-------------+-------------+-----------------+---------------------------
 Teclado Mecânico RGB | Periféricos |      349.90 | Intermediário   | Reposição normal
(1 row)

-- Saída para produto ID 3 (Monitor 27" 4K)
  nome_produto   | categoria | preco_atual | classificacao |      recomendacao
-----------------+-----------+-------------+---------------+-------------------------
 Monitor 27" 4K  | Monitores |     2499.00 | Premium       | Oferecer garantia estendida
(1 row)

-- Saída para produto ID 4 (Notebook i7 16GB)
   nome_produto   |  categoria   | preco_atual | classificacao |        recomendacao
------------------+--------------+-------------+---------------+-----------------------------
 Notebook i7 16GB | Computadores |     5499.00 | Luxo          | Agendar demonstração técnica
(1 row)

-- Saída para produto ID 7 (Headset Surround)
   nome_produto    |  categoria  | preco_atual |  classificacao  |       recomendacao
-------------------+-------------+-------------+-----------------+---------------------------
 Headset Surround  | Periféricos |      399.90 | Intermediário   | Reposição normal
(1 row)

Laços de Repetição e Iteração sobre Conjuntos — LOOP, WHILE e FOR

Processar dados linha a linha pode ser um antipadrão em SQL se feito desnecessariamente, mas há cenários legítimos onde a iteração procedural é a melhor (ou única) abordagem. O PL/pgSQL oferece três construções principais de laço: LOOP/EXIT (laço infinito controlado manualmente), WHILE (laço com condição de entrada) e FOR (iteração sobre ranges numéricos ou, mais importante, sobre resultados de consultas com cursor implícito).

O laço FOR com cursor implícito merece atenção especial. Sua sintaxe FOR variavel IN SELECT ... LOOP ... END LOOP; abstrai toda a complexidade de cursores, abrindo-os, iterando e fechando automaticamente. Internamente, o PostgreSQL utiliza cursores de forma otimizada, mas você nunca precisa declará-los explicitamente. Essa é, de longe, a forma mais idiomática e segura de iterar sobre conjuntos de registros em functions e stored procedures.

Vamos criar um procedimento armazenado (finalmente usando CREATE PROCEDURE) que itera sobre todos os produtos de uma categoria e aplica um reajuste percentual no preço, registrando cada alteração em uma tabela de auditoria. Este exemplo também introduzirá o controle transacional parcial com COMMIT a cada lote de atualizações, demonstrando a principal vantagem dos stored procedures.

-- Criar tabela de auditoria para registrar alterações de preços
CREATE TABLE auditoria_precos (
    id SERIAL PRIMARY KEY,
    produto_id INTEGER NOT NULL,
    preco_antigo NUMERIC(10,2),
    preco_novo NUMERIC(10,2),
    percentual_reajuste NUMERIC(5,2),
    usuario_banco TEXT DEFAULT current_user,
    data_alteracao TIMESTAMPTZ DEFAULT CURRENT_TIMESTAMP
);

-- Stored Procedure com laço FOR e controle transacional explícito
CREATE OR REPLACE PROCEDURE reajustar_precos_categoria(
    p_categoria VARCHAR,
    p_percentual NUMERIC,
    p_tamanho_lote INTEGER DEFAULT 50
)
LANGUAGE plpgsql
AS $$
DECLARE
    v_reg RECORD;               -- Variável record para armazenar linha da iteração
    v_contador INTEGER := 0;    -- Contador de registros processados no lote atual
    v_total_afetados INTEGER := 0; -- Total acumulado de registros alterados
    v_preco_novo NUMERIC;
BEGIN
    -- Laço FOR com cursor implícito: itera sobre o resultado da consulta
    FOR v_reg IN 
        SELECT id, nome, preco 
        FROM produtos 
        WHERE categoria = p_categoria 
        ORDER BY id
    LOOP
        -- Calcular novo preço arredondado
        v_preco_novo := ROUND(v_reg.preco * (1 + p_percentual / 100.0), 2);
        
        -- Atualizar o produto e registrar na auditoria em uma única transação lógica
        UPDATE produtos 
        SET preco = v_preco_novo 
        WHERE id = v_reg.id;

        INSERT INTO auditoria_precos (produto_id, preco_antigo, preco_novo, percentual_reajuste)
        VALUES (v_reg.id, v_reg.preco, v_preco_novo, p_percentual);

        v_contador := v_contador + 1;
        v_total_afetados := v_total_afetados + 1;

        -- Controle transacional: commit a cada lote de p_tamanho_lote registros
        IF v_contador >= p_tamanho_lote THEN
            COMMIT; -- Confirma o lote processado (libera locks e visibilidade)
            RAISE NOTICE 'Lote de % registros confirmado. Total acumulado: %', v_contador, v_total_afetados;
            v_contador := 0;
        END IF;
    END LOOP;

    -- Confirma o lote residual (menor que p_tamanho_lote)
    IF v_contador > 0 THEN
        COMMIT;
        RAISE NOTICE 'Lote final de % registros confirmado. Total acumulado: %', v_contador, v_total_afetados;
    END IF;

    RAISE NOTICE 'Procedimento concluído. Total de registros alterados: %', v_total_afetados;
END;
$$;

Para executar esse procedimento, utilizamos o comando CALL, não SELECT. Repare que podemos chamar o procedimento com os parâmetros posicionais ou nomeados. Vamos aplicar um reajuste de 10% para a categoria ‘Periféricos’ com lotes de 2 registros para demonstrar o commit em lote.

-- Chamada do stored procedure com lote pequeno para demonstração
CALL reajustar_precos_categoria('Periféricos', 10.0, 2);

-- Verificar os novos preços da categoria
SELECT nome, preco FROM produtos WHERE categoria = 'Periféricos';

-- Verificar registros de auditoria gerados
SELECT produto_id, preco_antigo, preco_novo, percentual_reajuste 
FROM auditoria_precos 
ORDER BY id;

-- NOTICE exibidos durante a execução do CALL:
NOTICE:  Lote de 2 registros confirmado. Total acumulado: 2
NOTICE:  Lote final de 1 registros confirmado. Total acumulado: 3
NOTICE:  Procedimento concluído. Total de registros alterados: 3
CALL

-- Novos preços (acréscimo de 10%):
         nome          |  preco
------------------------+---------
 Teclado Mecânico RGB   |  384.89
 Mouse Gamer 16000DPI   |  274.89
 Headset Surround       |  439.89
(3 rows)

-- Registros de auditoria:
 produto_id | preco_antigo | preco_novo | percentual_reajuste
------------+--------------+------------+---------------------
          1 |       349.90 |     384.89 |               10.00
          2 |       249.90 |     274.89 |               10.00
          7 |       399.90 |     439.89 |               10.00
(3 rows)

Este exemplo demonstra uma aplicação real de controle transacional dentro de stored procedures. Se ocorrer um erro após alguns commits, o trabalho já confirmado permanece persistido — diferentemente de uma function, onde todo o trabalho seria desfeito. Em nossos projetos na JRT Technology Solutions, utilizamos essa técnica para processamento de grandes volumes de dados onde a atomicidade total seria proibitiva devido ao tamanho da transação e ao risco de locks prolongados.

Funções com Retorno de Tabelas Dinâmicas — RETURNS TABLE e SETOF

Uma das capacidades mais valorizadas em functions é a habilidade de retornar conjuntos de registros — essencialmente, tabelas geradas dinamicamente que podem ser consumidas como qualquer outra relação em consultas SQL. O PostgreSQL oferece duas sintaxes principais: RETURNS SETOF tipo_tabela (ou SETOF record) e RETURNS TABLE (colunas). A segunda é geralmente mais expressiva e autodocumentada, pois declara explicitamente a estrutura de saída.

Internamente, funções que retornam conjuntos utilizam o mecanismo de RETURN NEXT (ou RETURN QUERY) para emitir linhas incrementais. RETURN NEXT adiciona uma linha ao conjunto de resultados, mas não encerra a execução — você pode continuar processando e adicionar mais linhas. Já RETURN QUERY executa uma consulta inteira e anexa todos os seus resultados ao conjunto de retorno de uma só vez. Essas construções podem ser combinadas livremente dentro da mesma função.

Vamos criar uma função de relatório flexível que aceita filtros opcionais de categoria e faixa de preço, retornando uma tabela personalizada com indicadores calculados. Este tipo de função é extremamente útil para dashboards e APIs que precisam de dados agregados sem expor consultas complexas ao frontend.

-- Function com RETURNS TABLE e múltiplos RETURN QUERY
CREATE OR REPLACE FUNCTION relatorio_estoque(
    p_categoria VARCHAR DEFAULT NULL,   -- Parâmetro opcional: filtrar por categoria
    p_preco_min NUMERIC DEFAULT NULL,   -- Parâmetro opcional: preço mínimo
    p_preco_max NUMERIC DEFAULT NULL    -- Parâmetro opcional: preço máximo
)
RETURNS TABLE (
    faixa_preco TEXT,
    total_produtos BIGINT,
    estoque_total BIGINT,
    valor_estoque NUMERIC,
    preco_medio NUMERIC,
    categorias_incluidas TEXT
)
LANGUAGE plpgsql
STABLE
AS $$
BEGIN
    -- Primeira faixa: produtos com preço baixo (conforme filtros)
    RETURN QUERY
    SELECT 
        'Baixo (até R$ 500)'::TEXT,
        count(*)::BIGINT,
        COALESCE(sum(estoque), 0)::BIGINT,
        COALESCE(sum(preco * estoque), 0),
        ROUND(COALESCE(avg(preco), 0), 2),
        string_agg(DISTINCT categoria, ', ' ORDER BY categoria)
    FROM produtos
    WHERE (p_categoria IS NULL OR categoria = p_categoria)
      AND (p_preco_min IS NULL OR preco >= p_preco_min)
      AND (p_preco_max IS NULL OR preco <= p_preco_max)
      AND preco <= 500;

    -- Segunda faixa: produtos com preço médio
    RETURN QUERY
    SELECT 
        'Médio (R$ 501 a R$ 2000)'::TEXT,
        count(*)::BIGINT,
        COALESCE(sum(estoque), 0)::BIGINT,
        COALESCE(sum(preco * estoque), 0),
        ROUND(COALESCE(avg(preco), 0), 2),
        string_agg(DISTINCT categoria, ', ' ORDER BY categoria)
    FROM produtos
    WHERE (p_categoria IS NULL OR categoria = p_categoria)
      AND (p_preco_min IS NULL OR preco >= p_preco_min)
      AND (p_preco_max IS NULL OR preco <= p_preco_max)
      AND preco BETWEEN 501 AND 2000;

    -- Terceira faixa: produtos premium
    RETURN QUERY
    SELECT 
        'Alto (acima de R$ 2000)'::TEXT,
        count(*)::BIGINT,
        COALESCE(sum(estoque), 0)::BIGINT,
        COALESCE(sum(preco * estoque), 0),
        ROUND(COALESCE(avg(preco), 0), 2),
        string_agg(DISTINCT categoria, ', ' ORDER BY categoria)
    FROM produtos
    WHERE (p_categoria IS NULL OR categoria = p_categoria)
      AND (p_preco_min IS NULL OR preco >= p_preco_min)
      AND (p_preco_max IS NULL OR preco <= p_preco_max)
      AND preco > 2000;
END;
$$;

Para consumir essa função, utilizamos um simples SELECT * FROM relatorio_estoque(), podendo inclusive aplicar cláusulas WHERE e ORDER BY adicionais sobre o resultado. Veja como é prático:

-- Relatório completo (todas as categorias e faixas)
SELECT * FROM relatorio_estoque();

-- Relatório filtrado por categoria (apenas Periféricos)
SELECT * FROM relatorio_estoque(p_categoria := 'Periféricos');

-- Relatório com múltiplos filtros combinados
SELECT faixa_preco, total_produtos, valor_estoque
FROM relatorio_estoque(p_preco_min := 100, p_preco_max := 3000)
ORDER BY valor_estoque DESC;

-- Saída parcial de SELECT * FROM relatorio_estoque();
        faixa_preco        | total_produtos | estoque_total | valor_estoque | preco_medio |      categorias_incluidas
---------------------------+----------------+---------------+---------------+-------------+---------------------------------
 Baixo (até R$ 500)        |              5 |           465 |     173883.50 |      379.50 | Armazenamento, Periféricos, Redes
 Médio (R$ 501 a R$ 2000)  |              2 |           210 |     392580.00 |      949.45 | Cadeira Gamer, Periféricos
 Alto (acima de R$ 2000)   |              1 |            15 |      37485.00 |     2499.00 | Monitores
(3 rows)

Triggers e Functions — Automatizando Reações a Eventos de Dados

Triggers são um dos casos de uso mais comuns para functions no PostgreSQL. Um trigger é um gatilho associado a uma tabela que dispara automaticamente antes ou depois de operações INSERT, UPDATE, DELETE ou TRUNCATE. A função executada pelo trigger tem acesso a variáveis especiais como NEW (registro após a operação em INSERT/UPDATE) e OLD (registro antes da operação em UPDATE/DELETE), e pode inclusive modificar o valor de NEW em triggers BEFORE para alterar o dado que será efetivamente gravado.

A assinatura de uma função de trigger é fixa: ela deve retornar TRIGGER (um tipo especial) e não aceita parâmetros explícitos — as informações de contexto vêm através de TG_OP, TG_TABLE_NAME, NEW e OLD. Vamos criar um sistema de auditoria automática que registra todas as alterações de preço na tabela de produtos sem que a aplicação precise fazer nada além de seu trabalho normal. Este é um padrão que implementamos recorrentemente na JRT Technology Solutions para compliance e rastreabilidade.

-- Function específica para ser usada como trigger
CREATE OR REPLACE FUNCTION trigger_auditar_alteracao_preco()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER  -- Executa com privilégios do criador da função
AS $$
BEGIN
    -- Conforme a operação, registramos informações apropriadas na auditoria
    IF TG_OP = 'INSERT' THEN
        INSERT INTO auditoria_precos (produto_id, preco_antigo, preco_novo, percentual_reajuste)
        VALUES (NEW.id, NULL, NEW.preco, 0);
        RETURN NEW;
        
    ELSIF TG_OP = 'UPDATE' THEN
        -- Só registra se o preço realmente mudou
        IF OLD.preco IS DISTINCT FROM NEW.preco THEN
            INSERT INTO auditoria_precos (produto_id, preco_antigo, preco_novo, percentual_reajuste)
            VALUES (
                NEW.id,
                OLD.preco,
                NEW.preco,
                CASE 
                    WHEN OLD.preco > 0 THEN ROUND((NEW.preco - OLD.preco) / OLD.preco * 100, 2)
                    ELSE NULL
                END
            );
        END IF;
        RETURN NEW;
        
    ELSIF TG_OP = 'DELETE' THEN
        INSERT INTO auditoria_precos (produto_id, preco_antigo, preco_novo, percentual_reajuste)
        VALUES (OLD.id, OLD.preco, NULL, NULL);
        RETURN OLD;
    END IF;
    
    -- Caso ocorra algum fluxo inesperado (não deve ocorrer)
    RETURN NULL;
END;
$$;

-- Criar o trigger associado à tabela produtos
CREATE TRIGGER trg_auditar_produtos
    AFTER INSERT OR UPDATE OR DELETE ON produtos
    FOR EACH ROW
    EXECUTE FUNCTION trigger_auditar_alteracao_preco();

Agora, vamos testar o trigger realizando operações comuns de manipulação de dados e verificando se a tabela de auditoria reflete automaticamente cada alteração.

-- Inserir um novo produto (dispara trigger AFTER INSERT)
INSERT INTO produtos (nome, categoria, preco, estoque)
VALUES ('Webcam Full HD', 'Periféricos', 199.90, 45);

-- Atualizar o preço de um produto existente (dispara AFTER UPDATE)
UPDATE produtos SET preco = 279.90 WHERE nome = 'Mouse Gamer 16000DPI';

-- Deletar um produto (dispara AFTER DELETE)
DELETE FROM produtos WHERE nome = 'Cadeira Gamer';

-- Consultar a tabela de auditoria para ver os registros automáticos
SELECT 
    id,
    produto_id,
    preco_antigo,
    preco_novo,
    percentual_reajuste,
    data_alteracao
FROM auditoria_precos
ORDER BY id DESC
LIMIT 5;

 id | produto_id | preco_antigo | preco_novo | percentual_reajuste |        data_alteracao
----+------------+--------------+------------+---------------------+-------------------------------
  9 |          8 |      1299.00 |            |                     | 2026-07-10 14:30:22.123456-03
 10 |          2 |       274.89 |     279.90 |                1.82 | 2026-07-10 14:30:22.124789-03
 11 |          9 |              |     199.90 |                0.00 | 2026-07-10 14:30:22.125012-03
(3 rows)

Repare na elegância dessa solução: a aplicação que faz o

Quer aprender na prática com especialistas?