Funções, Triggers e Views

PL/pgSQL — Estrutura

-- Estrutura básica
CREATE OR REPLACE FUNCTION minha_funcao()
RETURNS void AS $$
<<block>>
DECLARE
    var_nome TEXT := 'Maria';
    var_idade INT;
    var_registro RECORD;
BEGIN
    -- Lógica
    RAISE NOTICE 'Olá, %', var_nome;
END;
$$ LANGUAGE plpgsql;

DECLARE, BEGIN, EXCEPTION, END

CREATE OR REPLACE FUNCTION processar_pedido(pedido_id INT)
RETURNS TEXT AS $$
DECLARE
    v_status TEXT;
    v_total NUMERIC;
BEGIN
    -- Buscar dados
    SELECT status, total INTO v_status, v_total
    FROM pedidos
    WHERE id = pedido_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Pedido % não encontrado', pedido_id;
    END IF;

    -- Lógica de negócio
    IF v_status != 'pendente' THEN
        RETURN 'Pedido já processado';
    END IF;

    UPDATE pedidos SET status = 'confirmado'
    WHERE id = pedido_id;

    RETURN format('Pedido %s confirmado. Total: %s', pedido_id, v_total);

EXCEPTION
    WHEN OTHERS THEN
        RAISE WARNING 'Erro ao processar pedido %: %', pedido_id, SQLERRM;
        RETURN 'Erro no processamento';
END;
$$ LANGUAGE plpgsql;

RETURN QUERY

-- Retornar conjunto de resultados diretamente de uma query
CREATE OR REPLACE FUNCTION pedidos_por_periodo(
    data_ini DATE,
    data_fim DATE
)
RETURNS TABLE(
    id INT,
    cliente TEXT,
    total NUMERIC,
    data TIMESTAMPTZ
) AS $$
BEGIN
    RETURN QUERY
    SELECT p.id, c.nome, p.total, p.criado_em
    FROM pedidos p
    JOIN clientes c ON c.id = p.cliente_id
    WHERE p.criado_em::DATE BETWEEN data_ini AND data_fim
    ORDER BY p.criado_em;
END;
$$ LANGUAGE plpgsql;

-- Uso
SELECT * FROM pedidos_por_periodo('2024-01-01', '2024-01-31');

PERFORM

-- PERFORM: executar sem retornar resultado (como SELECT sem INTO)
CREATE OR REPLACE FUNCTION atualizar_estoque(produto_id INT, qtde INT)
RETURNS void AS $$
BEGIN
    UPDATE produtos SET estoque = estoque - qtde
    WHERE id = produto_id;

    IF NOT FOUND THEN
        RAISE EXCEPTION 'Produto % não encontrado', produto_id;
    END IF;

    -- PERFORM para chamar função sem resultado
    PERFORM log_auditoria('estoque', 'atualizado', produto_id);

    -- Verificar estoque baixo
    IF (SELECT estoque FROM produtos WHERE id = produto_id) < 10 THEN
        PERFORM notificar_baixo_estoque(produto_id);
    END IF;
END;
$$ LANGUAGE plpgsql;

RAISE

-- Níveis de NOTICE/INFO/DEBUG/WARNING/EXCEPTION
CREATE OR REPLACE FUNCTION testar_raise()
RETURNS void AS $$
DECLARE
    v_valor INT := 42;
BEGIN
    RAISE DEBUG 'Debug: processando %', v_valor;
    RAISE LOG 'Log: valor = %', v_valor;
    RAISE INFO 'Info: operação iniciada';
    RAISE NOTICE 'Notice: processando %', v_valor;
    RAISE WARNING 'Warning: valor alto: %', v_valor;

    -- RAISE EXCEPTION aborta a transação
    RAISE EXCEPTION 'Erro crítico: valor inválido %', v_valor
        USING HINT = 'Valores devem ser menores que 40',
              DETAIL = format('Recebido: %s, Esperado: < 40', v_valor);
END;
$$ LANGUAGE plpgsql;

Funções — Volatility Classes

-- IMMUTABLE: resultado depende apenas dos argumentos (mesmo input = mesmo output)
CREATE OR REPLACE FUNCTION calcular_imposto(valor NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
    RETURN valor * 0.15;
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- STABLE: resultado estável dentro da transação
CREATE OR REPLACE FUNCTION nome_usuario_logado()
RETURNS TEXT AS $$
BEGIN
    RETURN current_user;
END;
$$ LANGUAGE plpgsql STABLE;

-- VOLATILE: resultado pode mudar a cada chamada (padrão)
CREATE OR REPLACE FUNCTION gerar_timestamp()
RETURNS TIMESTAMPTZ AS $$
BEGIN
    RETURN NOW();
END;
$$ LANGUAGE plpgsql VOLATILE;

SQL Functions

-- Funções SQL simples (sem PL/pgSQL)
CREATE OR REPLACE FUNCTION total_pedidos_cliente(cliente_id INT)
RETURNS NUMERIC AS $$
    SELECT COALESCE(SUM(total), 0)
    FROM pedidos
    WHERE cliente_id = $1;
$$ LANGUAGE SQL STABLE;

CREATE OR REPLACE FUNCTION produtos_por_categoria(cat_id INT)
RETURNS TABLE(id INT, nome TEXT, preco NUMERIC) AS $$
    SELECT id, nome, preco
    FROM produtos
    WHERE categoria_id = cat_id
    ORDER BY nome;
$$ LANGUAGE SQL STABLE;

OUT Parameters

CREATE OR REPLACE FUNCTION calcular_estatisticas(
    IN pedido_id INT,
    OUT total_itens INT,
    OUT valor_medio NUMERIC,
    OUT valor_total NUMERIC
) AS $$
BEGIN
    SELECT
        COUNT(*),
        AVG(subtotal),
        SUM(subtotal)
    INTO total_itens, valor_medio, valor_total
    FROM itens_pedido
    WHERE pedido_id = $1;
END;
$$ LANGUAGE plpgsql;

-- Uso
SELECT * FROM calcular_estatisticas(123);
-- Retorna: total_itens, valor_medio, valor_total

Triggers

-- Estrutura: trigger function (retorna TRIGGER) + trigger na tabela

CREATE OR REPLACE FUNCTION trigger_atualizar_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.atualizado_em = NOW();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_pedidos_timestamp
    BEFORE UPDATE ON pedidos
    FOR EACH ROW
    EXECUTE FUNCTION trigger_atualizar_timestamp();

TG_OP e Audit Trigger

-- Trigger de auditoria genérico
CREATE TABLE audit_log (
    id          BIGSERIAL PRIMARY KEY,
    tabela      TEXT NOT NULL,
    operacao    TEXT NOT NULL,
    registro_id INT,
    dados_antigos JSONB,
    dados_novos  JSONB,
    usuario     TEXT DEFAULT current_user,
    criado_em   TIMESTAMPTZ DEFAULT NOW()
);

CREATE OR REPLACE FUNCTION audit_trigger()
RETURNS TRIGGER AS $$
DECLARE
    v_dados_antigos JSONB;
    v_dados_novos   JSONB;
    v_id INT;
BEGIN
    -- Capturar ID independente do nome da coluna
    v_id := COALESCE(NEW.id, OLD.id);

    IF TG_OP = 'INSERT' THEN
        v_dados_novos = to_jsonb(NEW);
    ELSIF TG_OP = 'UPDATE' THEN
        v_dados_antigos = to_jsonb(OLD);
        v_dados_novos = to_jsonb(NEW);
    ELSIF TG_OP = 'DELETE' THEN
        v_dados_antigos = to_jsonb(OLD);
    END IF;

    INSERT INTO audit_log (tabela, operacao, registro_id, dados_antigos, dados_novos)
    VALUES (TG_TABLE_NAME, TG_OP, v_id, v_dados_antigos, v_dados_novos);

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Aplicar a tabelas
CREATE TRIGGER trg_pedidos_audit
    AFTER INSERT OR UPDATE OR DELETE ON pedidos
    FOR EACH ROW
    EXECUTE FUNCTION audit_trigger();

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

INSTEAD OF

-- INSTEAD OF: usado em views (não suporta BEFORE/AFTER em views)
CREATE OR REPLACE FUNCTION inserir_cliente_view()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO clientes (nome, email, cpf)
    VALUES (NEW.nome, NEW.email, NEW.cpf)
    RETURNING id INTO NEW.id;

    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_vw_clientes_insert
    INSTEAD OF INSERT ON vw_clientes_ativos
    FOR EACH ROW
    EXECUTE FUNCTION inserir_cliente_view();

Views

-- View simples
CREATE VIEW vw_pedidos_recentes AS
SELECT p.id, c.nome AS cliente, p.total, p.status, p.criado_em
FROM pedidos p
JOIN clientes c ON c.id = p.cliente_id
WHERE p.criado_em > NOW() - INTERVAL '30 days'
ORDER BY p.criado_em DESC;

-- Updatable view (se não tiver JOIN, GROUP BY, etc)
CREATE VIEW vw_clientes_ativos AS
SELECT id, nome, email, telefone
FROM clientes
WHERE ativo = true;

INSERT INTO vw_clientes_ativos (nome, email) VALUES ('Novo', '[email protected]');
-- Funciona!

-- WITH CHECK OPTION: impede inserções que violam a condição WHERE
CREATE VIEW vw_produtos_baratos AS
SELECT * FROM produtos WHERE preco < 50
WITH CHECK OPTION;

INSERT INTO vw_produtos_baratos (nome, preco) VALUES ('Caro', 100);
-- ERRO! Violou WITH CHECK OPTION

Materialized Views

-- View materializada: dados são armazenados fisicamente
CREATE MATERIALIZED VIEW mv_relatorio_vendas AS
SELECT
    c.nome AS cliente,
    COUNT(p.id) AS total_pedidos,
    SUM(p.total) AS total_gasto,
    MAX(p.criado_em) AS ultima_compra
FROM clientes c
JOIN pedidos p ON p.cliente_id = c.id
GROUP BY c.id, c.nome;

-- Atualizar
REFRESH MATERIALIZED VIEW mv_relatorio_vendas;

-- Atualizar concorrentemente (requer índice único)
CREATE UNIQUE INDEX mv_relatorio_idx ON mv_relatorio_vendas(cliente);
REFRESH MATERIALIZED VIEW CONCURRENTLY mv_relatorio_vendas;

Extensões

-- uuid-ossp (UUID)
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";
SELECT uuid_generate_v4();
SELECT gen_random_uuid();  -- pgcrypto, mais simples

-- pg_trgm (similaridade de texto)
CREATE EXTENSION IF NOT EXISTS pg_trgm;
CREATE INDEX idx_cliente_nome_trgm ON clientes USING GIN (nome gin_trgm_ops);
SELECT * FROM clientes WHERE nome % 'Joao';  -- similaridade

-- pgcrypto (hash, criptografia)
CREATE EXTENSION IF NOT EXISTS pgcrypto;
SELECT crypt('senha123', gen_salt('bf'));     -- bcrypt hash
SELECT digest('mensagem', 'sha256');          -- SHA-256

-- citext (case-insensitive text)
CREATE EXTENSION IF NOT EXISTS citext;
CREATE TABLE usuarios (email CITEXT UNIQUE);
INSERT INTO usuarios VALUES ('[email protected]');
SELECT * FROM usuarios WHERE email = '[email protected]';  -- encontra!

Lab: Sistema de Auditoria Completo

-- 04-auditoria.sql
CREATE EXTENSION IF NOT EXISTS "uuid-ossp";

-- Tabela de auditoria
CREATE TABLE auditoria (
    id              UUID DEFAULT gen_random_uuid() PRIMARY KEY,
    tabela          TEXT NOT NULL,
    operacao        TEXT NOT NULL,
    registro_id     UUID,
    dados_antigos   JSONB,
    dados_novos     JSONB,
    usuario         TEXT DEFAULT current_user,
    ip_origem       TEXT,
    criado_em       TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_auditoria_tabela ON auditoria(tabela, criado_em);

-- Função trigger genérica
CREATE OR REPLACE FUNCTION fn_auditoria()
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO auditoria (tabela, operacao, registro_id, dados_antigos, dados_novos)
    VALUES (
        TG_TABLE_NAME,
        TG_OP,
        COALESCE(NEW.id, OLD.id),
        CASE WHEN TG_OP IN ('UPDATE', 'DELETE') THEN to_jsonb(OLD) ELSE NULL END,
        CASE WHEN TG_OP IN ('INSERT', 'UPDATE') THEN to_jsonb(NEW) ELSE NULL END
    );
    RETURN COALESCE(NEW, OLD);
END;
$$ LANGUAGE plpgsql;

-- Aplicar a todas as tabelas do schema vendas
CREATE TRIGGER trg_audit_clientes
    AFTER INSERT OR UPDATE OR DELETE ON clientes
    FOR EACH ROW EXECUTE FUNCTION fn_auditoria();

CREATE TRIGGER trg_audit_pedidos
    AFTER INSERT OR UPDATE OR DELETE ON pedidos
    FOR EACH ROW EXECUTE FUNCTION fn_auditoria();

CREATE TRIGGER trg_audit_produtos
    AFTER INSERT OR UPDATE OR DELETE ON produtos
    FOR EACH ROW EXECUTE FUNCTION fn_auditoria();

-- Testar
INSERT INTO clientes (nome, email, cpf) VALUES ('Teste', '[email protected]', '12345678901');
UPDATE clientes SET nome = 'Teste Atualizado' WHERE email = '[email protected]';
DELETE FROM clientes WHERE email = '[email protected]';

-- Ver auditoria
SELECT tabela, operacao, registro_id, usuario, criado_em
FROM auditoria
ORDER BY criado_em;

-- Relatório de alterações por período
SELECT
    tabela,
    operacao,
    COUNT(*) AS total,
    MIN(criado_em) AS primeira,
    MAX(criado_em) AS ultima
FROM auditoria
WHERE criado_em >= NOW() - INTERVAL '7 days'
GROUP BY tabela, operacao
ORDER BY total DESC;

PL/pgSQL usa $$ para delimitar corpo, DECLARE para variáveis, BEGIN/EXCEPTION/END para blocos. RETURN QUERY retorna conjuntos. RAISE para logging. Triggers exigem função que retorna TRIGGER. TG_OP detecta INSERT/UPDATE/DELETE. Materialized views aceleram relatórios pesados.