postgresSQL跨库查询
<p>1.安装dblink
下载安装包 <a href="https://www.showdoc.com.cn/server/api/attachment/visitfile/sign/c2c32be72510cfd615d63597c51c9f21" title="[postgresql11-contrib-11.3-1PGDG.rhel7.x86_64.rpm">postgresql11-contrib-11.3-1PGDG.rhel7.x86_64.rpm</a>
下载之后重命名一下，执行命令安装</p>
<pre><code>rpm -ivh postgresql11-contrib-11.3-1PGDG.rhel7.x86_64.rpm</code></pre>
<p>如果安装失败，可以把<a href="https://www.showdoc.com.cn/server/api/attachment/visitfile/sign/11944b4e16194ad957b43540f7a179bf" title="[RPM-GPG-KEY-PGDG">RPM-GPG-KEY-PGDG</a>文件（下载之后重命名一下）导入<code>/etc/pki/rpm-gpg</code>文件夹
<img src="https://www.showdoc.com.cn/server/api/attachment/visitfile/sign/cd3d964457f34b5cacd9ed098c26f6e1" alt="" />
然后执行</p>
<pre><code>rpm --import /etc/pki/rpm-gpg/RPM* </code></pre>
<p>如果再次执行<code>rpm -ivh postgresql11-contrib-11.3-1PGDG.rhel7.x86_64.rpm</code>失败，报错
<img src="https://www.showdoc.com.cn/server/api/attachment/visitfile/sign/12f15f25dea3adfbee8902fd90af8ad5" alt="" />
安装<a href="https://www.showdoc.com.cn/server/api/attachment/visitfile/sign/bc02b3698509e585438247cc98bf5363" title="[libxslt-1.1.28-6.el7.x86_64.rpm">libxslt-1.1.28-6.el7.x86_64.rpm</a>（下载之后重命名一下）</p>
<pre><code>rpm -ivh libxslt-1.1.28-6.el7.x86_64.rpm</code></pre>
<p>再次执行<code>rpm -ivh postgresql11-contrib-11.3-1PGDG.rhel7.x86_64.rpm</code></p>
<p>2.使用数据库连接工具执行SQL，哪个库需要跨库查询就在哪个库上执行</p>
<pre><code>create extension dblink;</code></pre>
<p><img src="https://www.showdoc.com.cn/server/api/attachment/visitfile/sign/91486dcbbacf629b46fe681c2276890f" alt="" />
安装完成之后，可以在函数中看到dblink相关的扩展函数
或者使用<code>select * from pg_extension;</code>-查看pgsql数据库已安装的扩展
3.使用方式</p>
<pre><code>--常规使用
select * from dblink('hostaddr=192.168.0.105 port=5432 dbname=yjtBackstagePermissions user=postgres password=123456','select "NikeName" from "People"') AS testTable ("NikeName" VARCHAR);
--如果不只是查询数据，而是需要修改数据库数据的情况下怎么弄呢？
1. 先执行dblink_connect保持连接
SELECT dblink_connect('mycoon','hostaddr=192.168.0.105 port=5432 dbname=yjtBackstagePermissions 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''');
SELECT dblink_exec('mycoon', 'insert into people(username,nikename) values ("张三","小三") ');
4. 执行事务提交
SELECT dblink_exec('mycoon', 'COMMIT');
5. 解除连接
SELECT dblink_disconnect('mycoon');</code></pre>
<p>4.创建视图。方便查询使用-示例</p>
<pre><code>create view xxxx_orders_view AS
select * from dblink('host=127.0.0.1 port=5432 dbname=zszq user=postgres password=123456','select * from xxxx_orders;') as xxxx_orders(
uuid varchar(50),
m_uuid varchar(50),
"fileId" varchar(50),
authcode varchar(255),
oa_result text,
m_number varchar(50),
oa_status varchar(255),
create_time varchar(50),
update_time varchar(50)
);</code></pre>