dblink

dblink Overview

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.

dblink Installation

For details, please refer to gs_install_plugin or gs_install_plugin_local.

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')