Árvores em PL/pgSQL - SQL recursivo para busca em estruturas hierárquicas

Neste artigo faremos uso da linguagem procedural PL/pgSQL para implementar uma busca hierárquica via SQL recursivo no PostgreSQL. Estruturas organizacionais (departamentos, sub-departamentos, e assim por diante - ver Figura 1), fóruns de discussão (mensagem, resposta, resposta da resposta), listas de materiais em manufatura (bill of material), classificações de produtos, disciplinas acadêmicas e seus pré-requisitos, e hierarquias de documentos são alguns exemplos de informações organizadas de forma hierárquicas.

Figura 1. Organograma departamental: exemplo de informações organizadas em hierarquia.

As instruções SQL contidas neste artigo foram executadas utilizando a ferramenta pgAdmin III (www.pgadmin.org), que já vem instalada com o PostgreSQL 8.1 no Windows e é facilmente instalada no Linux, FreeBSD ou Mac OS X. Além disso, estas instruções funcionam em qualquer aplicativo cliente do PostgreSQL, como psql, phpPgAdmin ou PgAccess, e em qualquer plataforma. Portanto, use a ferramenta que achar mais conveniente.

No pgAdmin, selecione o banco de dados desejado e acesse o menu Tools, Query Tool. Será aberta a janela para execução das instruções. Digite-as na área de texto e acesse o menu Query, Execute ou simplesmente pressione F5 para executá-las. Para mais informações sobre o funcionamento da ferramenta, consulte a documentação online (http://www.pgadmin.org/docs/1.4/using.html).

Linguagens procedurais no PostgreSQL

Assim como temos a PL/SQL no Oracle, ou a TransactSQL no SQL Server, o PostgreSQL também possui uma linguagem procedural. Na realidade, ele possui várias! Nativamente, funções definidas pelo usuário (UDFs) no PostgreSQL podem ser criadas em SQL ou em C (para os que não têm medo de ponteiros).

O que quase ninguém sabe é que podemos criar funções no PostgreSQL em qualquer linguagem procedural, pois elas não são embutidas no servidor. Ao invés disso, estas são oferecidas através de módulos carregáveis. Assim, a tarefa de interpretar o código-fonte de um procedimento armazenado fica por conta de um handler, uma função especial escrita em C que conhece todos os detalhes da linguagem em questão. Funciona como uma ponte entre a implementação da linguagem específica e o servidor do PostgreSQL (também chamado de postmaster). Esse handler é compilado na forma de uma biblioteca compartilhada (DLL no Windows ou SO no Linux), e é carregado sob demanda.

Como são executadas no servidor, algumas linguagens são marcadas como não-confiáveis (untrusted), pois dependendo dos privilégios do usuário, uma função escrita nela poderia executar comandos críticos do sistema operacional. Porém, existem linguagens que possuem ambas as implementações: confiável e não-confiável. Por exemplo, funções em Perl podem ser criadas com as linguagens ‘plperl’ e ‘plperlu’ (o sufixo “u” indica a não-confiabilidade).

Assim, com essa estrutura do PostgreSQL, podemos criar funções (stored procedures) ou gatilhos (triggers) em qualquer linguagem. Se esta não existir, somos livres para criar um handler. Na Tabela 1 são listadas as linguagens já existentes para o PostgreSQL. Algumas não são disponibilizadas com o core do PostgreSQL, porém encontram-se em módulos opcionais.

Tabela 1. Linguagens procedurais existentes no PostgreSQL.

A linguagem procedural SQL (‘sql’) é extremamente simples e limitada. Já a linguagem em C (‘c’) é poderosa, porém de difícil construção e manutenção. A linguagem mais utilizada e que é considerada análoga às soluções comerciais é a PL/pgSQL (‘plpgsql’). É nela em que serão apresentados os exemplos deste artigo.

Segundo a documentação oficial do PostgreSQL, os objetivos do projeto da PL/pgSQL foram criar uma linguagem procedural carregável que:

·         pudesse ser usada para criar funções (stored procedures) e gatilhos (triggers);

·         agregasse estruturas de controle à linguagem SQL;

·         pudesse efetuar cálculos complexos;

·         herdasse todos os tipos de dados, funções e operadores definidos pelo usuário;

·         pudesse ser definida como “confiável” ao servidor;

·         fosse fácil de utilizar.

Informações mais detalhadas sobre a PL/pgSQL podem ser obtidas no manual oficial do PostgreSQL 8.1.

Preparando o ambiente

A PL/pgSQL pode não estar ativada para um banco de dados recém-criado no PostgreSQL. Neste caso, precisamos habilitá-la utilizando a instrução createlang.

Na Listagem 1 ...