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 dblink

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

2. Unpack the package, for example:

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

3. Go to the directory where the extension is located and execute the make install command.

   cd dblink/
   make install

4. 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.