PolarDB:dblink extensions

Syntax

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

Description

dblink_connect establishes a connection to a remote PostgreSQL database. The target server and database are identified by a standard libpq connection string. The connection persists until you close it explicitly or the database session ends.

• Named connections: Assign a name to open multiple connections at the same time.

• Unnamed connections: Omit the name to open a single unnamed connection. Opening a second unnamed connection replaces the first.

• Foreign servers: Pass the name of an existing foreign server as the connection string. Use the dblink_fdw foreign-data wrapper to define foreign servers.

Parameters

Return value

Returns OK on success. Reports an error otherwise.

Security notes

• Only superusers can create connections that do not use password authentication. Non-superusers must use password authentication.

Examples

Connect with a connection string:

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

Open a named connection:

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

Use a foreign server:

-- Note: local connection must require password authentication
-- Error if not: "password is required – Non-superuser cannot connect if
-- the server does not request a password."

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;

Syntax

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

Description

dblink_disconnect closes a connection opened by dblink_connect. If no name is specified, the unnamed connection is closed.

Parameters

Return value

Returns OK on success. Reports an error otherwise.

Examples

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

Description

dblink runs an SQL query—typically a SELECT statement—in a remote database and returns the resulting rows.

When called with two text parameters, the first is treated as a named connection. If no matching connection is found, it is used as a connection string and the connection is closed after the query completes.

Because dblink can run any query, it returns record rather than a fixed set of columns. You must declare the expected columns in the FROM clause alias of the calling query. PostgreSQL uses this declaration to resolve column references before the function runs. At runtime, the column count must match exactly; column names need not match and types are matched loosely (the returned strings must be valid input for the declared types).

Parameters

Return value

Returns the rows produced by the query. Declare the column names and types in the FROM clause alias, for 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%';

Examples

Query using a connection string:

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)

Query using an unnamed persistent connection:

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)

Query using a named persistent connection:

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)

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

dblink_exec runs a command that does not return rows—such as INSERT, UPDATE, or DELETE—in a remote database.

When called with two text parameters, the first is treated as a named connection. If no matching connection is found, it is used as a connection string and the connection is closed after the command completes.

Parameters

Return value

Returns the command status string (for example, INSERT 0 1) on success, or ERROR if fail_on_error is set to false and an error occurs.

Examples

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)

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

dblink_open opens a cursor in a remote database. Use dblink_fetch to retrieve rows and dblink_close to close the cursor.

Because a cursor can only persist within a transaction, dblink_open issues a BEGIN on the remote side if the remote database is not already in a transaction. This transaction is committed when the matching dblink_close runs.

Parameters

Return value

Returns OK or ERROR.

Usage notes

Examples

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

Description

dblink_fetch retrieves rows from a cursor opened by dblink_open. Retrieval starts at the cursor's current position and stops when howmany rows are retrieved or the cursor reaches the end.

If the column count returned by the remote cursor does not match what is declared in the FROM clause, an error is raised and the cursor position is preserved. For column declaration syntax, see the dblink section.

Parameters

Return value

Returns the fetched rows. Declare the column names and types in the FROM clause alias (same as dblink).

Examples

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)

Syntax

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

Description

dblink_close closes a cursor opened by dblink_open. If the cursor is the last open cursor on a connection and dblink_open started an explicit transaction block, dblink_close issues a COMMIT to close the transaction.

Parameters

Return value

Returns OK or ERROR.

Examples

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)

Syntax

dblink_get_connections() returns text[]

Description

dblink_get_connections returns an array of names of all currently open named dblink connections.

Return value

Returns a text array of connection names, or NULL if no named connections are open.

Examples

SELECT dblink_get_connections();

Syntax

dblink_error_message(text connname) returns text

Description

dblink_error_message retrieves the most recent error message on a specific named connection.

Parameters

Return value

Returns the last error message, or an empty string if no error has occurred on the connection.

Examples

SELECT dblink_error_message('dtest1');

Syntax

dblink_send_query(text connname, text sql) returns int

Description

dblink_send_query submits a query to a remote database without waiting for it to complete. Only one asynchronous query can be active on a given connection at a time.

After sending, use dblink_is_busy to check status, dblink_get_result to collect results, and dblink_cancel_query to cancel if needed.

Parameters

Return value

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

Examples

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

Syntax

dblink_is_busy(text connname) returns int

Description

dblink_is_busy checks whether an asynchronous query is still running on a connection.

Parameters

Return value

Returns 1 if an asynchronous query is in progress. Returns 0 if the connection is idle—in which case dblink_get_result will not block.

Examples

SELECT dblink_is_busy('dtest1');

Syntax

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

Description

dblink_get_result collects the result of an asynchronous query sent by dblink_send_query. If the query is not yet complete, the function waits until it finishes.

Parameters

Return value

• Row-returning queries: Returns the rows produced by the query. Declare the column names and types in the FROM clause alias (same as dblink).

• Non-row-returning commands: Returns a single row with a single text column containing the command status string. Declare a single text column in the FROM clause.

Usage notes

• Call dblink_get_result once for each query sent, plus one additional time to drain the empty-set result, before the connection can be reused.

• When using dblink_send_query and dblink_get_result, dblink fetches the entire remote query result before returning any rows. For large result sets, this can cause memory bloat in the local session. To avoid this, use dblink_open to open a cursor and fetch rows in batches with dblink_fetch. Alternatively, use dblink(), which helps prevent the memory bloat caused by spooling large result sets to a disk.

Examples

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)

Syntax

dblink_cancel_query(text connname) returns text

Description

dblink_cancel_query sends a cancel request to the remote database for queries running on a named connection. Cancellation is not guaranteed—a query that has already completed cannot be canceled. A cancel request only improves the odds that a query fails. After sending a cancel request, still call dblink_get_result or use other methods to complete the query protocol.

Parameters

Return value

Returns OK if the cancel request was sent, or an error message string if it could not be sent.

Examples

SELECT dblink_cancel_query('dtest1');