怎么使用PostgreSQL contrib中的dblink插件

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

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

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

[root@localhost contrib]# cd dblink/[root@localhost dblink]# lsdblink--1.0--1.1.sql  dblink--1.1--1.2.sql  dblink--1.2.sql  dblink.c  dblink.control  dblink--unpackaged--1.0.sql  expected  input  Makefile  output  pg_service.conf  sql[root@localhost dblink]# [root@localhost dblink]# makemake -C ../../src/backend generated-headersmake[1]: Entering directory `/data/source/postgresql-12beta1/src/backend'make -C catalog distprep generated-header-symlinksmake[2]: Entering directory `/data/source/postgresql-12beta1/src/backend/catalog'make[2]: Nothing to be done for `distprep'.make[2]: Nothing to be done for `generated-header-symlinks'.make[2]: Leaving directory `/data/source/postgresql-12beta1/src/backend/catalog'make -C utils distprep generated-header-symlinksmake[2]: Entering directory `/data/source/postgresql-12beta1/src/backend/utils'make[2]: Nothing to be done for `distprep'.make[2]: Nothing to be done for `generated-header-symlinks'.make[2]: Leaving directory `/data/source/postgresql-12beta1/src/backend/utils'make[1]: Leaving directory `/data/source/postgresql-12beta1/src/backend'make -C ../../src/interfaces/libpq allmake[1]: Entering directory `/data/source/postgresql-12beta1/src/interfaces/libpq'make -C ../../../src/port allmake[2]: Entering directory `/data/source/postgresql-12beta1/src/port'make[2]: Nothing to be done for `all'.make[2]: Leaving directory `/data/source/postgresql-12beta1/src/port'make -C ../../../src/common allmake[2]: Entering directory `/data/source/postgresql-12beta1/src/common'make[2]: Nothing to be done for `all'.make[2]: Leaving directory `/data/source/postgresql-12beta1/src/common'make[1]: Leaving directory `/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 -o dblink.o dblink.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 -o dblink.so dblink.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@localhost dblink]# make installmake -C ../../src/backend generated-headersmake[1]: Entering directory `/data/source/postgresql-12beta1/src/backend'make -C catalog distprep generated-header-symlinksmake[2]: Entering directory `/data/source/postgresql-12beta1/src/backend/catalog'make[2]: Nothing to be done for `distprep'.make[2]: Nothing to be done for `generated-header-symlinks'.make[2]: Leaving directory `/data/source/postgresql-12beta1/src/backend/catalog'make -C utils distprep generated-header-symlinksmake[2]: Entering directory `/data/source/postgresql-12beta1/src/backend/utils'make[2]: Nothing to be done for `distprep'.make[2]: Nothing to be done for `generated-header-symlinks'.make[2]: Leaving directory `/data/source/postgresql-12beta1/src/backend/utils'make[1]: Leaving directory `/data/source/postgresql-12beta1/src/backend'make -C ../../src/interfaces/libpq allmake[1]: Entering directory `/data/source/postgresql-12beta1/src/interfaces/libpq'make -C ../../../src/port allmake[2]: Entering directory `/data/source/postgresql-12beta1/src/port'make[2]: Nothing to be done for `all'.make[2]: Leaving directory `/data/source/postgresql-12beta1/src/port'make -C ../../../src/common allmake[2]: Entering directory `/data/source/postgresql-12beta1/src/common'make[2]: Nothing to be done for `all'.make[2]: Leaving directory `/data/source/postgresql-12beta1/src/common'make[1]: Leaving directory `/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 -m 755  dblink.so '/appdb/xdb/pg12beta1/lib/postgresql/dblink.so'/usr/bin/install -c -m 644 ./dblink.control '/appdb/xdb/pg12beta1/share/postgresql/extension/'/usr/bin/install -c -m 644 ./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@localhost dblink]#

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

dblink(text connname, text sql [, bool fail_on_error]) returns setof recorddblink(text connstr, text sql [, bool fail_on_error]) returns setof recorddblink(text sql [, bool fail_on_error]) returns setof record

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

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

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

在本机执行以下查询:

testdb=# create extension dblink;CREATE EXTENSIONtestdb=# select t1.* from dblink('host=192.168.26.26 port=5432 dbname=testdb user=xdb','select * from t1') as t1(id int); id ----  1(1 row)

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

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