这篇文章主要介绍“怎么使用PostgreSQL contrib中的dblink插件”，在日常操作中，相信很多人在怎么使用PostgreSQL contrib中的dblink插件问题上存在疑惑，小编查阅了各式资料，整理出简单好用的操作方法，希望对大家解答”怎么使用PostgreSQL contrib中的dblink插件”的疑惑有所帮助！接下来，请跟着小编一起来学习吧！

该插件可用于连接远程PG数据库,看起来像是Oracle的DBLink.

安装
进入dblink目录,make & make install

[root@localhostcontrib]#cddblink/[root@localhostdblink]#lsdblink--1.0--1.1.sqldblink--1.1--1.2.sqldblink--1.2.sqldblink.cdblink.controldblink--unpackaged--1.0.sqlexpectedinputMakefileoutputpg_service.confsql[root@localhostdblink]#[root@localhostdblink]#makemake-C../../src/backendgenerated-headersmake[1]:Enteringdirectory`/data/source/postgresql-12beta1/src/backend'make-Ccatalogdistprepgenerated-header-symlinksmake[2]:Enteringdirectory`/data/source/postgresql-12beta1/src/backend/catalog'make[2]:Nothingtobedonefor`distprep'.make[2]:Nothingtobedonefor`generated-header-symlinks'.make[2]:Leavingdirectory`/data/source/postgresql-12beta1/src/backend/catalog'make-Cutilsdistprepgenerated-header-symlinksmake[2]:Enteringdirectory`/data/source/postgresql-12beta1/src/backend/utils'make[2]:Nothingtobedonefor`distprep'.make[2]:Nothingtobedonefor`generated-header-symlinks'.make[2]:Leavingdirectory`/data/source/postgresql-12beta1/src/backend/utils'make[1]:Leavingdirectory`/data/source/postgresql-12beta1/src/backend'make-C../../src/interfaces/libpqallmake[1]:Enteringdirectory`/data/source/postgresql-12beta1/src/interfaces/libpq'make-C../../../src/portallmake[2]:Enteringdirectory`/data/source/postgresql-12beta1/src/port'make[2]:Nothingtobedonefor`all'.make[2]:Leavingdirectory`/data/source/postgresql-12beta1/src/port'make-C../../../src/commonallmake[2]:Enteringdirectory`/data/source/postgresql-12beta1/src/common'make[2]:Nothingtobedonefor`all'.make[2]:Leavingdirectory`/data/source/postgresql-12beta1/src/common'make[1]:Leavingdirectory`/data/source/postgresql-12beta1/src/interfaces/libpq'gcc-std=gnu99-Wall-Wmissing-prototypes-Wpointer-arith-Wdeclaration-after-statement-Werror=vla-Wendif-labels-Wmissing-format-attribute-Wformat-security-fno-strict-aliasing-fwrapv-fexcess-precision=standard-g-O0-DOPTIMIZER_DEBUG-g3-gdwarf-2-fPIC-I../../src/interfaces/libpq-I.-I.-I../../src/include-D_GNU_SOURCE-I/usr/include/libxml2-c-odblink.odblink.c-MMD-MP-MF.deps/dblink.Pogcc-std=gnu99-Wall-Wmissing-prototypes-Wpointer-arith-Wdeclaration-after-statement-Werror=vla-Wendif-labels-Wmissing-format-attribute-Wformat-security-fno-strict-aliasing-fwrapv-fexcess-precision=standard-g-O0-DOPTIMIZER_DEBUG-g3-gdwarf-2-fPIC-shared-odblink.sodblink.o-L../../src/port-L../../src/common-L../../src/interfaces/libpq-lpq-Wl,--as-needed-Wl,-rpath,'/appdb/xdb/pg12beta1/lib',--enable-new-dtags[root@localhostdblink]#makeinstallmake-C../../src/backendgenerated-headersmake[1]:Enteringdirectory`/data/source/postgresql-12beta1/src/backend'make-Ccatalogdistprepgenerated-header-symlinksmake[2]:Enteringdirectory`/data/source/postgresql-12beta1/src/backend/catalog'make[2]:Nothingtobedonefor`distprep'.make[2]:Nothingtobedonefor`generated-header-symlinks'.make[2]:Leavingdirectory`/data/source/postgresql-12beta1/src/backend/catalog'make-Cutilsdistprepgenerated-header-symlinksmake[2]:Enteringdirectory`/data/source/postgresql-12beta1/src/backend/utils'make[2]:Nothingtobedonefor`distprep'.make[2]:Nothingtobedonefor`generated-header-symlinks'.make[2]:Leavingdirectory`/data/source/postgresql-12beta1/src/backend/utils'make[1]:Leavingdirectory`/data/source/postgresql-12beta1/src/backend'make-C../../src/interfaces/libpqallmake[1]:Enteringdirectory`/data/source/postgresql-12beta1/src/interfaces/libpq'make-C../../../src/portallmake[2]:Enteringdirectory`/data/source/postgresql-12beta1/src/port'make[2]:Nothingtobedonefor`all'.make[2]:Leavingdirectory`/data/source/postgresql-12beta1/src/port'make-C../../../src/commonallmake[2]:Enteringdirectory`/data/source/postgresql-12beta1/src/common'make[2]:Nothingtobedonefor`all'.make[2]:Leavingdirectory`/data/source/postgresql-12beta1/src/common'make[1]:Leavingdirectory`/data/source/postgresql-12beta1/src/interfaces/libpq'/usr/bin/mkdir-p'/appdb/xdb/pg12beta1/lib/postgresql'/usr/bin/mkdir-p'/appdb/xdb/pg12beta1/share/postgresql/extension'/usr/bin/mkdir-p'/appdb/xdb/pg12beta1/share/postgresql/extension'/usr/bin/install-c-m755dblink.so'/appdb/xdb/pg12beta1/lib/postgresql/dblink.so'/usr/bin/install-c-m644./dblink.control'/appdb/xdb/pg12beta1/share/postgresql/extension/'/usr/bin/install-c-m644./dblink--1.2.sql./dblink--1.1--1.2.sql./dblink--1.0--1.1.sql./dblink--unpackaged--1.0.sql'/appdb/xdb/pg12beta1/share/postgresql/extension/'[root@localhostdblink]#

简介
dblink可以让PG具备在远程数据库上执行查询的能力,其语法如下:

dblink(textconnname,textsql[,boolfail_on_error])returnssetofrecorddblink(textconnstr,textsql[,boolfail_on_error])returnssetofrecorddblink(textsql[,boolfail_on_error])returnssetofrecord

其中,输入参数:connname为连接名称,connstr是连接串,sql为查询语句,fail_on_error标记出现错误是否停止;输出参数是产生的行,Record类型.由于返回没有列信息,因此使用时需指定columns信息.

使用
远程PG数据库部署在26.26上,希望在PG中查询该26.26实例testdb数据库public模式下的t1表.

testdb=#select*fromt1;id----1(1row)testdb=#\d+t1Table"public.t1"Column|Type|Collation|Nullable|Default|Storage|Statstarget|Description--------+---------+-----------+----------+---------+---------+--------------+-------------id|integer||||plain||testdb=#select*fromt1;id----1(1row)

在本机执行以下查询:

testdb=#createextensiondblink;CREATEEXTENSIONtestdb=#selectt1.*fromdblink('host=192.168.26.26port=5432dbname=testdbuser=xdb','select*fromt1')ast1(idint);id----1(1row)

由于需要显式的指定的返回数据行的元数据,使用起来有些啰嗦.

到此，关于“怎么使用PostgreSQL contrib中的dblink插件”的学习就结束了，希望能够解决大家的疑惑。理论与实践的搭配能更好的帮助大家学习，快去试试吧！若想继续学习更多相关知识，请继续关注亿速云网站，小编会继续努力为大家带来更多实用的文章！