PostgreSQL: Stored Procedures e PL/pgSQL no PostgreSQL

Q: P: Por que format + USING é recomendado sobre concatenação de strings para SQL dinâmico?

R: O %I do format coloca aspas automaticamente em identificadores e %L trata literais, enquanto USING parametriza a execução para prevenir injeção de SQL. A concatenação direta de strings traz risco de injeção e força você a tratar escape de aspas manualmente.

Última atualização: 2026-08-26

1. O Que Você Vai Aprender

CREATE FUNCTION / CREATE PROCEDURE (o PG distingue funções de stored procedures)
Sintaxe PL/pgSQL: variáveis, atribuição, IF/CASE/LOOP/WHILE/FOR
Modos de parâmetros: IN / OUT / INOUT / VARIADIC
RETURN e RETURN QUERY
Cursores: CURSOR / REFCURSOR
Tratamento de exceções: EXCEPTION / RAISE
Funções de trigger
SQL dinâmico: EXECUTE

2. A História

Bob é um engenheiro back-end em uma plataforma de e-commerce. Todas as manhãs ele precisa executar automaticamente uma série de tarefas de dados:

Calcular o resumo de vendas do dia anterior
Atualizar a visão materializada mv_daily_sales
Se o cálculo falhar, notificar a equipe de operações

Bob decidiu escrever uma stored procedure sp_daily_sales_refresh() em PL/pgSQL, encapsulando toda a lógica no lado do banco de dados para "execução com um clique".

3. Conceito: FUNCTION vs PROCEDURE

(1) Diferença entre Funções e Procedures no PG

Recurso	FUNCTION	PROCEDURE
Valor de retorno	Deve ter RETURNS	Sem RETURNS (pode não retornar nada)
Estilo de chamada	`SELECT func()`	`CALL proc()`
Controle de transação	Não pode COMMIT/ROLLBACK internamente	Pode COMMIT/ROLLBACK internamente
Uso em SQL	Em SELECT/WHERE	Não pode ser incorporado em SQL
Parâmetros INOUT	Suportados, tornam-se automaticamente colunas de retorno	Suportados, passados de volta via CALL

▶ Exemplo: Básico de CREATE FUNCTION

SQL

CREATE OR REPLACE FUNCTION fn_get_order_count(p_customer_id INT)
RETURNS INT
LANGUAGE plpgsql
AS $$
DECLARE
  v_count INT;
BEGIN
  SELECT COUNT(*) INTO v_count
  FROM orders
  WHERE customer_id = p_customer_id;
  RETURN v_count;
END;
$$;

-- Chamar como escalar em SELECT
SELECT fn_get_order_count(1001);

TEXT 📖 Somente leitura

 count 
-------
     5
(1 row)

▶ Exemplo: Básico de CREATE PROCEDURE

SQL

CREATE OR REPLACE PROCEDURE sp_reset_daily_stats()
LANGUAGE plpgsql
AS $$
BEGIN
  TRUNCATE TABLE daily_stats;
  INSERT INTO daily_stats (stat_date, total_orders, total_revenue)
  VALUES (CURRENT_DATE, 0, 0.00);
  COMMIT;
END;
$$;

-- Chamar com CALL
CALL sp_reset_daily_stats();

TEXT 📖 Somente leitura

INSERT 0 1

(2) Quando Usar FUNCTION vs PROCEDURE

Cenário	Recomendado	Razão
Calcular e retornar um valor	FUNCTION	Pode ser incorporado em SQL
Operações ETL em lote	PROCEDURE	Suporta controle de transação interno
Callback de trigger	FUNCTION	Triggers só aceitam funções
Tarefa agendada	PROCEDURE	Pode fazer commit passo a passo

▶ Exemplo: Função com Valores Padrão

SQL

CREATE OR REPLACE FUNCTION fn_calc_tax(
  p_amount NUMERIC,
  p_rate NUMERIC DEFAULT 0.08
)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN ROUND(p_amount * p_rate, 2);
END;
$$;

SELECT fn_calc_tax(1000);        -- usar 8% padrão
SELECT fn_calc_tax(1000, 0.10);  -- 10% personalizado

TEXT 📖 Somente leitura

CREATE TABLE

4. Conceito: Básico do PL/pgSQL

(1) Variáveis e Atribuição

SQL

DECLARE
  v_name TEXT;
  v_price NUMERIC(10,2) := 0.00;
  v_count INT;
  v_row orders%ROWTYPE;       -- tipo de linha da tabela
  v_status TEXT NOT NULL := 'pending';
BEGIN
  v_name := 'Bob';
  SELECT unit_price INTO v_price FROM products WHERE product_id = 1;
END;

Estilo de declaração	Sintaxe	Descrição
Tipo básico	`v_name TEXT;`	Declarar diretamente
Valor padrão	`v_price NUMERIC := 0;`	:= ou DEFAULT
Tipo de linha	`v_row orders%ROWTYPE;`	Corresponde à estrutura da tabela
NOT NULL	`v_status TEXT NOT NULL := 'x';`	Deve atribuir um valor inicial

▶ Exemplo: Variáveis e SELECT INTO

SQL

CREATE OR REPLACE FUNCTION fn_customer_summary(p_id INT)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
  v_name TEXT;
  v_total NUMERIC(12,2);
  v_msg TEXT;
BEGIN
  SELECT first_name || ' ' || last_name, SUM(total_amount)
    INTO v_name, v_total
  FROM customers c
  LEFT JOIN orders o ON o.customer_id = c.customer_id
  WHERE c.customer_id = p_id
  GROUP BY c.customer_id, c.first_name, c.last_name;

  v_msg := v_name || ': $' || COALESCE(v_total::TEXT, '0');
  RETURN v_msg;
END;
$$;

TEXT 📖 Somente leitura

  result  
----------
   42.50
(1 row)

(2) Instruções Condicionais

▶ Exemplo: IF/ELSIF/ELSE

SQL

CREATE OR REPLACE FUNCTION fn_discount_tier(p_total NUMERIC)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
BEGIN
  IF p_total >= 10000 THEN
    RETURN 'PLATINUM';
  ELSIF p_total >= 5000 THEN
    RETURN 'GOLD';
  ELSIF p_total >= 1000 THEN
    RETURN 'SILVER';
  ELSE
    RETURN 'BRONZE';
  END IF;
END;
$$;

TEXT 📖 Somente leitura

CREATE TABLE

▶ Exemplo: Instrução CASE

SQL

CREATE OR REPLACE FUNCTION fn_order_priority(p_amount NUMERIC)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
BEGIN
  CASE
    WHEN p_amount >= 5000 THEN RETURN 'URGENT';
    WHEN p_amount >= 1000 THEN RETURN 'HIGH';
    WHEN p_amount >= 100  THEN RETURN 'NORMAL';
    ELSE RETURN 'LOW';
  END CASE;
END;
$$;

TEXT 📖 Somente leitura

CREATE TABLE

(3) Instruções de Laço

Tipo de laço	Sintaxe	Melhor para
LOOP	`LOOP ... END LOOP;`	Precisa de EXIT manual
WHILE	`WHILE cond LOOP ... END LOOP;`	Condição verificada primeiro
FOR (inteiro)	`FOR i IN 1..10 LOOP ... END LOOP;`	Número fixo de iterações
FOR (consulta)	`FOR rec IN SELECT ... LOOP ... END LOOP;`	Iterar sobre resultados de consulta

▶ Exemplo: LOOP e EXIT

SQL

CREATE OR REPLACE FUNCTION fn_find_price_threshold(
  p_target NUMERIC
)
RETURNS INT
LANGUAGE plpgsql
AS $$
DECLARE
  v_limit INT := 10;
  v_sum NUMERIC := 0;
  v_id INT;
BEGIN
  LOOP
    SELECT unit_price INTO v_sum FROM products
    WHERE product_id = v_limit;
    v_sum := v_sum + p_target;
    EXIT WHEN v_limit > 100 OR v_sum > p_target * 10;
    v_limit := v_limit + 10;
  END LOOP;
  RETURN v_limit;
END;
$$;

TEXT 📖 Somente leitura

  result  
----------
   42.50
(1 row)

▶ Exemplo: Laço FOR com Consulta

SQL

CREATE OR REPLACE FUNCTION fn_sum_category_revenue()
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
DECLARE
  v_rec RECORD;
  v_total NUMERIC(14,2) := 0;
BEGIN
  FOR v_rec IN
    SELECT category, SUM(unit_price * stock_qty) AS cat_rev
    FROM products
    GROUP BY category
  LOOP
    v_total := v_total + v_rec.cat_rev;
    RAISE NOTICE 'Categoria %: R$%', v_rec.category, v_rec.cat_rev;
  END LOOP;
  RETURN v_total;
END;
$$;

TEXT 📖 Somente leitura

  result  
----------
   42.50
(1 row)

5. Conceito: Modos de Parâmetros

(1) IN / OUT / INOUT / VARIADIC

Modo	Entrada	Saída	Descrição
IN (padrão)	Sim	Não	Parâmetro somente leitura
OUT	Não	Sim	Parâmetro de saída, torna-se automaticamente uma coluna de retorno
INOUT	Sim	Sim	Parâmetro bidirecional
VARIADIC	Sim	Não	Lista de argumentos de comprimento variável (array)

▶ Exemplo: Função com Parâmetros OUT

SQL

CREATE OR REPLACE FUNCTION fn_order_stats(
  p_customer_id INT,
  OUT o_count INT,
  OUT o_total NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
  SELECT COUNT(*), COALESCE(SUM(total_amount), 0)
    INTO o_count, o_total
  FROM orders
  WHERE customer_id = p_customer_id;
END;
$$;

SELECT * FROM fn_order_stats(1001);

TEXT 📖 Somente leitura

 count 
-------
     5
(1 row)

▶ Exemplo: Parâmetro INOUT

SQL

CREATE OR REPLACE FUNCTION fn_apply_discount(
  p_price INOUT NUMERIC,
  p_rate NUMERIC
)
LANGUAGE plpgsql
AS $$
BEGIN
  p_price := ROUND(p_price * (1 - p_rate), 2);
END;
$$;

-- Retorna p_price modificado como resultado
SELECT fn_apply_discount(99.99, 0.15);

TEXT 📖 Somente leitura

 count 
-------
     5
(1 row)

(2) RETURN vs RETURN QUERY

Instrução	Finalidade	Tipo de retorno
`RETURN value;`	Retornar um único valor	Corresponde ao tipo RETURNS
`RETURN NEXT row;`	Anexar uma linha ao conjunto de resultados	RETURNS SETOF
`RETURN QUERY SELECT ...;`	Retornar todo o resultado de uma consulta	RETURNS SETOF/TABLE

▶ Exemplo: RETURN QUERY para Retornar um Conjunto

SQL

CREATE OR REPLACE FUNCTION fn_orders_by_date(p_date DATE)
RETURNS SETOF orders
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY
    SELECT * FROM orders
    WHERE order_date = p_date
    ORDER BY order_id;
END;
$$;

SELECT * FROM fn_orders_by_date('2025-06-01');

TEXT 📖 Somente leitura

CREATE TABLE

▶ Exemplo: RETURN TABLE Forma de Retorno Personalizada

SQL

CREATE OR REPLACE FUNCTION fn_top_customers(p_limit INT)
RETURNS TABLE(
  customer_name TEXT,
  total_spent NUMERIC,
  order_count INT
)
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY
    SELECT
      c.first_name || ' ' || c.last_name,
      SUM(o.total_amount)::NUMERIC,
      COUNT(*)::INT
    FROM customers c
    JOIN orders o ON o.customer_id = c.customer_id
    GROUP BY c.customer_id, c.first_name, c.last_name
    ORDER BY total_spent DESC
    LIMIT p_limit;
END;
$$;

TEXT 📖 Somente leitura

 count 
-------
     5
(1 row)

6. Conceito: Cursores

(1) Cursores Explícitos e REFCURSOR

Tipo de cursor	Declaração	Melhor para
Cursor vinculado	`CURSOR (consulta) FOR`	Consulta fixa
REFCURSOR	`REFCURSOR`	Consulta dinâmica, pode ser retornado ao chamador
Cursor implícito	FOR rec IN SELECT	Travessia simples

▶ Exemplo: Travessia com Cursor Explícito

SQL

CREATE OR REPLACE FUNCTION fn_process_overdue_orders()
RETURNS INT
LANGUAGE plpgsql
AS $$
DECLARE
  v_cur CURSOR(p_date DATE) FOR
    SELECT order_id, customer_id, total_amount
    FROM orders
    WHERE order_date < p_date
      AND order_status = 'pending';
  v_rec RECORD;
  v_count INT := 0;
BEGIN
  OPEN v_cur(CURRENT_DATE - INTERVAL '7 days');
  LOOP
    FETCH v_cur INTO v_rec;
    EXIT WHEN NOT FOUND;
    UPDATE orders SET order_status = 'cancelled'
    WHERE order_id = v_rec.order_id;
    v_count := v_count + 1;
  END LOOP;
  CLOSE v_cur;
  RETURN v_count;
END;
$$;

TEXT 📖 Somente leitura

 count 
-------
     5
(1 row)

▶ Exemplo: Retornando um Cursor via REFCURSOR

SQL

CREATE OR REPLACE FUNCTION fn_open_category_cursor(
  p_category TEXT
)
RETURNS REFCURSOR
LANGUAGE plpgsql
AS $$
DECLARE
  v_ref REFCURSOR;
BEGIN
  OPEN v_ref FOR
    SELECT product_id, product_name, unit_price
    FROM products
    WHERE category = p_category
    ORDER BY unit_price DESC;
  RETURN v_ref;
END;
$$;

BEGIN;
SELECT fn_open_category_cursor('Electronics');
-- Retorna nome do cursor como "<unnamed portal 1>"
FETCH ALL FROM "<unnamed portal 1>";
COMMIT;

TEXT 📖 Somente leitura

CREATE TABLE

7. Conceito: Tratamento de Exceções

(1) Bloco EXCEPTION

SQL

BEGIN
  -- lógica normal
EXCEPTION
  WHEN OTHERS THEN
    -- tratar erro
END;

Condição	Descrição
`NO_DATA_FOUND`	SELECT INTO não retornou linhas
`TOO_MANY_ROWS`	SELECT INTO retornou mais de uma linha
`UNIQUE_VIOLATION`	Restrição de unicidade violada
`FOREIGN_KEY_VIOLATION`	Chave estrangeira violada
`DIVISION_BY_ZERO`	Divisão por zero
`OTHERS`	Captura todas as exceções

▶ Exemplo: EXCEPTION Capturando uma Violação de Unicidade

SQL

CREATE OR REPLACE FUNCTION fn_safe_insert_product(
  p_name TEXT, p_price NUMERIC, p_cat TEXT
)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
BEGIN
  INSERT INTO products (product_name, unit_price, category)
  VALUES (p_name, p_price, p_cat);
  RETURN 'OK';
EXCEPTION
  WHEN unique_violation THEN
    RETURN 'DUPLICATE: ' || p_name;
END;
$$;

TEXT 📖 Somente leitura

INSERT 0 1

(2) RAISE Notice e Error

Nível RAISE	Comportamento
`DEBUG`	Apenas log de desenvolvimento
`LOG`	Gravado no log do servidor
`NOTICE`	Mostrado ao cliente
`WARNING`	Mostrado ao cliente + log
`EXCEPTION`	Lança um erro, reverte a transação

▶ Exemplo: RAISE NOTICE e RAISE EXCEPTION

SQL

CREATE OR REPLACE FUNCTION fn_validate_order(p_amount NUMERIC)
RETURNS BOOLEAN
LANGUAGE plpgsql
AS $$
BEGIN
  IF p_amount <= 0 THEN
    RAISE EXCEPTION 'Valor de pedido inválido: R$%', p_amount;
  ELSIF p_amount > 100000 THEN
    RAISE WARNING 'Pedido grande: R$% - requer aprovação', p_amount;
  ELSE
    RAISE NOTICE 'Pedido validado: R$%', p_amount;
  END IF;
  RETURN true;
END;
$$;

TEXT 📖 Somente leitura

CREATE TABLE

8. Conceito: SQL Dinâmico

(1) Usando EXECUTE

Cenário	Sintaxe	Descrição
DDL dinâmico	`EXECUTE 'CREATE TABLE ...';`	Construir SQL em tempo de execução
Parametrizado	`EXECUTE fmt USING v1, v2;`	Parametrizado, seguro contra injeção
Obter resultado	`EXECUTE sql INTO v_var;`	Armazenar em uma variável
Obter múltiplas linhas	`FOR rec IN EXECUTE sql LOOP`	Iterar sobre consulta dinâmica

▶ Exemplo: SQL Dinâmico para Particionamento Mensal

SQL

CREATE OR REPLACE FUNCTION fn_create_monthly_partition(
  p_year INT, p_month INT
)
RETURNS TEXT
LANGUAGE plpgsql
AS $$
DECLARE
  v_sql TEXT;
  v_table TEXT;
  v_start DATE;
  v_end DATE;
BEGIN
  v_table := 'orders_y' || p_year || 'm' || LPAD(p_month::TEXT, 2, '0');
  v_start := make_date(p_year, p_month, 1);
  v_end := v_start + INTERVAL '1 month';

  v_sql := format(
    'CREATE TABLE IF NOT EXISTS %I PARTITION OF orders
     FOR VALUES FROM (%L) TO (%L)',
    v_table, v_start, v_end
  );
  EXECUTE v_sql;
  RETURN v_table || ' criado';
END;
$$;

TEXT 📖 Somente leitura

CREATE TABLE

▶ Exemplo: Consulta Dinâmica com USING

SQL

CREATE OR REPLACE FUNCTION fn_search_products(
  p_col TEXT, p_val TEXT
)
RETURNS SETOF products
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY EXECUTE format(
    'SELECT * FROM products WHERE %I = $1',
    p_col
  ) USING p_val;
END;
$$;

SELECT * FROM fn_search_products('category', 'Electronics');

TEXT 📖 Somente leitura

CREATE TABLE

9. Fluxograma: Decisões de Estrutura PL/pgSQL

flowchart TD
    A[Precisa encapsular lógica?] --> B{Precisa de valor de retorno?}
    B -->|Sim| C[CREATE FUNCTION]
    B -->|Não| D{Precisa de controle de transação?}
    D -->|Sim| E[CREATE PROCEDURE]
    D -->|Não| C
    C --> F{Retorna uma linha / muitas linhas?}
    F -->|Valor único| G[RETURN value]
    F -->|Muitas linhas| H{A consulta é fixa?}
    H -->|Sim| I[RETURN QUERY SELECT]
    H -->|Não| J[FOR rec IN EXECUTE ... LOOP]
    E --> K{Precisa de SQL dinâmico?}
    K -->|Sim| L[EXECUTE format(...) USING]
    K -->|Não| M[Instruções SQL estáticas]
    G --> N{Pode dar erro?}
    N -->|Sim| O[Bloco EXCEPTION]
    N -->|Não| P[Lógica direta]
    I --> N

    style A fill:#e1f5fe
    style O fill:#ffcdd2
    style L fill:#c8e6c9

10. Exemplo Abrangente

A stored procedure de resumo diário de vendas do Bob — calcula vendas, atualiza a visão materializada e notifica em caso de erros:

SQL

CREATE OR REPLACE PROCEDURE sp_daily_sales_refresh()
LANGUAGE plpgsql
AS $$
DECLARE
  v_yesterday DATE := CURRENT_DATE - INTERVAL '1 day';
  v_order_count INT;
  v_total_revenue NUMERIC(14,2);
  v_avg_order NUMERIC(10,2);
BEGIN
  RAISE NOTICE 'Iniciar atualização diária para %', v_yesterday;

  SELECT COUNT(*), COALESCE(SUM(total_amount), 0),
         COALESCE(AVG(total_amount), 0)
    INTO v_order_count, v_total_revenue, v_avg_order
  FROM orders
  WHERE order_date = v_yesterday
    AND order_status = 'completed';

  INSERT INTO daily_sales_summary
    (stat_date, order_count, total_revenue, avg_order_value, created_at)
  VALUES
    (v_yesterday, v_order_count, v_total_revenue, v_avg_order, NOW())
  ON CONFLICT (stat_date) DO UPDATE
    SET order_count = EXCLUDED.order_count,
        total_revenue = EXCLUDED.total_revenue,
        avg_order_value = EXCLUDED.avg_order_value;

  REFRESH MATERIALIZED VIEW CONCURRENTLY mv_daily_sales;

  RAISE NOTICE 'Concluído: % pedidos, R$% receita, R$% média',
    v_order_count, v_total_revenue, v_avg_order;

EXCEPTION
  WHEN OTHERS THEN
    INSERT INTO error_log (error_time, procedure_name, error_msg)
    VALUES (NOW(), 'sp_daily_sales_refresh', SQLERRM);
    RAISE NOTICE 'ERRO na atualização diária: %', SQLERRM;
    -- Notificar equipe de operações via pg_notify
    PERFORM pg_notify('ops_alerts',
      'Falha na atualização diária de vendas: ' || SQLERRM);
END;
$$;

❓ Perguntas Frequentes

📖 Resumo

O PG distingue FUNCTION (deve retornar um valor, pode ser incorporada em SQL) de PROCEDURE (sem valor de retorno, suporta controle de transação)
PL/pgSQL atribui variáveis com :=, busca valores de consulta com SELECT INTO e corresponde tipos de linha com %ROWTYPE
Condicionais: tanto IF/ELSIF/ELSE quanto CASE; CASE se encaixa melhor em lógica de múltiplos ramos
Laços: LOOP (precisa de EXIT), WHILE (condição no início), FOR (contagem fixa ou travessia de consulta)
Modos de parâmetros: IN é somente leitura, OUT é uma coluna de saída, INOUT é bidirecional, VARIADIC recebe argumentos variáveis
RETURN retorna um único valor; RETURN NEXT/QUERY retornam um conjunto
Cursores: cursores vinculados servem para consultas fixas; REFCURSOR serve para consultas dinâmicas e busca adiada
Blocos EXCEPTION capturam erros mas adicionam sobrecarga de subtransação; RAISE controla o nível da mensagem
EXECUTE + format + USING executa SQL dinâmico e previne injeção

📝 Exercícios

⭐ Escreva uma função fn_get_product_price(p_id INT) que use SELECT INTO para consultar unit_price da tabela products e retorne 0 se nenhuma linha existir.
⭐⭐ Escreva uma função fn_customer_tier(p_customer_id INT) que consulte o gasto total do cliente e retorne um nível PLATINUM/GOLD/SILVER/BRONZE usando IF/ELSIF (limiares: 10000 / 5000 / 1000 USD).
⭐⭐⭐ Escreva uma stored procedure sp_monthly_revenue_report(p_year INT, p_month INT): consulte dinamicamente o resumo de pedidos daquele mês, faça INSERT dele na tabela monthly_reports, capture erros com EXCEPTION e grave-os em error_log, exiba progresso com RAISE NOTICE e construa o nome da tabela dinamicamente com format (ex., orders_y2025m06).

Equipe Técnica Web-Tutorial

Uma plataforma de tutoriais mantida por diversos desenvolvedores. Cada tutorial é escrito e revisado por profissionais da área correspondente. Trabalhamos para manter nosso conteúdo preciso e confiável — se encontrar algum problema, avise-nos.

Sobre Nós