postgresql扩展extension跨库查询dblink

——postgresql扩展extension跨库查询dblink———
https://blog.csdn.net/luojinbai/article/details/45032683
—在Mydb库创建表tb1
create table tb1
(id integer,nm varchar(20));
—在另外库
create extension dblink;

SELECT dblink_connect(‘mycoon1′,’hostaddr=192.168.1.113 port=5432 dbname=Mydb user=postgres password=postgres’); 
select * from dblink(‘mycoon1′,’select id,nm from tb1’) as t(id bigint,nm varchar(20))
 –或
SELECT dblink_connect(‘hostaddr=192.168.1.113 port=5432 dbname=Mydb user=postgres password=postgres’);
select * from dblink(‘select id,nm from tb1’) as t(id bigint,nm varchar(20))

如果不只是查询数据，而是需要修改postgres的数据的情况下怎么弄呢？
1. 先执行dblink_connect保持连接
SELECT dblink_connect(‘mycoon’,’hostaddr=127.0.0.1 port=5432 dbname=postgres user=postgres password=123456′);  
2. 执行BEGIN命令
SELECT dblink_exec(‘mycoon’, ‘BEGIN’);
3. 执行数据操作（update，insert，create等命令）
SELECT dblink_exec(‘mycoon’, ‘insert into tb1 select generate_series(10,20),”hello”’);
4. 执行事务提交
SELECT dblink_exec(‘mycoon’, ‘COMMIT’);
5. 解除连接
SELECT dblink_disconnect(‘mycoon’);

转载自：https://blog.csdn.net/ozhy111/article/details/81945718