PolarDB:dblink extensions

Background information

dblink extensions allow you to use functions to connect to remote PostgreSQL databases. dblink extensions are more flexible than foreign tables and can be used to call irregular objects, such as functions and storage procedures of the connected database.

dblink_connect

Turns on a persistent connection to a remote database.

Syntax

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

Description

• You can use dblink_connect() to establish a connection to a remote PostgreSQL database. The server and database to be connected are identified by using a standard libpq connection string. You can assign names to connections to turn on multiple named connections at a time. If no names are assigned, only one unnamed connection can be opened at a time. The connection persists until it is closed or the database session ends.

• You can also use the name of an existing foreign server as the connection string. We recommend that you use the foreign-data wrapper dblink_fdw to use a foreign server.

Parameters

Return value

If no error occurs, OK is returned. Otherwise, the error is reported and no value is returned.

Example

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

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

-- FOREIGN DATA WRAPPER functionality
-- Note: local connection must require password authentication for this to work properly
--       Otherwise, you will receive the following error from dblink_connect():
--       ERROR:  password is required
--       DETAIL:  Non-superuser cannot connect if the server does not request a password.
--       HINT:  Target server's authentication method must be changed.

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;

\set ORIGINAL_USER :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}
  3 | d | {a3,b3,c3}
  4 | e | {a4,b4,c4}
  5 | f | {a5,b5,c5}
  6 | g | {a6,b6,c6}
  7 | h | {a7,b7,c7}
  8 | i | {a8,b8,c8}
  9 | j | {a9,b9,c9}
 10 | k | {a10,b10,c10}
(11 rows)

\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;

dblink_disconnect

Closes a persistent connection to a remote database.

Syntax

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

Description

You can use dblink_disconnect() to close a connection that is turned on by using dblink_connect(). If you leave this parameter empty, an unnamed connection is closed.

Parameters

Return value

If no error occurs, OK is returned. Otherwise, the error is reported and no value is returned.

Example

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

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

dblink

Performs a query in a remote database.

Syntax

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

Description

• You can use dblink to perform an SQL query in a remote database. You can use all types of SQL statements that return rows, such as the SELECT statement in most cases.

• If you configure two text parameters, the value of the first parameter is used as the name of a persistent connection. The system performs a query by using the connection. If the connection is not found, the value of the first parameter is used as a connection string to establish a connection. The connection persists only when the query is performed.

Parameters

Return value

The rows that are generated by the query are returned. dblink can be used with other queries. Therefore, it is declared to return records instead of a particular set of columns. This indicates that you must specify the set of columns that you want to query. Otherwise, PostgreSQL database cannot perform the query. Example:

SELECT *
    FROM dblink('dbname=mydb options=-csearch_path=',
                'select proname, prosrc from pg_proc')
      AS t1(proname name, prosrc text)
    WHERE proname LIKE 'bytea%';

The names and types of the columns that are returned must be specified in the alias section of the FROM clause. To specify column names in an alias is standard SQL syntax, but to specify column types follows the norms of a PostgreSQL extension. The alias section allows the system to understand the scope of * and what proname in the WHERE clause refers to before the function is run. If the query result from the remote database does not match the specified number of columns in the FROM clause, an error is thrown. However, dblink does not exactly match the column name If the returned data strings are valid input for the column type that is declared in the FROM clause, the query succeeds.

Example

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
 bytealt    | bytealt
 byteale    | byteale
 byteagt    | byteagt
 byteage    | byteage
 byteane    | byteane
 byteacmp   | byteacmp
 bytealike  | bytealike
 byteanlike | byteanlike
 byteain    | byteain
 byteaout   | byteaout
(12 rows)

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

SELECT * FROM dblink('select proname, prosrc from pg_proc')
  AS t1(proname name, prosrc text) WHERE proname LIKE 'bytea%';
  proname   |   prosrc
------------+------------
 byteacat   | byteacat
 byteaeq    | byteaeq
 bytealt    | bytealt
 byteale    | byteale
 byteagt    | byteagt
 byteage    | byteage
 byteane    | byteane
 byteacmp   | byteacmp
 bytealike  | bytealike
 byteanlike | byteanlike
 byteain    | byteain
 byteaout   | byteaout
(12 rows)

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

SELECT * FROM dblink('myconn', 'select proname, prosrc from pg_proc')
  AS t1(proname name, prosrc text) WHERE proname LIKE 'bytea%';
  proname   |   prosrc
------------+------------
 bytearecv  | bytearecv
 byteasend  | byteasend
 byteale    | byteale
 byteagt    | byteagt
 byteage    | byteage
 byteane    | byteane
 byteacmp   | byteacmp
 bytealike  | bytealike
 byteanlike | byteanlike
 byteacat   | byteacat
 byteaeq    | byteaeq
 bytealt    | bytealt
 byteain    | byteain
 byteaout   | byteaout
(14 rows)

dblink_exec

Runs a command in a remote database.

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

Description

• You can use dblink_exec to run a command in a remote database, such as an SQL statement that does not return rows.

• If you configure two text parameters, the value of the first parameter is used as the name of a persistent connection. The system runs a command by using the connection. If the connection is not found, the value of the first parameter is used as a connection string to establish a connection. The connection persists only when the command is run.

Parameters

Return value

The status string of the command or ERROR may be returned, which indicates the status of the query.

Example

SELECT dblink_connect('dbname=dblink_test_standby');
 dblink_connect
----------------
 OK
(1 row)

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

SELECT dblink_connect('myconn', 'dbname=regression');
 dblink_connect
----------------
 OK
(1 row)

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

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)

dblink_open

Turns on a cursor in a remote database.

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

Description

• You can use dblink_open() to turn on a cursor in a remote database and perform operations on the cursor by using dblink_fetch() and dblink_close().

• A cursor can persist only in a transaction. If the remote database is not in a transaction, dblink_open starts an explicit transaction block by using the BEGIN command in the remote database. This transaction is closed after the matched dblink_close is executed.

  Note

  If you use dblink_exec to change data between dblink_open and dblink_close, and an error occurs or you use dblink_disconnect before dblink_close, the transaction is aborted and your changes are lost.

Parameters

Return value

OK or ERROR is returned, which indicates the status of the operation.

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)

dblink_fetch

Returns rows from a cursor that is started in a remote database.

Syntax

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

Description

• You can use dblink_fetch to return rows from a cursor that is started in a remote database by using dblink_open.

• If the number of columns that are returned from the remote cursor does not match the number of returned columns specified in the FROM clause, an error is thrown. The remote cursor is returned to the row where it resides when the error does not occur. If an error occurs in the local query after dblink_fetch is complete, the cursor is also returned to this row.

Parameters

Return value

The rows that are obtained by using the cursor are returned. To use this function, you must specify the set of columns that you want to query. For more information, see the dblink section of this topic.

Example

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

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

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)

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

dblink_close

Closes a cursor in a remote database.

Syntax

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

Description

• You can use dblink_close to close a cursor that is started in a remote database by using dblink_open.

• If an explicit transaction block is started by dblink_open, and the cursor that you want to close is the last cursor in the connection. dblink_close issues a matched COMMIT.

Parameters

Return value

OK or ERROR is returned, which indicates the status of the operation

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)

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

dblink_get_connections

Returns the names of all named dblink connections that were turned on.

Syntax

dblink_get_connections() returns text[]

Description

You can use dblink_get_connections to return an array of the names of all named dblink connections that were turned on.

Return value

A text array of connection names is returned. If no named dblink connection was turned on, NULL is returned.

Example

SELECT dblink_get_connections();

dblink_error_message

Obtains the last error message on a named connection.

Syntax

dblink_error_message(text connname) returns text

Description

You can use dblink_error_message to obtain the last error message on a specific named connection.

Parameters

Return value

The last error message is returned. If error messages do not exist on the connection, an empty string is returned.

Example

SELECT dblink_error_message('dtest1');

dblink_send_query

Performs an asynchronous query in a remote database.

Syntax

dblink_send_query(text connname, text sql) returns int

Description

• You can use dblink_send_query to perform an asynchronous query in a remote database. This way, you do not need to wait for the query to complete. An asynchronous query cannot exist if another asynchronous query is being performed on the same connection.

• You can use dblink_is_busy to check the status of the asynchronous query and collect the results by using dblink_get_result. You can also use dblink_cancel_query to cancel an active asynchronous query.

Parameters

Return value

If the query succeeds, 1 is returned. Otherwise, 0 is returned.

Example

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

dblink_is_busy

Checks whether an asynchronous query is performed on a connection.

Syntax

dblink_is_busy(text connname) returns int

Description

You can use dblink_is_busy to check whether an asynchronous query is performed on a connection.

Parameters

Return value

If an asynchronous query is performed, 1 is returned. Otherwise, 0 is returned. If 0 is returned, dblink_get_result is not blocked.

Example

SELECT dblink_is_busy('dtest1');

dblink_get_result

Obtains the result of an asynchronous query.

Syntax

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

Description

You can use dblink_get_result to collect the results of an asynchronous query that is performed by using dblink_send_query. If the query is not complete, dblink_get_result waits until the query is complete.

Parameters

Return value

• If you perform an asynchronous query, which is an SQL statement that returns rows, the rows that are generated by the query are returned. To use this function, you must specify the set of columns that you want to query. For more information, see the dblink section of this topic.

• If you run an asynchronous command, which is an SQL statement that does not return rows, a single row with a single text column that contains the status string of the command is returned. You must specify that the result has a single text column in the FROM clause that you call.

• You must call the function of dblink_get_result if 1 is returned for dblink_send_query, which indicates that an asynchronous query is performed. The function of dblink_get_result must be called for each query and before the connection can be used to obtain an empty set result.

• If you use dblink_send_query and dblink_get_result, dblink obtains the result of the entire remote query before rows are returned to the local query processor. If the query returns a large number of rows, transient memory bloat happens in the local session. We recommend that you use dblink_open to run a cursor and use the cursor to query a manageable number of rows at a time. You can also use dblink(), which helps prevent the memory bloat that is caused by spooling large result sets to a disk.

Example

contrib_regression=# SELECT dblink_connect('dtest1', 'dbname=contrib_regression');
 dblink_connect
----------------
 OK
(1 row)

contrib_regression=# SELECT * FROM
contrib_regression-# dblink_send_query('dtest1', 'select * from foo where f1 < 3') AS t1;
 t1
----
  1
(1 row)

contrib_regression=# 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)

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

contrib_regression=# SELECT * FROM
contrib_regression-# dblink_send_query('dtest1', 'select * from foo where f1 < 3; select * from foo where f1 > 6') AS t1;
 t1
----
  1
(1 row)

contrib_regression=# 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)

contrib_regression=# 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)

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

dblink_cancel_query

Cancels active queries on a named connection.

Syntax

dblink_cancel_query(text connname) returns text

Description

You can use dblink_cancel_query to cancel queries that are being performed on a named connection. The cancellation may fail. For example, a complete remote query cannot be canceled. A cancel request only improves the odds that a query fails. You still must call dblink_get_result or use other methods to complete the common query protocol.

Parameters

Return value

If the cancel request is initiated, OK is returned. Otherwise, the text of an error message is returned.

Example

SELECT dblink_cancel_query('dtest1');