dblink

dblink is used to execute queries in a remote database. It usually refers to SELECT, and it can also be any SQL statement that returns rows.

When two text parameters are given, the first one is searched by the name for persistent connection; if it is found, the command is executed on the connection. If not found, the first parameter is treated as the same connection information string as dblink_connect, and the connection is only implemented during execution of this command.

Install dependency packages

Version 5.0.0 and 5.0.1 of dblink require libtool-ltdl-devel.

Uninstall libtool-ltdl-devel:

sudo rpm -e libtool-ltdl libtool-ltdl-devel

Check if the package is installed:

sudo rpm -qa |grep libtool-ltdl

Install the dependency packages:

sudo yum install -y libtool-ltdl-devel

Install dblink

dblink participates in the compilation by default, and can be used by creating the extension through the CREATE EXTENSION dblink; statement after logging into the database.

You can also install dblink separately by following these steps.

Access Download page of the MogDB official website, download the dblink extension for the version you need.

Unpack the package, for example:

tar -xzvf dblink-1.0-x.x.x-01-CentOS-x86_64.tar.gz

Go to the directory where the extension is located and execute the make install command.
```
cd dblink/
make install
```
Connect to the database and execute the create extension dblink; to use it.
```
MogDB=# create extension dblink;
CREATE EXTENSION
```

dblink Usage

Create Extensions In the Database
```
create extension dblink;
```
Check Whether the Extension Was Created Successfully
```
\dx
```

Connect to the Remote Database to Perform Query Operations

select * from dblink('dbname=postgres host=127.0.0.1 port=12345 user=test password=Test123456'::text, 'select * from dblink_test'::text)t(id int, name varchar);

Create Connections

select dblink_connect('dblink_conn','hostaddr=127.0.0.1 port=12345 dbname=postgres user=test password=Test123456');

Operate on Database Tables

(View does not support query operations)

select dblink_exec('dblink_conn', 'create table ss(id int, name int)');
select dblink_exec('dblink_conn', 'insert into ss values(2,1)');
select dblink_exec('dblink_conn', 'update ss set name=2 where id=1');
select dblink_exec('dblink_conn', 'delete from ss where id=1');

Close Connections
```
select dblink_disconnect('dblink_conn')
```

Common dblink Functions

Load the dblink extension.
```
CREATE EXTENSION dblink;
```
Open a persistent connection to a remote database.
```
SELECT dblink_connect(text connstr);
```
Close a persistent connection to a remote database.
```
SELECT dblink_disconnect();
```

Query data in a remote database.

SELECT * FROM dblink(text connstr, text sql);

Execute commands in a remote database.

SELECT dblink_exec(text connstr, text sql);

Return the names of all opened dblinks.
```
SELECT dblink_get_connections();
```

Send an asynchronous query to a remote database.

SELECT dblink_send_query(text connname, text sql);

Check whether the connection is busy with an asynchronous query.
```
SELECT dblink_is_busy(text connname);
```
Delete the extension.
```
DROP EXTENSION dblink;
```

Precautions

Currently, dblink allows only the MogDB database to access another MogDB database and does not allow the MogDB database to access a PostgreSQL database.
Currently, dblink does not support the thread pool mode.