PostgreSQL的dblink扩展模块使用_MsSqlserver

postgresql想要在a库下查询b库的表，可以使用dblink插件。postgresql的dblink是一个支持在一个数据库会话中连接到其他postgresql数据库的扩展模块，可以实现在不同的数据库之间进行通信和交互。

它可以让你在一个数据库中访问另一个数据库的表和函数，甚至可以在不同的服务器之间进行数据交互。

pgsql9.6版本以后自带，不需要手动安装，另外pg使用dblink执行一个远程查询时，必须在调用时定义返回的列名和类型。

dblink用法

创建 pg dblink扩展

create extension if not exists dblink;
###如果已经有，可以在 pg 扩展表查到
select * from pg_extension where extname = 'dblink'; 
或使用\dx 
postgres=# \dx
                                                 已安装扩展列表
        名称        | 版本 |  架构模式  |                                  描述
--------------------+------+------------+------------------------------------------------------------------------
 adminpack          | 2.1  | pg_catalog | administrative functions for postgresql
 dblink             | 1.2  | postgres   | connect to other postgresql databases from within a database
 oracle_fdw         | 1.2  | postgres   | foreign data wrapper for oracle access
 pg_stat_statements | 1.9  | postgres   | track planning and execution statistics of all sql statements executed
 plpgsql            | 1.0  | pg_catalog | pl/pgsql procedural language

建立远程连接

select dblink_connect('local_connect','hostaddr=127.0.0.1 port=5432 dbname=xxxx user=xxxx password=xxxx') as dev;
解释：
'local_connect'  是我自定义的连接的名称
hostaddr=127.0.0.1  表示是本机地址
port=5432  表示使用5432端口，自行设置
dbname  表示要访问的数据库的名称
user，password分别表示用户名和密码，根据自己配置的用户名密码更改

如：
postgres=# select dblink_connect('local_connect','hostaddr=127.0.0.1 port=5432 dbname=hrdb user=hr password=hr') as dev;

 dev
-----

 ok
(1 行记录)

-- 查询所有已链接的dblink
select dblink_get_connections();

ps:

当dblink连接的是同一个pg实例下的不同数据库时，hostaddr就写 127.0.0.1，不用写实际的实例地址。
当是不同实例时，需要写正确的实例，且这两个实例地址间网络是通的。

查询所有已链接的dblink

postgres=# select dblink_get_connections();
 dblink_get_connections
------------------------
 {local_connect}
(1 行记录)

执行查询

--跨库查询
select num,id from dblink('local_connect','select num,id from hr.demotable') as t(num numeric,id integer);
select * from dblink('local_connect','select num,id from hr.demotable') as t(num numeric,id integer);
select * from dblink('local_connect','select * from hr.demotable') as t(num numeric,id integer);

--跨库查询写入
insert into t_dblink
select * from dblink('hostaddr=127.0.0.1 port=5432 dbname=hrdb user=hr password=hr','select * from hr.demotable where id<1000') as t(num numeric,id integer);

####使用 dblink 函数从远程数据库获取数据。 local_connect是预先配置好的远程数据库连接名
####dblink 中查询语句被引号括起来，如果查询语句本身有引号，需要多写一个引号做转义
####as t()表示dblink返回的结果集定义了一个别名't'，并指定了每个列的数据类型

关闭连接

-- 关闭远程连接
###在postgresql中dblink是会话级别；会话断开即dblink也关闭。当然也可以在会话中手动关闭
select dblink_disconnect('local_connect');

-- 查询所有已链接的dblink
select dblink_get_connections();

dblink 扩展

简便写法

上面使用方法比较繁琐，要先创建 dblink连接才能使用，也可以写成下面这种方式，在一个语句中完成:

--直接写 dblink 方式，预先配置好的到远程数据库的连接名 
select * from dblink('hostaddr=127.0.0.1 port=5432 dbname=hrdb user=hr password=hr','select * from hr.demotable') as t(num numeric,id integer);


create table t_dblink as select * from dblink('hostaddr=127.0.0.1 port=5432 dbname=hrdb user=hr password=hr','select * from hr.demotable where 1=2') as t(num numeric,id integer);

insert into t_dblink
select * from dblink('hostaddr=127.0.0.1 port=5432 dbname=hrdb user=hr password=hr','select * from hr.demotable where id<1000') as t(num numeric,id integer);

explain analyze with t_temp as (select * from dblink('hostaddr=127.0.0.1 port=5432 dbname=hrdb user=hr password=hr','select * from hr.demotable where id<1000') as t(num numeric,id integer)) 
select a.num,a.id from t_dblink a,t_temp b where a.id=b.id;

使用dblink查询要带有conn_str，非常不简洁，可以考虑在会话使用临时表/视图来保存。

临时表调用方式

postgres=# create temp table t_dblink as select * from dblink('hostaddr=127.0.0.1 port=5432 dbname=hrdb user=hr password=hr','select * from hr.demotable') as t(num numeric,id integer);
select 1000000
postgres=# select * from t_dblink;
...........

--退出后重新进去临时表不存在
postgres=# select * from t_dblink;
错误:  关系 "t_dblink" 不存在
第1行select * from t_dblink;

视图调用方式

如果认为每次查询都要写dblink的一堆信息很麻烦的话，可以在db中建一个view来解决

postgres=# create view  v_dblink as select * from dblink('hostaddr=127.0.0.1 port=5432 dbname=hrdb user=hr password=hr','select * from hr.demotable') as t(num numeric,id integer);
create view
postgres=# select * from v_dblink;
................
--退出后，重新执行
postgres=# select * from v_dblink;

到底选择视图/临时表，看你需求。在postgresql中临时表在会话结束后是不会保持的，这样的好处：不使用的话无需去删除对应的临时表。

跨库执行ddl/dml操作

–如果需要跨库执行ddl、dml操作，使用dblink_exec

select dblink_connect('local_connect','hostaddr=127.0.0.1 port=5432 dbname=hrdb user=hr password=hr') as dev;
select dblink_exec('local_connect', 'create table aa(id int,name varchar(50))');
select dblink_exec('local_connect', 'drop table aa');
select dblink_exec('local_connect', 'insert into hr.t values (1011102,8999,''hello'',''2048-10-09''::date)');
select dblink_exec('local_connect', 'delete from  hr.t values where id=1011102');