Stored procedure no PostgreSQL - Introdução

Hoje vamos falar sobre uma importante funcionalidade disponibilizada pelo PostgreSQL, as stored procedures ou procedimentos armazenados.

Stored procedures são, normalmente, pequenos pedaços de código que ficam armazenados no lado do servidor de banco de dados. A construção desses pedaços de código é tida como uma boa prática por programadores que podem, por exemplo, deixar no lado do servidor códigos complexos que são utilizados por vários aplicativos, evitando a necessidade de replicá-los em cada um deles.

Diferentemente de outros SGBDs que tratam os conceitos de procedimentos armazenados, gatilhos e funções como coisas distintas, o PostgreSQL trata todos eles como funções. Essas funções tem características diferentes, mas são todas criadas como funções ou functions. Por exemplo, o que diferencia uma função de trigger (gatilho) das outras é o tipo de dado que ela retorna. Falaremos sobre gatilhos em artigos mais adiante. Então, a partir de agora utilizaremos o termo função para nos referirmos aos nossos procedimentos armazenados.

No PostgreSQL podemos utilizar uma série de linguagens diferentes para escrevermos nossas funções. Essas linguagens podem ser divididas em três grupos ou categorias: as não procedurais, as procedurais e as que utilizam linguagem externa e mais complexa. As não procedurais utilizam a SQL como linguagem. Essas são caracterizadas por não possuírem estruturas comuns às linguagens de programação, como por exemplo condição (if, else, case) e repetição (for, while). Entre as procedurais estão a PL/pgSQL (muito semelhante ao PL/SQL do Oracle), PL/Tcl, PL/Perl, PL/Python... Entre as externas temos linguagens como C e C++. Devido ao “poder” de linguagens como o “c”, ela é tida como uma linguagem não confiável, o que indica que ela só pode ser utilizada por um usuário com permissões de super usuário.

Antes de ser utilizada para escrever uma função, uma linguagem precisa ser instalada ou estar habilitada no banco no qual queremos escrever nossa função. Para verificar se uma linguagem já está disponível, basta, no prompt de comando do psql, conectado ao banco no qual se deseja fazer a verificação, digitar o comando "select * from pg_language;". O resultado será a lista de linguagens disponíveis para serem utilizadas nesse banco.

Caso a linguagem escolhida não esteja presente, o comando CREATE LANGUAGE deve ser utilizado.

Ver sintaxe digitando "\h create language;" no psql.

Após instalada a linguagem podemos passar à criação de nossas funções.

Nesse nosso primeiro artigo usaremos apenas o SQL para criação de nossas funções.

Por padrão, a linguagem SQL já está instalada e pode ser usada. Lembrando que as funções criadas utilizando SQL são do grupo não procedural e não possuem instruções como repetição e condição.

A criação de uma nova função é feita através da execução do comando CREATE FUNCTION.

Sintaxe:

CREATE [ OR REPLACE ] FUNCTION nome ( tipo [, ...] ] ) [ RETURNS tipo_retorno ] as

'

         corpo da função

'

LANGUAGE nome_linguagem;

onde:

nome - é o nome da função a ser criada.

tipo - define o tipo do argumento que será recebido.

tipo_retorno - é o tipo de dado que será retornado pela nossa função.

nome_linguagem - é o nome da linguagem que será utilizada para escrever a nossa função.

conteudo - é o corpo da nossa função.

Nota: O comando CREATE FUNCTION possui muito mais detalhes do que os que estamos abordando nesse artigo e que serão abordados em artigos futuros.

A nossa primeira função será uma função usando a linguagem SQL para somar 2 números.

No prompt de comando do psql digite “\e”. Será aberta uma janela para edição de código. Digite o código abaixo.

create function soma(integer, integer) returns integer as

'

         select  $1 + $2;

'

language 'sql';

Feche a janela e responda sim à pergunta de “deseja salvar”. O prompt do psql deve nos retornar a mensagem CREATE FUNCTION.

Após criada nossa função,  vamos testa-la. No prompt do psql digite "select soma(1, 2);".

O $1 e o $2 fazem referência aos parâmetros passados para função. $1 é o primeiro parâmetro e $2 o segundo.

Para visualizar a estrutura da função criada, no psql digite \df+ nomeFuncao; (onde nomeFuncao é o nome da função que você criou).

Até agora vimos uma função simples, que retorna um tipo de dados simples.

Procedimentos armazenados normalmente são utilizados por programadores para esconder consultas complexas para a geração de relatórios. Nesse caso precisamos de um tipo de retorno que suporte uma estrutura um pouco mais complexa.

Esse tipo é o setof. No exemplo abaixo vamos retornar um setof de alunos aprovados.

create or replace function alunosAprovados()returns setof aluno as

'

         select * from aluno where situacao = ''aprovado'';

' language 'sql';

Nota: É claro que essa não é uma consulta complexa, o objetivo é só dar um exemplo do uso do setof como tipo de retorno.

A estrutura da tabela criada para nosso exemplo é:

Matricula int primary key;

Nome varchar(50);

Situacao varchar(20);

Para verificar o resultado basta utiliza um dos comandos abaixo:

Select alunosaprovados();

Select nome from alunosaprovados();

Select * from alunosaprovados();

Bom, pra quem fez alguns testes e agora quer remover as funções do banco, basta usar o comando drop function nome_funcao(lista de parâmetros);

[]'s e até a próxima se Deus quiser.

Hesley Py