Desenvolvimento de Funções Armazenadas em PostgreSQL

Introdução

Neste laboratório, você aprenderá a desenvolver funções armazenadas (stored functions) em PostgreSQL. Você passará pelo processo de definir uma função armazenada básica, adicionar parâmetros de entrada com valores padrão, executar a função dentro de uma consulta e, finalmente, remover uma função não utilizada para manter seu banco de dados organizado.

Definir uma Função Armazenada Básica

Nesta etapa, você aprenderá a definir uma função armazenada (stored function) básica no PostgreSQL. Funções armazenadas são blocos de código reutilizáveis que executam uma tarefa específica e podem ser executadas dentro do banco de dados.

Primeiro, abra um terminal e conecte-se ao banco de dados PostgreSQL usando a ferramenta de linha de comando psql. Você realizará todas as operações de banco de dados dentro deste shell psql.

sudo -u postgres psql

Você deverá ver o prompt do PostgreSQL, que se parece com postgres=#.

Agora, crie uma função chamada get_total_products que retorna um valor inteiro fixo. Esta função simples ajudará você a entender a sintaxe básica. Execute o seguinte comando SQL no shell psql:

CREATE FUNCTION get_total_products()
RETURNS INTEGER AS $$
BEGIN
  RETURN 100;
END;
$$ LANGUAGE plpgsql;

Vamos detalhar este comando:

• CREATE FUNCTION get_total_products(): Isso define uma nova função chamada get_total_products sem parâmetros de entrada.
• RETURNS INTEGER: Isso especifica que a função retornará um único valor do tipo de dados INTEGER.
• AS $$ ... $$: A string delimitada por cifrões $$ é usada para encapsular o corpo da função. Esta é uma prática comum no PostgreSQL para evitar problemas com aspas simples dentro do código da função.
• BEGIN ... END;: Este bloco contém a parte executável da função.
• RETURN 100;: Esta é a lógica da nossa função, que simplesmente retorna o inteiro 100.
• LANGUAGE plpgsql: Isso especifica que a função está escrita em plpgsql, a linguagem procedural do PostgreSQL.

Após executar o comando, o PostgreSQL confirmará a criação da função:

CREATE FUNCTION

Para verificar se a função foi criada, você pode chamá-la em uma instrução SELECT:

SELECT get_total_products();

A saída mostrará o valor retornado pela função:

 get_total_products
--------------------
                100
(1 row)

Isso confirma que sua primeira função armazenada está funcionando corretamente.

Criar uma Função com Parâmetros

Funções armazenadas tornam-se mais poderosas quando podem aceitar parâmetros de entrada. Nesta etapa, você criará uma nova função que recebe dois números como entrada e retorna a soma deles.

Certifique-se de que você ainda está no shell psql da etapa anterior. Agora, crie uma função chamada add_numbers que aceita dois parâmetros inteiros.

CREATE FUNCTION add_numbers(a INTEGER, b INTEGER)
RETURNS INTEGER AS $$
BEGIN
  RETURN a + b;
END;
$$ LANGUAGE plpgsql;

A sintaxe é semelhante à etapa anterior, com uma diferença chave na assinatura da função:

• add_numbers(a INTEGER, b INTEGER): Isso define dois parâmetros, a e b, ambos do tipo INTEGER, que devem ser fornecidos quando a função for chamada.

Após executar o comando, você verá a confirmação CREATE FUNCTION.

Agora, teste a função fornecendo dois números como argumentos:

SELECT add_numbers(15, 25);

A função somará os dois números e retornará o resultado:

 add_numbers
-------------
          40
(1 row)

Você também pode usar funções com valores de parâmetros padrão. Vamos criar uma nova função greet_user onde a mensagem de saudação tem um valor padrão.

CREATE OR REPLACE FUNCTION greet_user(username VARCHAR, greeting VARCHAR DEFAULT 'Hello')
RETURNS TEXT AS $$
BEGIN
  RETURN greeting || ', ' || username || '!';
END;
$$ LANGUAGE plpgsql;

Aqui, CREATE OR REPLACE atualizará a função se ela já existir. O parâmetro greeting recebe um valor padrão de 'Hello'. O operador || é usado para concatenação de strings.

Teste a função fornecendo apenas o parâmetro obrigatório username:

SELECT greet_user('Alex');

A função usa a saudação padrão:

       greet_user
------------------------
 Hello, Alex!
(1 row)

Agora, chame-a novamente, mas desta vez, forneça uma saudação personalizada:

SELECT greet_user('Alex', 'Welcome');

A saída agora mostra sua mensagem personalizada:

      greet_user
-----------------------
 Welcome, Alex!
(1 row)

Executar uma Função em uma Consulta

Um uso comum para funções armazenadas é realizar cálculos em dados de tabelas. Nesta etapa, você criará uma tabela, a preencherá com dados e, em seguida, usará uma função em uma consulta nessa tabela.

Primeiro, crie uma tabela simples chamada products para armazenar nomes e preços de produtos.

CREATE TABLE products (
  id SERIAL PRIMARY KEY,
  name VARCHAR(100),
  price NUMERIC(10, 2)
);

Você verá a mensagem de confirmação CREATE TABLE.

Em seguida, insira alguns dados de exemplo na tabela products:

INSERT INTO products (name, price) VALUES
('Laptop', 1200.00),
('Mouse', 25.50),
('Keyboard', 75.00);

Você verá INSERT 0 3, indicando que três linhas foram inseridas.

Agora, vamos criar uma função para calcular o preço com imposto sobre vendas. Esta função receberá um preço como entrada e retornará o preço incluindo um imposto de 7%.

CREATE FUNCTION calculate_taxed_price(price NUMERIC)
RETURNS NUMERIC AS $$
BEGIN
  RETURN price * 1.07;
END;
$$ LANGUAGE plpgsql;

Após criar a função, você pode usá-la diretamente em uma consulta SELECT na tabela products. Esta consulta mostrará o preço original e o preço com imposto para cada produto.

SELECT name, price, calculate_taxed_price(price) AS taxed_price FROM products;

A saída exibirá os resultados da chamada da função para cada linha:

   name   |  price  | taxed_price
----------+---------+-------------
 Laptop   | 1200.00 |    1284.0000
 Mouse    |   25.50 |      27.2850
 Keyboard |   75.00 |      80.2500
(3 rows)

Isso demonstra como você pode encapsular a lógica de negócios dentro de uma função e aplicá-la aos seus dados.

Remover uma Função Armazenada

É uma boa prática remover objetos de banco de dados que não são mais necessários. Nesta etapa, você aprenderá como remover ou "dar drop" em uma função armazenada do seu banco de dados. Removeremos a função get_total_products criada na primeira etapa.

Primeiro, você pode listar as funções no seu banco de dados para confirmar que get_total_products existe.

\df

Você verá uma lista de funções, incluindo get_total_products.

Para remover a função, use o comando DROP FUNCTION. Você deve especificar o nome da função. Se a função tiver parâmetros, você precisará especificar seus tipos, mas como get_total_products não tem nenhum, você pode simplesmente usar o nome.

DROP FUNCTION get_total_products();

O PostgreSQL confirmará a ação:

DROP FUNCTION

Agora, se você listar as funções novamente com \df, verá que get_total_products não está mais na lista.

Também é importante especificar os tipos de argumento se a função for sobrecarregada (ou seja, múltiplas funções com o mesmo nome, mas parâmetros diferentes). Por exemplo, para dar drop na função add_numbers, você deve especificar seus parâmetros inteiros:

DROP FUNCTION add_numbers(INTEGER, INTEGER);

Finalmente, para limpar seu ambiente, dê drop na tabela products criada na etapa anterior.

DROP TABLE products;

Isso conclui o laboratório. Para sair do shell psql, digite \q e pressione Enter.

Resumo

Neste laboratório, você aprendeu os fundamentos do desenvolvimento de funções armazenadas em PostgreSQL. Você criou uma função básica, a aprimorou com parâmetros e valores padrão, aplicou uma função em uma consulta contra dados de tabela e, finalmente, limpou o banco de dados removendo as funções e tabelas. Essas habilidades são essenciais para a criação de soluções de banco de dados mais modulares e eficientes.