Funções e Procedimentos

Autor

Douglas Braga

Funções e procedimentos permitem armazenar lógica de negócio diretamente no banco de dados, onde ela pode ser invocada por qualquer aplicação conectada — independente de linguagem ou plataforma.

Característica	Função (`FUNCTION`)	Procedimento (`PROCEDURE`)
Retorna valor	Sim (escalar ou tabela)	Não (ou via parâmetros `OUT`)
Pode ser usada em `SELECT`	Sim	Não
Chamada com	Expressão SQL	`CALL nome(...)`
Controle de transação	Não (herda da transação chamante)	Sim (pode `COMMIT`/`ROLLBACK`)

Funções SQL Simples

A forma mais direta: o corpo é uma única expressão SQL.

CREATE OR REPLACE FUNCTION total_alunos_curso(p_id_curso INT)
RETURNS BIGINT AS $$
    SELECT COUNT(*) FROM aluno WHERE id_curso = p_id_curso;
$$ LANGUAGE sql;

-- Usando a função em uma consulta
SELECT nome_curso,
       total_alunos_curso(id_curso) AS total_alunos
FROM   curso
ORDER BY total_alunos DESC;

3 records
nome_curso	total_alunos
Engenharia de Software	6
Ciências Econômicas	6
Pedagogia	6

Funções SQL são ideais quando o corpo cabe em uma única instrução. Para lógica mais complexa, usa-se PL/pgSQL.

Funções PL/pgSQL

PL/pgSQL é a linguagem procedural nativa do PostgreSQL. Suporta variáveis, condicionais, laços e tratamento de exceções.

CREATE OR REPLACE FUNCTION classificar_nota(p_nota NUMERIC)
RETURNS TEXT AS $$
BEGIN
    IF    p_nota IS NULL     THEN RETURN 'Em curso';
    ELSIF p_nota >= 9.0      THEN RETURN 'SS — Superior';
    ELSIF p_nota >= 7.0      THEN RETURN 'MS — Médio Superior';
    ELSIF p_nota >= 6.0      THEN RETURN 'MM — Médio';
    ELSIF p_nota >= 4.0      THEN RETURN 'MI — Médio Inferior';
    ELSE                          RETURN 'II — Inferior';
    END IF;
END;
$$ LANGUAGE plpgsql;

-- Classificar notas dos alunos de ENS
SELECT a.nome,
       d.nome_disciplina,
       md.nota,
       classificar_nota(md.nota) AS conceito
FROM   matricula_disciplina md
JOIN   aluno      a USING (id_aluno)
JOIN   disciplina d USING (id_disciplina)
WHERE  d.id_curso = 311
ORDER BY a.nome, d.semestre;

Displaying records 1 - 10
nome	nome_disciplina	nota	conceito
Diego Correia	Projeto Aplicado 2 - site WEB	6.8	MM — Médio
Diego Correia	Bases da Eng. de Software 2	6.1	MM — Médio
Isabela Freitas	Bases da Eng. de Software 2	4.3	MI — Médio Inferior
Isabela Freitas	Projeto Aplicado 2 - site WEB	7.2	MS — Médio Superior
Isabela Freitas	Bases da Eng. de Software 4	7.7	MS — Médio Superior
Isabela Freitas	Projeto Aplicado 4 - blockchain	0.4	II — Inferior
Juliana Nunes	Projeto Aplicado 2 - site WEB	6.1	MM — Médio
Juliana Nunes	Bases da Eng. de Software 2	1.9	II — Inferior
Larissa Araújo	Projeto Aplicado 2 - site WEB	6.3	MM — Médio
Larissa Araújo	Bases da Eng. de Software 2	6.1	MM — Médio

Estruturas de Controle

PL/pgSQL suporta as estruturas usuais de linguagens procedurais:

-- IF / ELSIF / ELSE
IF condicao THEN
    ...
ELSIF outra_condicao THEN
    ...
ELSE
    ...
END IF;

-- WHILE
WHILE condicao LOOP
    ...
END LOOP;

-- FOR sobre resultado de consulta
FOR registro IN SELECT ... FROM ... LOOP
    -- registro.coluna
END LOOP;

Exemplo: Função com Laço FOR

CREATE OR REPLACE FUNCTION media_escola(p_id_escola INT)
RETURNS NUMERIC AS $$
DECLARE
    v_total   NUMERIC := 0;
    v_count   INT     := 0;
    v_salario NUMERIC;
BEGIN
    FOR v_salario IN
        SELECT salario FROM professor WHERE id_escola = p_id_escola
    LOOP
        v_total := v_total + v_salario;
        v_count := v_count + 1;
    END LOOP;

    IF v_count = 0 THEN RETURN NULL; END IF;
    RETURN ROUND(v_total / v_count, 2);
END;
$$ LANGUAGE plpgsql;

-- Média salarial calculada via função vs. AVG nativo
SELECT e.sigla,
       media_escola(e.id_escola)          AS media_fn,
       ROUND(AVG(p.salario), 2)           AS media_avg
FROM   escola e
JOIN   professor p ON p.id_escola = e.id_escola
GROUP BY e.id_escola, e.sigla
ORDER BY e.sigla;

3 records
sigla	media_fn	media_avg
EEMA	4800.00	4800.00
ESETI	4366.67	4366.67
ESG	6550.00	6550.00

Funções que Retornam Tabelas

O padrão SQL (e o PostgreSQL) permite que funções retornem um conjunto de linhas — chamadas de table functions.

CREATE OR REPLACE FUNCTION professores_da_escola(p_id_escola INT)
RETURNS TABLE(nome VARCHAR, ch_semanal INT, salario NUMERIC) AS $$
    SELECT nome, ch_semanal, salario
    FROM   professor
    WHERE  id_escola = p_id_escola
    ORDER BY salario DESC;
$$ LANGUAGE sql;

-- Professores da ESETI (escola 31) via table function
SELECT * FROM professores_da_escola(31);

3 records
nome	ch_semanal	salario
Helena Carvalho	40	6700
Gustavo Costa	20	3300
Igor Melo	20	3100

A função pode ser usada diretamente na cláusula FROM, como uma tabela.

Procedimentos Armazenados

PROCEDURE difere de FUNCTION por não retornar um valor e por poder controlar transações internamente.

CREATE OR REPLACE PROCEDURE reajustar_salarios(p_id_escola INT, p_percentual NUMERIC)
LANGUAGE plpgsql AS $$
BEGIN
    UPDATE professor
    SET    salario = ROUND(salario * (1 + p_percentual / 100), 2)
    WHERE  id_escola = p_id_escola;

    RAISE NOTICE 'Reajuste de % %% aplicado a % professor(es) da escola %',
        p_percentual,
        (SELECT COUNT(*) FROM professor WHERE id_escola = p_id_escola),
        p_id_escola;
END;
$$;

-- Salários antes do reajuste
SELECT nome, salario FROM professor WHERE id_escola = 11 ORDER BY nome;

2 records
nome	salario
Bruno Teixeira	6500
Carla Pinto	6600

-- Chamar o procedimento (5% de reajuste para ESG)
CALL reajustar_salarios(11, 5);

-- Salários após o reajuste
SELECT nome, salario FROM professor WHERE id_escola = 11 ORDER BY nome;

2 records
nome	salario
Bruno Teixeira	6825
Carla Pinto	6930

-- Desfazer o reajuste para não alterar os dados base do livro
CALL reajustar_salarios(11, -100.0 * 5 / 105);

Listando Funções Criadas

-- Funções e procedimentos definidos no schema public
SELECT routine_name        AS nome,
       routine_type        AS tipo,
       data_type           AS retorno
FROM   information_schema.routines
WHERE  routine_schema = 'public'
ORDER BY routine_type, routine_name;

5 records
nome	tipo	retorno
classificar_nota	FUNCTION	text
media_escola	FUNCTION	numeric
professores_da_escola	FUNCTION	record
total_alunos_curso	FUNCTION	bigint
reajustar_salarios	PROCEDURE	NA

Para Praticar

-- Chamar classificar_nota para ver a distribuição de conceitos em ENS
SELECT classificar_nota(nota)   AS conceito,
       COUNT(*)                  AS quantidade
FROM   matricula_disciplina
JOIN   disciplina USING (id_disciplina)
WHERE  id_curso = 311
  AND  nota IS NOT NULL
GROUP BY conceito
ORDER BY quantidade DESC;

5 records
conceito	quantidade
MS — Médio Superior	7
MM — Médio	6
II — Inferior	3
MI — Médio Inferior	2
SS — Superior	2

-- Total de alunos por escola usando a função + JOIN
SELECT e.sigla,
       SUM(total_alunos_curso(c.id_curso)) AS total_alunos
FROM   escola e
JOIN   curso  c ON c.id_escola = e.id_escola
GROUP BY e.id_escola, e.sigla
ORDER BY total_alunos DESC;

3 records
sigla	total_alunos
ESG	6
EEMA	6
ESETI	6