Neste artigo iremos demonstrar os detalhes da construção de funções no PostgreSQL, dando enfoque ao uso de Loop's que são muito úteis para diversos recursos quando trabalhamos com uma grande quantidade de dados.
Funções: O que são e porque usar?
São perguntas comuns quando ainda se está desvendando que um SGBD, mais especificamente o PostgreSQL, vai muito além de SELECT, INSERT, DELETE e UPDATE, na verdade muitas coisas que são feitas direto na aplicação podem ser controladas através de funções, vamos a um exemplo:
“Um Sistema de Controle de Estoque tem por principal objetivo garantir que o saldo em estoque seja fidedigno ao que se tem fisicamente no depósito. Sendo assim, a cada efetivação de uma venda ou cancelamento da mesma o produto deve ser retirado e colocado no estoque respectivamente.”
Dado do cenário acima, você pode imaginar duas formas de fazer isso:
- Criando um controle interno na sua aplicação, garantindo que a cada inserção de uma venda e cancelamento da mesma o estoque do produto A seja modificado corretamente.
- Criando uma trigger juntamente com uma função no PostgreSQL (partindo do principio que estamos trabalhando com um banco de dados PostgreSQL) que faça o controle do estoque a cada inserção de uma venda (INSERT) e a cada atualização (quando feito um UPDATE para situação CANCELADA).
Enfim, para certos tipos de projetos a solução 1 seria ideal e para outros a solução 2, e é muito subjetivo dizer qual é a melhor ou qual é a pior.
Vamos a um outro cenário onde uma função se aplicaria muito bem: “Um Módulo de Controle de Contratos de uma Empresa, onde é necessário garantir uma numeração específica para cada contrato, que não pode ser o ID gerado pelo banco de dados, por alguns motivos:”
- o ID é “perdido” toda vez que usamos o nextval, invalidando a numeração do contrato. Precisamos que o numero 002 seja reservado até que de fato haja um contrato 002 e não pelo simples fato do uso de um nextval.
- o Número do contrato pode conter letrar dependendo da formação do contrato, ex: Um contrato de uma empresa Brasileira seria: BR00001.
Sendo assim, criamos uma função própria para numerar os contratos e uma tabela para guardar as numerações em uso, assim garantimos a consistência desses dados independente do uso impróprio ou não dos ID's da tabela contrato.
Vimos então Porque usar as funções e porque elas são tão úteis no dia a dia. Veremos agora, nas próximos seções O que são as funções e como utilizá-las, com exemplos práticos e didáticos.
Estrutura das funções
Listagem 1. Estrutura da Função em PostgreSQL
CREATE FUNCTION NOME_DA_FUNCAO (param1, param2) RETURNS TIPO_RETORNO AS $$
$$ LANGUAGE LINGUAGEM_USADA;
CORPO DA FUNÇÃONa Listagem 1 temos a estrutura básica de uma função, vamos aos detalhes:
- NOME_DA_FUNCAO: Como o próprio nome já sugere aqui você deve colocar o nome da sua função, sem acentos, espaços ou caracteres especiais.
- param1, param2: A sua função pode receber parâmetros de diversos tipos e nomes, aqui mostramos apenas dois, mas você pode usar mais que dois parâmetros ou nenhum.
- TIPO_RETORNO: Pode-se retornar um valor (inteiro, varchar, boolean e etc) ou não retornar nada (void).
- $$: Esse caractere tanto no início como no fim da função, serve para dizer onde começa e onde termina respectivamente.
- LINGUAGEM_USADA: O PostgreSQL suporta diversos tipos de linguagens para você desenvolver suas funções, nesse artigo usaremos plpgsql, mas fique sabendo que você pode desenvolver até em “C”.
Exemplos Práticos
Nada melhor do que exemplos práticos para aprender como funciona. Primeiro vamos criar uma tabela que nos servirá de exemplo. Observe o código da Listagem 2.
Listagem 2. Tabela pessoa_fisica
CREATE TABLE pessoa_fisica (
id_pessoa SERIAL,
nome VARCHAR(80),
sobrenome VARCHAR(200),
sexo CHAR(1),
cpf CHAR(11),
PRIMARY KEY(id_pessoa)
);Antes de continuar é importante que haja alguns dados na tabela, então insira alguns e continue lendo o artigo.
Vamos começar com uma função simples que apenas retorna os dados da tabela pessoa_fisica, como mostra a Listagem 3.
Listagem 3. Retornando dados da tabela pessoa_fisica
CREATE FUNCTION get_pessoas() RETURNS SETOF pessoa_fisica AS $$
BEGIN
RETURN QUERY SELECT * FROM pessoa_fisica
RETURN;
END;
$$ LANGUAGE 'plpgsql'O nome da função acima é “get_pessoas()” que retorna um SETOF (uma lista de registros) do tipo pessoa_fisica. Poderíamos também usar o RECORD, como mostra a Listagem 4.
Listagem 4. Usando RECORD para retorno
CREATE FUNCTION get_pessoas() RETURNS SETOF RECORD AS $$
BEGIN
RETURN QUERY SELECT id_pessoa, nome, sobrenome, sexo, cpf FROM pessoa_fisica;
RETURN;
END;
$$ LANGUAGE 'plpgsql';Qual a diferença entre o retorno do tipo pessoa_fisica e o RECORD? O retorno do tipo pessoa_fisica, como o próprio nome já sugere, retorna registros da tabela pessoa_fisica e não nos da a opção de retornar só alguns campos ou mesmo fazer um JOIN com outras tabelas retornando campos de ambos, para isso usamos o RECORD. O RECORD permite que você faça o que mostramos na Listagem 4, retornar apenas algumas colunas do seu interesse.
Vamos ver agora como usar o FOR LOOP em funções no PostgreSQL. Observe a Listagem 5.
Listagem 5. Usando FOR LOOP
CREATE OR REPLACE FUNCTION meuesquema.telefones()
RETURNS SETOF meuesquema.telefone AS
$BODY$
DECLARE
--cursor
reg meuesquema.telefone%ROWTYPE;
BEGIN
--realiza um loop em todos os telefones da tabela
FOR reg in
SELECT tel.numero, tel.ddd, tel.operadora
FROM meuesquema.telefone tel
LOOP
RETURN NEXT reg;
END LOOP;
RETURN;
END;
$BODY$
LANGUAGE plpgsql VOLATILE;Vamos apontar alguns pontos importantes na listagem acima:
- %rowtype: A palavra-chave rowtype junta do símbolo %, diz que a variável reg irá armazenar um registro do tipo “meuesquema.telefone”, ou seja, uma linha da tabela telefone no esquema “meuesquema”. Poderíamos também usar o RECORD, substituindo toda a declaração por apenas RECORD, mas não precisamos disso no momento.
- FOR: Realizamos um SELECT que retorna o numero, ddd e operadora a partir da tabela telefone no esquema meuesquema. A cada iteração no laço FOR, o registro é armazenado na variável reg que pode ser manipulada dentro da nossa função. Podíamos, por exemplo, chamar o campo numero com: “reg.numero”.
- LOOP e END LOOP: Estes são os delimitadores do laço, que dizem onde começa e onde termina o LOOP.
- $BODY$: Este delimita o início e o fim do “corpo” da função, ou seja, onde a lógica realmente está implementada.
Podemos também optar pela criação de funções que não retornam nada, ou seja, void. Observe a Listagem 6.
Listagem 6. Função sem retorno
CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $$
DECLARE
curtime timestamp := now();
BEGIN
UPDATE users SET last_modified = curtime, comment = comment
WHERE users.id = id;
END;
$$ LANGUAGE plpgsql;Na função acima temos uma exemplo da atualização da tabelas users sem nenhum retorno, ou seja, ao executar a função nada será retornado, apenas executado internamente. Perceba que temos um variável chamada “curtime” que recebe como valor padrão a data e hora atual em que a função está sendo executada. Como parâmetro da função temos o id e o comentário que será atualizado para a tabela usuários.
Raise Notice
Você pode utilizar o RAISE NOTICE quando desejar mostrar mensagens (depuração) enquanto estiver rodando a função, em casos onde a função irá demorar muito tempo para finalizar o RAISE NOTICE pode ajudar a mostrar o progresso da função, por exemplo. Observe a Listagem 7.
Listagem 7. Raise Notice
CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $$
DECLARE
curtime timestamp := now();
BEGIN
RAISE NOTICE 'Atualizando registro id = %',id;
UPDATE users SET last_modified = curtime, comment = comment
WHERE users.id = id;
END;
$$ LANGUAGE plpgsql;Raise Exception
O Raise notice apenas lhe mostra uma mensagem sem interferir no fluxo normal da função, por outro lado o Raise Exception dispara uma exceção e para a execução da função. Muito útil quando deseja-se interromper a execução por uma condição qualquer e mostrar uma mensagem com o motivo da interrupção. Veja a Listagem 8.
Listagem 8. Raise Exception
CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $
DECLARE
curtime timestamp := now();
BEGIN
IF id = 3 THEN
RAISE EXCEPTION 'Ops, o usuario de numero 3 não pode ser atualizado';
END IF;
UPDATE users SET last_modified = curtime, comment = comment
WHERE users.id = id;
END;
$$ LANGUAGE plpgsql;Na Listagem 8 nós interrompemos a execução da função se o id do usuário for igual a 3, por algum motivo (que não nos importa agora) nós não podemos deixar que este usuário seja atualizado e lançamos uma exceção com o erro.
Perceba também que conforme mostramos novos recursos aprendemos outros importantes, como o uso do IF THEN na Listagem 8. O conjunto IF THEN e END IF delimitam um bloco condicional, ou seja, só entrará neste bloco se a condição proposta pelo IF THEN for verdadeira. Aproveitando que estamos falando de bloco condicional, vamos ver como ficaria se desejamos acrescentar o SENÃO, como mostra a Listagem 9.
Listagem 9. ELSE
CREATE FUNCTION stamp_user(id int, comment text) RETURNS void AS $
DECLARE
curtime timestamp := now();
BEGIN
IF id = 3 THEN
RAISE EXCEPTION 'Ops, o usuario de numero 3 não pode ser atualizado';
ELSE
UPDATE users SET last_modified = curtime, comment = comment
WHERE users.id = id;
END IF;
END;
$$ LANGUAGE plpgsql;Apenas colocando o UPDATE dentro do ELSE, mas não faz muita diferença estar depois do bloco IF THEN ou dentro do ELSE, pois se o RAISE EXCEPTION for executado toda a execução da função irá parar e o UPDATE jamais será executado.
Este artigo teve como principal objetivo demonstrar técnicas mais avançadas do PostgreSQL para análise de performance e otimização do banco e o uso de funções para automatização de processos mais complexos, assunto este essencial e obrigatório para DBA's (DataBase Administrator's). Vale ressaltar que todas as técnicas descritas neste artigo devem ser usadas com cautela e sempre mediante a uma análise prévia do problema.
