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.

 

Fuente: http://www.jardelmorais.com/banco-de-dados/postgres/ddl-postgres/postgres-habilitar-dblink-no-windows/

Publicado el 5 marzo, 2012 en Postgresql. Añade a favoritos el enlace permanente. 4 comentarios.

  1. Woah! I’m really loving the template/theme of this blog. It’s simple, yet effective.
    A lot of times it’s very difficult to get that “perfect balance” between usability and appearance. I must say that you’ve done a awesome job
    with this. Also, the blog loads very quick for me on Chrome.
    Excellent Blog!

  2. It’s really very complex in this busy life to listen news on TV, therefore I only use internet for that reason, and take the hottest information.

  3. Hello to every one, it’s truly a good for me to go to see this site, it includes useful Information.

  4. Thanks for the info Ivancho, while doing some more searching I’ve found an easier way, perhaps, of accomplishing this. FIrst thing is first you must have the PostgreSQL contrib installed (for linux: on ubuntu sudo apt-get install postgresql-contrib, opensuse zypper in postgresql-contrib, centos yum install postgresql-contrib).

    Then execute this query in postgres: CREATE EXTENSION dblink;

    And you’re done =D

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: