Shulou(Shulou.com)05/31 Report--

This article mainly introduces "how to use the dblink plug-in in PostgreSQL contrib". In daily operation, I believe many people have doubts about how to use the dblink plug-in in PostgreSQL contrib. The editor consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts about "how to use the dblink plug-in in PostgreSQL contrib". Next, please follow the editor to study!

This plug-in can be used to connect to a remote PG database and looks like Oracle's DBLink.

Installation

Enter the dblink directory, 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] #

Brief introduction

Dblink enables PG to execute queries on a remote database with the following syntax:

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

Among them, the input parameter: connname is the connection name, connstr is the connection string, sql is the query statement, fail_on_error mark whether to stop if there is an error, and the output parameter is the generated row, Record type. Since there is no column information returned, you need to specify columns information when using it.

Use

The remote PG database is deployed on 26.26, and we want to query the T1 table in the public mode of the testdb database of the 26.26 instance in PG.

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)

Execute the following query locally:

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)

It is verbose to use because of the need to explicitly specify metadata that returns rows of data.

At this point, the study on "how to use the dblink plug-in in PostgreSQL contrib" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.