记录PostgreSql 使用dblink跨库

dblink 是 PostgreSQL 的一个模块，允许你从当前数据库会话中连接到其他数据库并执行 SQL 命令。这对于跨库查询、数据同步或临时数据交换等场景非常有用。

以下是如何使用 dblink 进行跨库查询的基本步骤：

1. 启用 dblink：
   如果你的 PostgreSQL 还没有启用 dblink，你需要先安装它(psql数据库默认是安装了的，可先在服务器上查看)。对于大多数 PostgreSQL 安装，dblink 是作为标准扩展提供的，你可以通过以下 SQL 命令启用它：

--查看pgsql数据库已安装的扩展
SELECT * FROM pg_extension;

--安装dblink扩展
CREATE EXTENSION IF NOT EXISTS dblink;

2. 连接到另一个数据库：
   使用 dblink_connect 函数连接到另一个数据库。这个函数接受一个连接字符串作为参数，该字符串描述了如何连接到目标数据库。

SELECT dblink_connect('myconn', 'host=myotherhost dbname=myotherdb user=myuser password=mypass');

在这里，myconn 是一个你可以选择的连接名（以后用于引用此连接），而其他参数则是你连接到另一个数据库所需的典型 PostgreSQL 连接参数。
3. 执行查询：
使用 dblink_exec 或 dblink_sql 执行查询。但请注意，这些函数主要执行不返回结果集的 SQL 命令（如 INSERT、UPDATE 等）。对于 SELECT 查询，你需要使用 dblink_get_result 或 dblink_fetch。

例如，执行一个 SELECT 查询并获取结果：

-- 常规使用
SELECT * FROM dblink('myconn', 'SELECT * FROM myotherdb.public.mytable') AS t1(col1 type1, col2 type2, ...);

--如果不只是查询数据，而是需要修改数据库数据的情况下怎么弄呢？
1. 先执行dblink_connect保持连接
SELECT dblink_connect('myconn', 'host=myotherhost dbname=myotherdb user=myuser password=mypass');
2. 执行BEGIN命令
 SELECT dblink_exec('mycoon', 'BEGIN');
3. 执行数据操作（update，insert，create等命令）
 SELECT dblink_exec('mycoon', 'insert into tb1 select generate_series(10,20),''hello''');
 SELECT dblink_exec('mycoon', 'insert into people(username,nikename) values ("张三","小三") ');
4. 执行事务提交
 SELECT dblink_exec('mycoon', 'COMMIT');

-- 建议搭配视图使用
CREATE VIEW people_nikename_view AS 
SELECT * FROM dblink('host=myotherhost dbname=myotherdb user=myuser password=mypass', 
'SELECT * FROM some_table')   
AS t(id int, name text);

注意：在 dblink 的 SQL 查询中，你通常需要指定目标数据库的表的全名（包括模式名）。
4. 断开连接（可选）：
当你完成所有操作后，可以使用 dblink_disconnect 断开连接。但请注意，如果会话结束，连接通常会自动关闭。

SELECT dblink_disconnect('myconn');

5. 注意事项：
   • 使用 dblink 时，请确保你了解并遵循了所有相关的安全最佳实践，特别是关于密码管理和连接字符串的处理。
   • 跨库查询可能会影响性能，特别是当涉及大量数据时。确保在生产环境中仔细测试你的解决方案。
   • 考虑使用其他技术（如外部数据包装器、复制解决方案等）来满足你的具体需求，特别是当你需要更高级或更复杂的跨库功能时。