Postgres – Habilitar dblink no windows

Para quem já utilizou o linked server no sql server, um dia pode se deparar com a necessidade de criar um link com um servidor remoto e executar uma consulta em um banco remoto.

Pensando nisso, vou demonstrar aqui como criar o linkedserver do postgresql, neste caso chamado de dblink.

O postgres traz nos seus arquivos um chamado dblink.sql, no caso do windows, você pode executar este script que assim então estará liberadas as funções/store procedures do dblink.

O script deve estar em:

Versão 9.0 : C:\Program Files\PostgreSQL\9.0\share\contrib

Caso não encontre o script é esse:

-- dblink_connect now restricts non-superusers to password
-- authenticated connections
CREATE OR REPLACE FUNCTION dblink_connect (text)
RETURNS text
AS '$libdir/dblink','dblink_connect'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_connect (text, text)
RETURNS text
AS '$libdir/dblink','dblink_connect'
LANGUAGE C STRICT;

-- dblink_connect_u allows non-superusers to use
-- non-password authenticated connections, but initially
-- privileges are revoked from public
CREATE OR REPLACE FUNCTION dblink_connect_u (text)
RETURNS text
AS '$libdir/dblink','dblink_connect'
LANGUAGE C STRICT SECURITY DEFINER;

CREATE OR REPLACE FUNCTION dblink_connect_u (text, text)
RETURNS text
AS '$libdir/dblink','dblink_connect'
LANGUAGE C STRICT SECURITY DEFINER;

REVOKE ALL ON FUNCTION dblink_connect_u (text) FROM public;
REVOKE ALL ON FUNCTION dblink_connect_u (text, text) FROM public;

CREATE OR REPLACE FUNCTION dblink_disconnect ()
RETURNS text
AS '$libdir/dblink','dblink_disconnect'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_disconnect (text)
RETURNS text
AS '$libdir/dblink','dblink_disconnect'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_open (text, text)
RETURNS text
AS '$libdir/dblink','dblink_open'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_open (text, text, boolean)
RETURNS text
AS '$libdir/dblink','dblink_open'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_open (text, text, text)
RETURNS text
AS '$libdir/dblink','dblink_open'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_open (text, text, text, boolean)
RETURNS text
AS '$libdir/dblink','dblink_open'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_fetch (text, int)
RETURNS setof record
AS '$libdir/dblink','dblink_fetch'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_fetch (text, int, boolean)
RETURNS setof record
AS '$libdir/dblink','dblink_fetch'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_fetch (text, text, int)
RETURNS setof record
AS '$libdir/dblink','dblink_fetch'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_fetch (text, text, int, boolean)
RETURNS setof record
AS '$libdir/dblink','dblink_fetch'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_close (text)
RETURNS text
AS '$libdir/dblink','dblink_close'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_close (text, boolean)
RETURNS text
AS '$libdir/dblink','dblink_close'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_close (text, text)
RETURNS text
AS '$libdir/dblink','dblink_close'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_close (text, text, boolean)
RETURNS text
AS '$libdir/dblink','dblink_close'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink (text, text)
RETURNS setof record
AS '$libdir/dblink','dblink_record'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink (text, text, boolean)
RETURNS setof record
AS '$libdir/dblink','dblink_record'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink (text)
RETURNS setof record
AS '$libdir/dblink','dblink_record'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink (text, boolean)
RETURNS setof record
AS '$libdir/dblink','dblink_record'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_exec (text, text)
RETURNS text
AS '$libdir/dblink','dblink_exec'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_exec (text, text, boolean)
RETURNS text
AS '$libdir/dblink','dblink_exec'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_exec (text)
RETURNS text
AS '$libdir/dblink','dblink_exec'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_exec (text,boolean)
RETURNS text
AS '$libdir/dblink','dblink_exec'
LANGUAGE C STRICT;

CREATE TYPE dblink_pkey_results AS (position int, colname text);

CREATE OR REPLACE FUNCTION dblink_get_pkey (text)
RETURNS setof dblink_pkey_results
AS '$libdir/dblink','dblink_get_pkey'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_build_sql_insert (text, int2vector, int, _text, _text)
RETURNS text
AS '$libdir/dblink','dblink_build_sql_insert'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_build_sql_delete (text, int2vector, int, _text)
RETURNS text
AS '$libdir/dblink','dblink_build_sql_delete'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_build_sql_update (text, int2vector, int, _text, _text)
RETURNS text
AS '$libdir/dblink','dblink_build_sql_update'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_current_query ()
RETURNS text
AS '$libdir/dblink','dblink_current_query'
LANGUAGE C;

CREATE OR REPLACE FUNCTION dblink_send_query(text, text)
RETURNS int4
AS '$libdir/dblink', 'dblink_send_query'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_is_busy(text)
RETURNS int4
AS '$libdir/dblink', 'dblink_is_busy'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_get_result(text)
RETURNS SETOF record
AS '$libdir/dblink', 'dblink_get_result'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_get_result(text, bool)
RETURNS SETOF record
AS '$libdir/dblink', 'dblink_get_result'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_get_connections()
RETURNS text[]
AS '$libdir/dblink', 'dblink_get_connections'
LANGUAGE C;

CREATE OR REPLACE FUNCTION dblink_cancel_query(text)
RETURNS text
AS '$libdir/dblink', 'dblink_cancel_query'
LANGUAGE C STRICT;

CREATE OR REPLACE FUNCTION dblink_error_message(text)
RETURNS text
AS '$libdir/dblink', 'dblink_error_message'
LANGUAGE C STRICT;

Cole-o no seu editor e execute.

Se executar com sucesso, seu dblink está ativado.

Alguns exemplos de como utilizar:

SELECT tab01.codigo,tab02.codigo
FROM tabela01 tab01
INNER JOIN (SELECT * FROM dblink(‘conexao’,'SELECT codigo FROM tabela02') AS resultado(codigo integer)) tab02 ON tab01.codigo=tab02.codigo;

---------------

select *
from dblink
   (
    'dbname=nomeDoSeuBanco
     hostaddr=10.0.0.11
     user=postgres
     password=postgrepwd
     port=5432',

    'select id
     from suaTabela
    '
   ) as resultado(idRemoto integer); 

--------------

select *
from dblink
   (
    'dbname=nomeBanco
     hostaddr=10.0.0.11
     user=postgres
     password=senha
     port=5432',

    'select *
     from baixa_pv_volume
    '
   ) as t1(idRemoto integer,pvRemoto varchar(10), itemRemoto varchar(10),volumeRemoto integer,
	   data_geracaoRemoto timestamp, usuario_idRemoto integer, deletadoRemoto boolean,
	   empresa_idRemoto varchar(2)); 

-------------

select remota.deletadoRemoto,local.deletado,*
from tabelaLocal local
inner join (select * from dblink('dbname=nomeBanco hostaddr=10.0.0.11 user=postgres password=senha
		port=5432', 'select * from tabela' ) as resultado(idRemoto integer,pvRemoto varchar(10), itemRemoto varchar(10),volumeRemoto integer,
	   data_geracaoRemoto timestamp, usuario_idRemoto integer, deletadoRemoto boolean,
	   empresa_idRemoto varchar(2))) remota on remota.idRemoto = local.id
where remota.deletadoRemoto <> local.deletado

Caso deseje habilitar no línux:

O primeiro passo é instalar o DBLink no banco. Aqui vou considerar que a instalação do PostgreSQL foi realizada de forma compilada e para isso será necessário fazer uso dos arquivos da instalação. Dentro do diretório existe um diretório denominado contrib e dentro deste diretório existe um subdiretório chamado dblink. Para instalar o dblink é necessário realizar a compilação e posteriormente adiciona-lo ao banco desejado.

Para compilar é necessário executar o seguinte comando:

make

make install

Após a execução dos comandos acima será gerado um arquivo chamado dblink.sql. Este arquivo deve ser carregado (importado) no banco desejado. Para demonstrar o seu uso trabalharei com os bancos: banco01 e banco02.

O banco banco01 possui um tabela chamada tabela01 e o banco02 possui uma tabela chamada tabela02. Cada tabela possui um atributo código do tipo inteiro e ambas as tabelas contém 10 registros.

Carregando o arquivo dblink.sql no banco01.

psql banco01 -f dblink.sql

Com o dblink carregado no banco01, o próximo passo é realizar a conexão entre o banco01 e o banco02.

No exemplo, será considerado que estando conectado no banco01 será requisitada uma conexão com o banco02 para ai sim possibilitar a troca de informações entre os dois bancos de dados.

Então vamos a prática:

banco01=# SELECT dblink_connect(‘conexao’,'host=localhost port=9999 user=postgres dbname=banco02′);
dblink_connect
—————-
OK
(1 row)

Alguns parâmetros são informados. O primeiro é um nome para a conexão, e o restante parâmetros normais de uma conexão: hostname, porta, usuário, senha (opcional) e o nome do banco. Como para este exemplo a autenticação esta usando o método trust, o parâmetro password foi omitido.

Com a conexão OK agora é só realizar uma operação qualquer.

Por exemplo, um join entre a tabela01 que pertence ao banco01 e a tabela02 que pertence ao banco02.

banco01=# SELECT tab01.codigo,tab02.codigo FROM tabela01 tab01 INNER JOIN (SELECT * FROM dblink(‘conexao’,'SELECT codigo FROM tabela02′) AS resultado(codigo integer)) tab02 ON tab01.codigo=tab02.codigo;

Um outro exemplo pode ser feito com uma operação de escrita (INSERT, UPDATE OU DELETE).

A partir do banco01 fazendo uma chamada de inserção na tabela02 que está no banco02.

banco01=# SELECT dblink_exec(‘conexao’,'INSERT INTO tabela02 VALUES (generate_series(11,20))’);
dblink_exec
————-
INSERT 0 10
(1 row)

Conferindo:

banco01=# SELECT * FROM dblink(‘conexao’,'SELECT codigo FROM tabela02′) AS resultado(codigo integer);
codigo
——–
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
(20 rows)

Para operações de UPDATE e DELETE o procedimento transcorre da mesma maneira do que do comando INSERT.

Por fim, para encerrar a conexão é necessário executar a seguinte função:

banco01=# SELECT dblink_disconnect(‘conexao’);
dblink_disconnect
——————-
OK
(1 row)

Dicas para a instalação do Línux foi retirada do blog: http://jotacomm.wordpress.com/
acesse caso deseje mais informações.

Comments (0)

Postgres – Habilitar dblink no windows

No comments yet.