DBLINK: Consultas, Comandos SQL, Transações e Funções

A realização de consultas e a execução de comandos SQL são as principais atividades realizadas com o DBLINK. Continuando os comentários iniciados no post anterior, este tópico apresenta exemplos de aplicação desta tecnologia do PostgreSQL.

As rotinas dblink e dblink_exec permitem a realização de consultas, inserção, alteração e exclusão de dados em bancos distintos. Podem ser utilizadas dentro de uma transação ou de uma função.

* Consultas

As consultas são feitas com a rotina dblink e seguem uma sintaxe diferente do tradicional. Pode ser criada uma conexão para a execução da consulta ou pode ser usada uma que já tenha sido criada anbteriormente.

A rotina dblink tem dois parâmetros, sendo o primeiro a conexão e o segundo o comando a ser executado. Além dos parâmetros, o dblink pede que se liste os campo e seus tipos de dados.

O exemplo abaixo mostra uma consulta que utiliza a conexão "myconn", criada pela função dblink_connect:

--Consulta em banco de dados de estoque de dentro do banco de dados de venda usando conexão criada
SELECT estoque_prod.* FROM dblink('myconn', 'SELECT desc_prod, quant_estocada FROM ESTOQUE_ITEM')
AS estoque_prod (desc_prod VARCHAR(50), quant_estocada INTEGER);

O exemplo abaixo mostra uma consulta que apresenta como parâmetro uma string para conexão:

--Consulta em banco de dados de estoque de dentro do banco de dados de venda com conexão dentro do comando
SELECT * FROM dblink('hostaddr=10.200.239.55 port=5432 dbname=estoque user=postgres password=post',
'SELECT desc_prod, quant_estocada FROM ESTOQUE_ITEM')
AS estoque_prod (desc_prod VARCHAR(50), quant_estocada INTEGER);


* Inserção, Alteração e Exclusão de Dados

Estas operações são implementadas por meio da rotina dblink_exec. Como parâmetros são fornecidos a conexão e o comando a ser executado:

--Inserção de registros com DBLINK_EXEC
--Detalhe: uso das aspas simples. As mesmas devem estar duplicadas.
SELECT dblink_exec('myconn', 'INSERT INTO ESTOQUE_ITEM VALUES (6, ''Relogio'', 400, 110.90);');

--Atualização de registros com DBLINK_EXEC
SELECT dblink_exec('myconn', 'UPDATE ESTOQUE_ITEM VALUES SET quant_estocada = quant_estocada - 1 WHERE cod_prod = 6;');

--Exclusão de registros com DBLINK_EXEC
SELECT dblink_exec('myconn', 'DELETE FROM ESTOQUE_ITEM WHERE cod_prod = 6;');

* Dblink Dentro de uma Transação

Comandos do dblink funcionam normalmente dentro de transações.

Abaixo, um exemplo de transação de venda, sensibilidando dois bancos de dados distintos:

BEGIN;
INSERT INTO VENDA_ITEM (cod_prod, quant_vendida, data) VALUES (1, 1000 , DEFAULT);
SELECT dblink_exec('myconn', 'INSERT INTO ESTOQUE_ITEM VALUES (5, ''Relogio'', 400, 110.90);');
END;

* Uso do Dblink Dentro de Funções

O dblink pode ser livremente utilizado dentro de funções. Pode ainda apresentar parametrização e a chamada à função pode estar inscrita dentro de uma transação maior.

Abaixo, um exemplo com dois parâmetros, simulando uma transação de compra e venda:

-- Uso de dblink DENTRO de uma função
CREATE OR REPLACE FUNCTION func_venda(cod_prd integer, quant integer) RETURNS INTEGER AS $$
DECLARE
comando_venda TEXT;
comando_estoque TEXT;
BEGIN
comando_venda := 'INSERT INTO VENDA_ITEM (cod_prod, quant_vendida, data) VALUES (' || $1 || ',' || $2 || ', DEFAULT);';
EXECUTE comando_venda;
comando_estoque := 'SELECT dblink_exec(''myconn'', ''UPDATE ESTOQUE_ITEM VALUES SET quant_estocada = quant_estocada - ' || $2 || ' WHERE cod_prod = ' || $1 || ''');';
EXECUTE comando_estoque;
RETURN 1; --Retorno com sucesso
END;
$$ LANGUAGE plpgsql;

SELECT func_venda(1,1);

DBLINK: Introdução, dblink_connect/ dblink_disconnect e dblink_get_connections

O dblink é um dos mais importantes contribs do PostgreSQL. Consiste em um conjunto de métodos para acesso a outros bancos de dados do PostgreSQL em uma mesma sessão.

Com esta biblioteca é possível fazer consultas, inserções, alteração e exclusão de dados em mais de um banco de dados (PostgreSQL) sem sair da sessão corrente, o que facilita muito a comunicação entre bancos de dados.

Não faz parte do núcleo deste SGBD, mas pode ser instalado sem grandes problemas e é relativamente fácil de se utilizar.

As principais funções do dblink são:
- dblink_connect
- dblink_get_connections
- dblink - para consultas
- dblink_exec - utilizado na inserção, alteração e exclusão de dados
- dblink_disconnect - liberar conexões dblink

Existem alternativas ao uso desta solução para a comunicaçção entre bancos de dados, tais como cargas diárias de bancos de dados, e outros softwares, mas o dblink tem sido bastante empregado na construção da interoperabilidade de bancos de dados do PostgreSQL e se posiciona como boa alternativa.

Ressalvas:
- Este artigo não visa esgotar a sintaxe do dblink e todos os seus comandos. Apenas mostra uma visão geral do seu funcionamento.

Para testar o dblink, crie dois bancos de dados distintos, chamados estoque e vendas, no mesmo servidor ou em máquinas distintas, e insira as seguintes tabelas e dados:

--Banco de Dados VENDA
CREATE TABLE VENDA_ITEM (
cod_venda SERIAL PRIMARY KEY,
cod_prod INTEGER NOT NULL,
quant_vendida INTEGER NOT NULL,
data DATE DEFAULT current_date NOT NULL);

--Banco de Dados ESTOQUE
CREATE DATABASE ESTOQUE;
DROP TABLE ESTOQUE_ITEM;
CREATE TABLE ESTOQUE_ITEM (
cod_prod SERIAL PRIMARY KEY,
desc_prod VARCHAR(50) NOT NULL,
quant_estocada INTEGER NOT NULL CHECK (quant_estocada > 0),
preco NUMERIC(10,2) CHECK (preco > 0));

INSERT INTO ESTOQUE_ITEM VALUES (1, 'Lápis Preto', 10000, 1.50);
INSERT INTO ESTOQUE_ITEM VALUES (2, 'Borracha', 50, 0.90);
INSERT INTO ESTOQUE_ITEM VALUES (3, 'Caderno 200 Folhas', 175, 8.90);
INSERT INTO ESTOQUE_ITEM VALUES (4, 'Agenda do Ano', 40, 12.50);

* dblink_connect

A rotina dblink_connect cria uma conexão na sessão corrente do PostgreSQL para outro banco de dados. Antes de utiliza-la, é necessário configurar o banco de dados para aceitar a conexão, alterando o arquivo pg_hba e as permissões do usuário.

Como parâmetros, devem ser fornecidos o nome da conexão e uma string de parâmetros de conexão para o banco a ser acessado:

--Criação de Conexão com o DBLINK
-- Conecta com o banco de dados estoque
SELECT dblink_connect('myconn', 'hostaddr=10.211.239.55 port=5432 dbname=estoque user=postgres password=post');
-- Cria nova conecxão com o banco de dados estoque, sem interferir na anterior
SELECT dblink_connect('myconn2', 'hostaddr=10.211.239.55 port=5432 dbname=estoque user=postgres password=post');
-- Conecta com o banco de dados vendas
SELECT dblink_connect('myconn3', 'hostaddr=10.211.239.55 port=5432 dbname=vendas user=postgres password=post');

Ressalvas:
- Evite criar muitas conexões pelo dblink. Cada conexão que for criada vai ocupar memória extra e pode impactar no desempenho do seu sistema. A execução dos exemplos anteriores vai criar três conexões ativas na memória.
- Pode haver lentidão de acesso na conexão dblink. Se um dos bancos de dados conectados estiver com problemas de lentidão, a conexão como um todo será lenta.

* dblink_get_connections

Esta função mostra as conexões ativas do dblink. Não mostra as demais conexões ao banco de dados.

Não apresenta parâmetros. Abaixo, a sintaxe:

--Consultando conexões abertas com DBLINK
SELECT dblink_get_connections();


* dblink_disconnect

A rotina dblink_disconnect encerra conexões criadas com o dblink, liberando a memória. Recebe como parâmetro o nome da conexão a ser encerrada. Execute este comando periodicamente para liberar recursos de hardware, evitando manter conexões ociosas.

--Desconexão
SELECT dblink_disconnect('myconn3');

Os próximos posts comentarão as demais funcionalidades do dblink, apresentando exemplos.

Referência:
- Livro PostgreSQL Prático - dblink

Aprovado! Instalador Amigável para Linux

Mês passado criei um post sobre novos instaladores do PostgreSQL para Linux e Mac. Depois de um tempinho, resolvi testar no Linux.

Na plataforma Linux, a instalação do PostgreSQL normalmente é feita com uma série de comandos do sistema operacional, o que é uma boa maneira de ter maior controle dos resultados. No entanto este processo pode ser dificultoso para neófitos e mesmo para os mais experientes. O procedimento também pode ficar difícil se algo não sair conforme o manual ou tutorial utilizado estiver indicando.

O instalador novo é uma boa opção para quem quer um servidor rodando, mas não deseja perder tempo em configurações avançadas e digitação em prompts de comando. Sua interface é gráfica, simples e agradável.

Os testes foram feitos em uma máquina com Linux Fedora 2.

Preparação da Instalação

Faça o login na estação Linux como usuário root. O próximo passo é fazer o download do instalador e copiá-lo em uma pasta do sistema de arquivos.

Abra o terminal, entre no diretório em que está o instalador e use o comando exec para rodar o programa:

exec /home/usr/postgresql-8.3.3-2-linux.bin

O sistema abrirá uma tela com um assistente amigável para a instalação do PostgreSQL como servidor na máquina.

Etapas da Instalação

As telas são bem simples e futuras versões do instalador devem ter novos recursos. Abaixo, a tela inicial.

Em seguida aparece a tela de seleção do diretório de instalação.


Telas de diretório de dados e de senha do superusuário. Por padrão este instalador cria um superusuário chamado POSTGRES .



Etapas de definição da porta de comunicação utilizada (padrão 5432) e locale (localização geográfica associada a um conjunto de parâmetros: país, idioma, etc.).



Terlas de seleção de linguagens procedurais para as funções e procedimentos e a progress bar de instalação. Observe que as linguagens serão instaladas no template1, geralmente utilizado como base para criação de novos bancos de dados.


Resultado da Instalação

Imediatamente após a instalação pode-se trabalhar com o PostgreSQL.
Abaixo, a tela de conclusão da instalação e visualização dos componentes instalados.



Considerações Finais

Espero que este instalador facilite a vida de quem está iniciando na ferramenta ou de quem tem de fazer várias instalações em curto prazo. Acredito que certas etapas poderiam ser unificadas e que novos parâmetros poderiam ser configuráveis para uma instalação mais personalizada. Apesar destas ressalvas, minha avaliação é super positiva.