The DBLink extension

Syntax

dblink_connect(text connstr) returns text
dblink_connect(text connname, text connstr) returns text

Parameters

Return value

Returns OK on success. Reports an error otherwise.

Example

Connect without a name (unnamed connection):

SELECT dblink_connect('channel_name=localhost dbname=postgres');
 dblink_connect
----------------
 OK
(1 row)

Connect with a name:

SELECT dblink_connect('myconn', 'channel_name=localhost dbname=postgres options=-csearch_path=');
 dblink_connect
----------------
 OK
(1 row)

Connect using a foreign server defined with dblink_fdw:

-- Create a foreign server and user mapping
CREATE SERVER fdtest FOREIGN DATA WRAPPER dblink_fdw OPTIONS (channel_name 'localhost', dbname 'contrib_regression');

CREATE USER regress_dblink_user WITH PASSWORD 'secret';
CREATE USER MAPPING FOR regress_dblink_user SERVER fdtest OPTIONS (user 'regress_dblink_user', password 'secret');
GRANT USAGE ON FOREIGN SERVER fdtest TO regress_dblink_user;
GRANT SELECT ON TABLE foo TO regress_dblink_user;

-- Connect and query as the mapped user
\c - regress_dblink_user
SELECT dblink_connect('myconn', 'fdtest');
 dblink_connect
----------------
 OK
(1 row)

SELECT * FROM dblink('myconn', 'SELECT * FROM foo') AS t(a int, b text, c text[]);
 a  | b |       c
----+---+---------------
  0 | a | {a0,b0,c0}
  1 | b | {a1,b1,c1}
  2 | c | {a2,b2,c2}
...
(11 rows)

-- Clean up
\c - :ORIGINAL_USER
REVOKE USAGE ON FOREIGN SERVER fdtest FROM regress_dblink_user;
REVOKE SELECT ON TABLE foo FROM regress_dblink_user;
DROP USER MAPPING FOR regress_dblink_user SERVER fdtest;
DROP USER regress_dblink_user;
DROP SERVER fdtest;

For foreign server connections to work, local connections must require password authentication. Without it, dblink_connect() returns: ERROR: password is required. Change the target server's authentication method to resolve this.

Syntax

dblink_disconnect() returns text
dblink_disconnect(text connname) returns text

Parameters

Return value

Returns OK on success. Reports an error otherwise.

Example

SELECT dblink_disconnect();
 dblink_disconnect
-------------------
 OK
(1 row)

SELECT dblink_disconnect('myconn');
 dblink_disconnect
-------------------
 OK
(1 row)

Syntax

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

Parameters

Return value

Returns a set of records. Because dblink returns generic records, you must declare column names and types in the FROM clause alias. If the remote result does not match the declared column count, an error is raised.

Example

Query without a persistent connection (transient connection):

SELECT * FROM dblink('dbname=postgres options=-csearch_path=',
                     'select proname, prosrc from pg_proc')
  AS t1(proname name, prosrc text) WHERE proname LIKE 'bytea%';
  proname   |   prosrc
------------+------------
 byteacat   | byteacat
 byteaeq    | byteaeq
...
(12 rows)

Query using an unnamed persistent connection:

SELECT dblink_connect('dbname=postgres options=-csearch_path=');

SELECT * FROM dblink('select proname, prosrc from pg_proc')
  AS t1(proname name, prosrc text) WHERE proname LIKE 'bytea%';

Query using a named connection:

SELECT dblink_connect('myconn', 'dbname=regression options=-csearch_path=');

SELECT * FROM dblink('myconn', 'select proname, prosrc from pg_proc')
  AS t1(proname name, prosrc text) WHERE proname LIKE 'bytea%';

Practical examples

Data synchronization — pull data from a remote table into a local staging table:

INSERT INTO local_staging_table (col1, col2)
SELECT remote_col1, remote_col2
FROM dblink('channel_name=remote_host dbname=remote_db user=myuser password=mypasswd',
            'SELECT col1, col2 FROM remote_table')
AS rt(remote_col1 INTEGER, remote_col2 TEXT);

Cross-database reporting — join local and remote data in a single query:

SELECT l.customer_name, r.order_total
FROM customers l
JOIN dblink('channel_name=orders_host dbname=orders_db user=myuser password=mypasswd',
            'SELECT customer_id, sum(amount) AS order_total FROM orders GROUP BY customer_id')
     AS r(customer_id INTEGER, order_total NUMERIC) ON l.customer_id = r.customer_id;

Syntax

dblink_exec(text connname, text sql [, bool fail_on_error]) returns text
dblink_exec(text connstr, text sql [, bool fail_on_error]) returns text
dblink_exec(text sql [, bool fail_on_error]) returns text

Parameters

Return value

Returns a command status string such as INSERT 0 1, or ERROR if fail_on_error is false and the command fails.

Example

SELECT dblink_connect('dbname=dblink_test_standby');

-- Insert a row using the unnamed connection
SELECT dblink_exec('insert into foo values(21, ''z'', ''{"a0","b0","c0"}'');');
   dblink_exec
-----------------
 INSERT 943366 1
(1 row)

-- Insert using a named connection
SELECT dblink_connect('myconn', 'dbname=regression');

SELECT dblink_exec('myconn', 'insert into foo values(21, ''z'', ''{"a0","b0","c0"}'');');
   dblink_exec
------------------
 INSERT 6432584 1
(1 row)

-- Suppress errors with fail_on_error = false
SELECT dblink_exec('myconn', 'insert into pg_class values (''foo'')', false);
NOTICE:  sql error
DETAIL:  ERROR:  null value in column "relnamespace" violates not-null constraint

 dblink_exec
-------------
 ERROR
(1 row)

Syntax

dblink_open(text cursorname, text sql [, bool fail_on_error]) returns text
dblink_open(text connname, text cursorname, text sql [, bool fail_on_error]) returns text

Parameters

Return value

Returns OK on success, or ERROR on failure.

How it works

A cursor exists only within a transaction. If the remote database is not already in a transaction when dblink_open is called, it starts one with BEGIN. That transaction is committed when the matching dblink_close runs and the cursor is the last one on the connection.

Example

SELECT dblink_connect('dbname=postgres options=-csearch_path=');
 dblink_connect
----------------
 OK
(1 row)

SELECT dblink_open('foo', 'select proname, prosrc from pg_proc');
 dblink_open
-------------
 OK
(1 row)

Syntax

dblink_fetch(text cursorname, int howmany [, bool fail_on_error]) returns setof record
dblink_fetch(text connname, text cursorname, int howmany [, bool fail_on_error]) returns setof record

Parameters

Return value

Returns a set of records. Declare column names and types in the FROM clause, the same as with dblink.

Example

SELECT dblink_connect('dbname=postgres options=-csearch_path=');

SELECT dblink_open('foo', 'select proname, prosrc from pg_proc where proname like ''bytea%''');

-- Fetch in batches of 5
SELECT * FROM dblink_fetch('foo', 5) AS (funcname name, source text);
 funcname |  source
----------+----------
 byteacat | byteacat
 byteacmp | byteacmp
 byteaeq  | byteaeq
 byteage  | byteage
 byteagt  | byteagt
(5 rows)

SELECT * FROM dblink_fetch('foo', 5) AS (funcname name, source text);
 funcname  |  source
-----------+-----------
 byteain   | byteain
 byteale   | byteale
 bytealike | bytealike
 bytealt   | bytealt
 byteane   | byteane
(5 rows)

SELECT * FROM dblink_fetch('foo', 5) AS (funcname name, source text);
  funcname  |   source
------------+------------
 byteanlike | byteanlike
 byteaout   | byteaout
(2 rows)

-- Cursor exhausted — returns 0 rows
SELECT * FROM dblink_fetch('foo', 5) AS (funcname name, source text);
 funcname | source
----------+--------
(0 rows)

Syntax

dblink_close(text cursorname [, bool fail_on_error]) returns text
dblink_close(text connname, text cursorname [, bool fail_on_error]) returns text

Parameters

Return value

Returns OK on success, or ERROR on failure.

If dblink_open started an explicit transaction and this cursor is the last open cursor on the connection, dblink_close commits that transaction with COMMIT.

Example

SELECT dblink_connect('dbname=postgres options=-csearch_path=');

SELECT dblink_open('foo', 'select proname, prosrc from pg_proc');

SELECT dblink_close('foo');
 dblink_close
--------------
 OK
(1 row)

Syntax

dblink_get_connections() returns text[]

Return value

A text array of connection names. Returns NULL if no named connections are open.

Example

SELECT dblink_get_connections();

Syntax

dblink_error_message(text connname) returns text

Parameters

Return value

The last error message on the connection, or an empty string if no errors have occurred.

Example

SELECT dblink_error_message('dtest1');

Syntax

dblink_send_query(text connname, text sql) returns int

Parameters

Return value

Returns 1 if the query was sent successfully, or 0 on failure.

Usage notes

• Only one async query can be active per connection at a time.

• After sending, use dblink_is_busy to check the query status and dblink_get_result to collect results.

• To cancel an active async query, use dblink_cancel_query.

• dblink_send_query combined with dblink_get_result buffers the entire remote result set locally before returning rows. For large result sets, use dblink_open with a cursor to fetch rows in batches and avoid memory bloat. Alternatively, use dblink() directly, which avoids the memory bloat caused by spooling large result sets to disk.

Example

SELECT dblink_send_query('dtest1', 'SELECT * FROM foo WHERE f1 < 3');

Syntax

dblink_is_busy(text connname) returns int

Parameters

Return value

Returns 1 if the connection is busy with an async query. Returns 0 if it is not busy — at which point calling dblink_get_result will not block.

Example

SELECT dblink_is_busy('dtest1');

Syntax

dblink_get_result(text connname [, bool fail_on_error]) returns setof record

Parameters

Return value

• For queries that return rows: returns a set of records. Declare column names and types in the FROM clause, the same as with dblink.

• For commands that do not return rows: returns a single row with one text column containing the command status string. Declare the result as (status text) in the FROM clause.

Call dblink_get_result once for each query sent. After all results are consumed, call it once more to get the empty-set result — this clears the connection for reuse.

Example

SELECT dblink_connect('dtest1', 'dbname=contrib_regression');

-- Send an async query
SELECT * FROM dblink_send_query('dtest1', 'select * from foo where f1 < 3') AS t1;
 t1
----
  1
(1 row)

-- Collect results
SELECT * FROM dblink_get_result('dtest1') AS t1(f1 int, f2 text, f3 text[]);
 f1 | f2 |     f3
----+----+------------
  0 | a  | {a0,b0,c0}
  1 | b  | {a1,b1,c1}
  2 | c  | {a2,b2,c2}
(3 rows)

-- Consume the empty-set result to release the connection
SELECT * FROM dblink_get_result('dtest1') AS t1(f1 int, f2 text, f3 text[]);
 f1 | f2 | f3
----+----+----
(0 rows)

-- Send a query with two result sets
SELECT * FROM dblink_send_query('dtest1', 'select * from foo where f1 < 3; select * from foo where f1 > 6') AS t1;

SELECT * FROM dblink_get_result('dtest1') AS t1(f1 int, f2 text, f3 text[]);
 f1 | f2 |     f3
----+----+------------
  0 | a  | {a0,b0,c0}
  1 | b  | {a1,b1,c1}
  2 | c  | {a2,b2,c2}
(3 rows)

SELECT * FROM dblink_get_result('dtest1') AS t1(f1 int, f2 text, f3 text[]);
 f1 | f2 |      f3
----+----+---------------
  7 | h  | {a7,b7,c7}
  8 | i  | {a8,b8,c8}
  9 | j  | {a9,b9,c9}
 10 | k  | {a10,b10,c10}
(4 rows)

SELECT * FROM dblink_get_result('dtest1') AS t1(f1 int, f2 text, f3 text[]);
 f1 | f2 | f3
----+----+----
(0 rows)

Syntax

dblink_cancel_query(text connname) returns text

Parameters

Return value

Returns OK if the cancel request was sent. Returns an error message string otherwise.

Cancellation is best-effort — a query that has already completed cannot be canceled. After calling dblink_cancel_query, you must still call dblink_get_result to complete the query protocol and release the connection.

Example

SELECT dblink_cancel_query('dtest1');