Tutorial com exemplos de Stored Procedures no PostgreSQL

Introdução

As Stored Procedures no PostgreSQL têm um papel importante e oferecem vários benefícios para o gerenciamento de bancos de dados. Neste tutorial, vamos ver como usar stored procedures no PostgreSQL para executar operações SQL de forma mais eficiente e segura. Este artigo é voltado para engenheiros de software e administradores de banco de dados que querem aprimorar suas habilidades usando recursos mais avançados do PostgreSQL.

O que é uma Stored Procedure?

Antes de começar com os exemplos, é importante entender o conceito de Stored Procedure. No PostgreSQL, uma Stored Procedure é um objeto executável pré-compilado que contém uma série de comandos SQL. Sua principal função é executar operações no banco de dados, como inserir, atualizar, excluir e criar. Elas são especialmente úteis para operações que envolvem várias etapas ou lógica complexa, permitindo um melhor gerenciamento de transações e reduzindo a vulnerabilidade do banco de dados.

Observação: os exemplos e comandos deste tutorial são compatíveis com o PostgreSQL 11 e versões mais recentes.

Pré-requisitos

Para este tutorial, você deve ter os seguintes itens instalados e configurados:

1. PostgreSQL instalado na sua máquina local ou em um servidor.
2. Uma ferramenta de linha de comando ou GUI, como o Beekeeper Studio, para executar os comandos SQL.

Criando uma Stored Procedure no PostgreSQL

Historicamente, o PostgreSQL usa a palavra-chave FUNCTION para definir rotinas que podem retornar um valor. Functions no PostgreSQL podem executar operações e devem retornar um valor ou um conjunto, que pode ser um valor escalar ou uma tabela. Elas também podem ser usadas em consultas SQL.

PROCEDURE, introduzida no PostgreSQL 11, é usada para definir rotinas que não retornam valor. Procedures podem ser usadas para executar ações como modificar o estado do banco de dados sem retornar dados diretamente. Elas são invocadas com a instrução CALL, diferentemente das Functions, que costumam ser invocadas como parte de uma instrução SELECT.

Usando PROCEDURE para atualizar uma tabela

Vamos começar criando uma Stored Procedure simples no PostgreSQL. Vamos criar uma procedure para atualizar o salário de um funcionário.

CREATE OR REPLACE PROCEDURE update_employee_salary(p_id INT, p_salary NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
   UPDATE employees SET salary = p_salary WHERE employee_id = p_id;
END;
$$;

No código acima, update_employee_salary é o nome da procedure, que aceita dois parâmetros: o ID do funcionário(p_id) e o novo salário(p_salary).

Chamar uma Stored Procedure no PostgreSQL

Para chamar a procedure que acabamos de criar, usamos o comando CALL, como mostrado abaixo:

CALL update_employee_salary(1, 50000);

O comando acima atualiza o salário do funcionário com ID 1 para 50000.

Uso avançado: procedures com lógica condicional

Procedures também podem usar lógica mais complexa, como instruções condicionais e tratamento de erros, para executar operações mais sofisticadas.

Esta procedure atualiza o departamento de um funcionário com base nas condições informadas, demonstrando o uso de estruturas de controle dentro de procedures.

CREATE OR REPLACE PROCEDURE update_employee_department(employee_id INT, new_department_id INT)
LANGUAGE plpgsql
AS $$
BEGIN
    IF new_department_id IS NOT NULL THEN
        UPDATE employees
        SET department_id = new_department_id
        WHERE id = employee_id;
    ELSE
        RAISE EXCEPTION 'Department ID cannot be null.';
    END IF;
END;
$$;

Resultado esperado:

CREATE PROCEDURE

Usando FUNCTION para recuperar dados

Ao contrário da PROCEDURE, uma FUNCTION no PostgreSQL é mais adequada para operações em que se espera um valor de retorno. Isso é especialmente útil para recuperação de dados e cálculos que precisam fazer parte de consultas SQL. Aqui, vamos criar um exemplo de function que recupera informações com base em condições específicas.

Esta function busca os detalhes de um funcionário com base no seu ID. Ela demonstra como functions podem ser usadas para retornar dados que podem ser usados diretamente em consultas SQL.

CREATE OR REPLACE FUNCTION get_employee_details(employee_id INT)
RETURNS TABLE(first_name VARCHAR, last_name VARCHAR, department_id INT) AS $$
BEGIN
    RETURN QUERY 
    SELECT first_name, last_name, department_id
    FROM employees
    WHERE id = employee_id;
END;
$$ LANGUAGE plpgsql;

Resultado esperado:

CREATE FUNCTION

Invocando a function

Para recuperar dados usando esta FUNCTION, você usa a instrução SELECT, como se estivesse consultando uma tabela.

SELECT * FROM get_employee_details(1);

Resultado esperado:

Aqui, o resultado serão os detalhes do funcionário com ID 1. Se o funcionário existir, o nome, o sobrenome e o ID do departamento serão exibidos. Se esse funcionário não existir, o resultado ficará vazio.

 first_name | last_name | department_id 
------------+-----------+---------------
 John       | Doe       | 1
(1 row)

Conclusão

Neste tutorial, aprendemos a escrever Stored Procedures no PostgreSQL usando PROCEDURE e FUNCTION e a invocá-las. Saber usar Stored Procedures ajuda a ter código mais organizado, mais segurança e maior velocidade de execução. Estes exemplos são simples, mas os conceitos continuam os mesmos para operações mais complexas.

As stored procedures no PostgreSQL oferecem uma forma robusta de encapsular lógica, garantindo que as operações sejam executadas de forma eficiente e segura. Ao integrar stored procedures ao design do seu banco de dados, você consegue aplicações mais fáceis de manter e mais confiáveis. Use este guia como ponto de partida para explorar as amplas possibilidades dos recursos procedurais do PostgreSQL.