Skip to main content
Version: 1.0.16

dblink

dblink is a module that supports connecting to other databases within a database session.

Also see postgres_fdw, which provides similar functionality in a more modern and standards-compliant architecture.

dblink_connect — Opens a persistent connection to a remote database

Synopsis​

dblink_connect(text connstr) returns text

dblink_connect(text connname, text connstr) returns text

Description​

dblink_connect() establishes a connection to a remote database. The server and database to contact are identified by a standard libpq connection string. Optionally, a name can be assigned to the connection. Multiple named connections can be open at once, but only one unnamed connection is allowed at a time. The connection persists until it is closed or the database session ends.

The connection string can also be the name of an existing foreign server. When using a foreign server, we recommend using the dblink_fdw foreign data wrapper. See the example below, as well as CREATE SERVER and CREATE USER MAPPING.

Parameters​

connname

The name to use for this connection. If omitted, an unnamed connection is opened, replacing any existing unnamed connection.

connstr

A libpq-style connection information string, for example hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd. This can also be the name of a foreign server.

Return Value​

Returns status, which is always OK (any error causes the function to throw an error rather than returning).

Notes​

If untrusted users can access a database that does not use a secure schema usage pattern, you should remove publicly writable schemas from the search_path at the start of each session. For example, you can add options=-csearch_path= to connstr. This consideration is not specific to dblink; it applies to every interface that executes arbitrary SQL commands.

Only superusers can use dblink_connect to create password-free authenticated connections. If non-superusers need this capability, use dblink_connect_u.

It is inadvisable to choose connection names containing equals signs, as this creates a risk of confusion with connection information strings in other dblink functions.

Examples​

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

dblink_connect

----------------

OK

(1 row)


SELECT dblink_connect('myconn', '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 (hostaddr '127.0.0.1', 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_connect_u — Opens a persistent connection to a remote database, insecurely

Synopsis​

dblink_connect_u(text connstr) returns text

dblink_connect_u(text connname, text connstr) returns text

Description​

dblink_connect_u() is identical to dblink_connect(), except that it allows non-superusers to connect using any authentication method.

If the remote server selects an authentication method that does not involve a password, impersonation and subsequent privilege escalation may occur, since the session will appear to have been initiated by the user running Halo. Furthermore, even if the remote server does not require a password, a password may be provided from the server environment, such as a ~/.pgpass file belonging to the server user. This poses not only an impersonation risk but also the risk of exposing the password to an untrusted remote server. Therefore, dblink_connect_u() is initially installed with all privileges revoked from PUBLIC, making it callable only by superusers. In some cases, it may be appropriate to grant EXECUTE privilege on dblink_connect_u() to specific trusted users, but this must be done with care. We also recommend that any ~/.pgpass file belonging to the server user should not contain any records specifying a wildcard hostname.

See dblink_connect() for more details.

dblink_disconnect — Closes a persistent connection to a remote database

Synopsis​

dblink_disconnect() returns text

dblink_disconnect(text connname) returns text

Description​

dblink_disconnect() closes a connection previously opened by dblink_connect(). The form without arguments closes the unnamed connection.

Parameters​

connname

The name of the named connection to be closed.

Return Value​

Returns OK (any error causes the function to throw an error rather than returning).

Examples​

SELECT dblink_disconnect();

dblink_disconnect

-------------------

OK

(1 row)


SELECT dblink_disconnect('myconn');

dblink_disconnect

-------------------

OK

(1 row)

dblink — Executes a query in a remote database

Synopsis​

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 executes a query (usually a SELECT, but it can be any SQL statement that returns rows) in a remote database.

When given two text arguments, the first is first looked up as a persistent connection name; if found, the command is executed on that connection. If not found, the first argument is treated as a connection information string for dblink_connect, and the indicated connection is established only for the duration of this command.

Parameters​

connname

The name of the connection to use. Omitting this parameter uses the unnamed connection.

connstr

A connection information string as previously described for dblink_connect.

sql

The SQL query you wish to execute in the remote database, for example select * from foo.

fail_on_error

If true (the default when omitted), an error thrown on the remote side of the connection also causes a local error to be thrown. If false, the remote error is reported locally only as a NOTICE, and the function returns no rows.

Return Value​

The function returns the rows produced by the query. Since dblink can be used with any query, it is declared as returning record rather than specifying any particular set of columns. This means you must specify the expected set of columns in the calling query — otherwise Halo will not know what to expect. Here is an 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 "alias" portion of the FROM clause must specify the column names and types that the function will return (specifying column names in an alias is actually standard SQL syntax, but specifying column types is a Halo extension). This allows the system to understand what * will expand to and what proname in the WHERE clause refers to before attempting to execute the function. At runtime, if the actual query result from the remote database has a different number of columns than shown in the FROM clause, an error will be thrown. However, column names do not need to match, and dblink does not insist on exact type matching. As long as the returned data strings are valid input for the column types declared in the FROM clause, it will succeed.

Notes​

A convenient way to use predefined queries with dblink is to create a view. This allows the column type information to be embedded in the view rather than spelled out in every query. For example:

test=## CREATE VIEW myremote_pg_proc AS

test-## SELECT *

test-## FROM dblink('dbname=postgres options=-csearch_path=',

test(## 'select proname, prosrc from pg_proc')

test-## AS t1(proname name, prosrc text);

CREATE VIEW

SELECT * FROM myremote_pg_proc WHERE proname LIKE 'bytea%';

Examples​

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 — Executes a command in a remote database

Synopsis​

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 executes a command (i.e., any SQL statement that does not return rows) in a remote database.

When given two text arguments, the first is first looked up as a persistent connection name; if found, the command is executed on that connection. If not found, the first argument is treated as a connection information string for dblink_connect, and the indicated connection is established only for the duration of this command.

Parameters​

connname

The name of the connection to use. Omitting this parameter uses the unnamed connection.

connstr

A connection information string as previously described for dblink_connect.

sql

The SQL command you wish to execute in the remote database, for example insert into foo values(0, 'a',

'{"a0","b0","c0"}').

fail_on_error

If true (the default when omitted), an error thrown on the remote side of the connection also causes a local error to be thrown. If false, the remote error is reported locally only as a NOTICE, and the function's return value is set to ERROR.

Return Value

Returns status, either the command's status string or ERROR.

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)

dblink_open — Opens a cursor in a remote database

Synopsis​

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. The cursor can subsequently be manipulated using dblink_fetch() and dblink_close().

Parameters​

connname

The name of the connection to use. Omitting this parameter uses the unnamed connection.

cursorname

The name to assign to this cursor.

sql

The SELECT statement you wish to execute in the remote database, for example select * from pg_class.

fail_on_error

If true (the default when omitted), an error thrown on the remote side of the connection also causes a local error to be thrown. If false, the remote error is reported locally only as a NOTICE, and the function's return value is set to ERROR.

Return Value

Returns status, either OK or ERROR.

Notes​

Since a cursor can only persist within a transaction, if the remote side is not already in a transaction, dblink_open starts an explicit transaction block (BEGIN) on the remote side. This transaction will be closed again when the matching dblink_close is executed. Note that if you use dblink_exec to modify data between dblink_open and dblink_close, and then an error occurs or you use dblink_disconnect before dblink_close, your changes will be lost because the transaction will be aborted.

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)

dblink_fetch — Returns rows from an open cursor in a remote database

Synopsis​

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 previously established by dblink_open.

Parameters​

connname

The name of the connection to use. Omitting this parameter uses the unnamed connection.

cursorname

The name of the cursor to fetch from.

howmany

The maximum number of rows to retrieve. The next howmany rows from the current cursor position are fetched. Once the cursor has reached its end, no more rows will be produced.

fail_on_error

If true (the default when omitted), an error thrown on the remote side of the connection also causes a local error to be thrown. If false, the remote error is reported locally only as a NOTICE, and the function returns no rows.

Return Value

The function returns the rows fetched from the cursor. To use this function, you will need to specify the desired set of columns, as discussed earlier for dblink.

Notes​

An error is thrown when the number of return columns specified in the FROM clause does not match the actual number of columns returned by the remote cursor.

In this event, the remote cursor is still advanced by the number of rows that were fetched before the error. The same applies to any errors that occur in the local query after the remote FETCH completes.

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)

dblink_close — Closes a cursor in a remote database

Synopsis​

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 previously opened by dblink_open.

Parameters​

connname

The name of the connection to use. Omitting this parameter uses the unnamed connection.

cursorname

The name of the cursor to close.

fail_on_error

If true (the default when omitted), an error thrown on the remote side of the connection also causes a local error to be thrown. If false, the remote error is reported locally only as a NOTICE, and the function's return value is set to ERROR.

Return Value

Returns status, either OK or ERROR.

Notes​

If dblink_open started an explicit transaction block and this is the last remaining open cursor in the connection, dblink_close will issue a matching COMMIT.

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)

dblink_get_connections — Returns the names of all open named dblink connections

Synopsis​

dblink_get_connections() returns text[]

Description​

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

Return Value​

Returns a text array of connection names, or NULL if there are none.

Examples​

SELECT dblink_get_connections();

dblink_error_message — Gets the last error message on the named connection

Synopsis​

dblink_error_message(text connname) returns text

Description​

dblink_error_message fetches the most recent remote error message for the given connection.

Parameters​

connname

The name of the connection to use.

Return Value

Returns the last error message, or OK if there has been no error on this connection.

Notes​

When an asynchronous query is initiated by dblink_send_query, the error message associated with the connection may not be updated until the server's response message has been consumed. This generally means that dblink_is_busy or dblink_get_result should be called before dblink_error_message so that any errors generated by the asynchronous query are visible.

Examples​

SELECT dblink_error_message('dtest1');

dblink_send_query — Sends an asynchronous query to a remote database

Synopsis​

dblink_send_query(text connname, text sql) returns int

Description​

dblink_send_query sends a query to be executed asynchronously, without immediately waiting for the result. There must not be an asynchronous query already in progress on this connection.

After successfully dispatching an asynchronous query, completion status can be checked with dblink_is_busy, and the result can eventually be collected with dlink_get_result. An active asynchronous query can also be canceled using dblink_cancel_query.

Parameters​

connname

The name of the connection to use.

sql​

The SQL statement you wish to execute in the remote database, for example select * from pg_class.

Returns 1 if the query was successfully dispatched, or 0 otherwise.

Examples​

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

dblink_is_busy — Checks whether a connection is busy with an asynchronous query

Synopsis​

dblink_is_busy(text connname) returns int

Description​

dblink_is_busy tests whether an asynchronous query is in progress.

Parameters​

connname

The name of the connection to check.

Return Value​

Returns 1 if the connection is busy, 0 if not busy. If this function returns 0, dblink_get_result is guaranteed not to block.

Examples​

SELECT dblink_is_busy('dtest1');

dblink_get_notify — Retrieves asynchronous notifications on a connection

Synopsis​

dblink_get_notify() returns setof (notify_name text, be_pid int, extra text)

dblink_get_notify(text connname) returns setof (notify_name text, be_pid int, extra text)

Description​

dblink_get_notify retrieves notifications on the unnamed connection or a specified named connection. To receive notifications via dblink, you must first issue LISTEN using dblink_exec.

Parameters​

connname

The name of the named connection on which to receive notifications.

Return Value​

Returns setof (notify_name text, be_pid int, extra text), or an empty set.

Examples​

SELECT dblink_exec('LISTEN virtual');

dblink_exec

-------------

LISTEN

(1 row)

SELECT * FROM dblink_get_notify();

notify_name | be_pid | extra

-------------+--------+-------

(0 rows)

NOTIFY virtual;

NOTIFY

SELECT * FROM dblink_get_notify();

notify_name | be_pid | extra

-------------+--------+-------

virtual | 1229 |

(1 row)

dblink_get_result — Gets an asynchronous query result

Synopsis​

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

Description​

dblink_get_result collects the result of an asynchronous query previously sent by dblink_send_query. If the query has not yet completed, dblink_get_result will wait until it finishes.

Parameters​

connname

The name of the connection to use.

fail_on_error

If true (the default when omitted), an error thrown on the remote side of the connection also causes a local error to be thrown. If false, the remote error is reported locally only as a NOTICE, and the function returns no rows.

Return Value

For an asynchronous query (i.e., an SQL statement that returns rows), this function returns the rows produced by the query. To use this function, you will need to specify the expected set of columns, as discussed earlier for dblink.

For an asynchronous command (i.e., an SQL statement that does not return rows), this function returns a single row with a single text column containing the command's status string. The result must be specified in the calling FROM clause as having a single text row.

Notes​

This function must be called if dblink_send_query returns 1. It must be called once for each sent query, and then called one additional time to get an empty result set before the connection becomes available again.

When using dblink_send_query and dblink_get_result, dblink fetches the entire remote query result before returning any rows in the result set to the local query processor. If the query returns a large number of rows, this may cause transient memory bloat in the local session. It is better to open such a query as a cursor using dblink_open and then fetch a manageable number of rows at a time. Alternatively, you can use the simple dblink() function, which avoids memory bloat by buffering large result sets to 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)

dblink_cancel_query — Cancels any active query on the named connection

Synopsis​

dblink_cancel_query(text connname) returns text

Description​

dblink_cancel_query attempts to cancel any query in progress on the named connection. Note that this is not guaranteed to succeed (for example, the remote query may have already finished). A cancel request merely increases the likelihood that the query will fail soon. You must still complete the normal query protocol, for example by calling dblink_get_result.

Parameters

connname

The name of the connection to use.

Return Value

Returns OK if the cancel request was sent, or a text error message if it failed.

Examples​

SELECT dblink_cancel_query('dtest1');

dblink_get_pkey — Returns the primary key column positions and column names of a relation

Synopsis​

dblink_get_pkey(text relname) returns setof dblink_pkey_results

Description​

dblink_get_pkey provides information about the primary key of a relation in the local database. This is useful for generating queries to be sent to a remote database.

Parameters​

relname

The name of a local relation, for example foo or myschema.mytab. If the name is mixed-case or contains special characters, enclose it in double quotes, for example "FooBar"; without quotes, the string will be folded to lowercase.

Return Value

Returns one row per primary key column, or no rows if the relation has no primary key. The result row type is defined as:

CREATE TYPE dblink_pkey_results AS (position int, colname text);

The position column value ranges from 1 to N, representing the column's ordinal position in the primary key, not in the table columns.

Examples​

CREATE TABLE foobar (

f1 int,

f2 int,

f3 int,

PRIMARY KEY (f1, f2, f3)

);

CREATE TABLE

SELECT * FROM dblink_get_pkey('foobar');

position | colname

----------+---------

1 | f1

2 | f2

3 | f3

(3 rows)



dblink_build_sql_insert — Builds an INSERT statement using a local tuple, replacing the primary key column values with the provided values

Synopsis​

dblink_build_sql_insert(text relname,

int2vector primary_key_attnums,

integer num_primary_key_atts,

text[] src_pk_att_vals_array,

text[] tgt_pk_att_vals_array) returns text

Description​

dblink_build_sql_insert is useful when selectively copying a local table to a remote database. It selects a row from the local table based on the primary key, then builds an INSERT command that copies the row but with the primary key values replaced by those in the last parameter (to create an exact copy of the row, simply specify the same values for the last two parameters).

Parameters​

relname The name of a local relation, for example foo or myschema.mytab. If the name is mixed-case or contains special characters, enclose it in double quotes, for example "FooBar"; without quotes, the string will be folded to lowercase.

primary_key_attnums The attribute numbers (starting from 1) of the primary key columns, for example 1 2. num_primary_key_atts The number of primary key columns. src_pk_att_vals_array The primary key column values used to look up the local tuple. Each column is represented as text. If no row has these primary key values, an error is thrown.

tgt_pk_att_vals_array The primary key column values to be substituted into the resulting INSERT command. Each column is represented as text.

Return Value​

Returns the requested SQL statement as text.

Notes​

The attribute numbers in primary_key_attnums are interpreted as logical column numbers, corresponding to the position of columns in SELECT * FROM relname. This may produce unexpected results if any columns to the left of those indicated have been dropped during the lifetime of the table.

Examples​

SELECT dblink_build_sql_insert('foo', '1 2', 2, '{"1", "a"}', '{"1", "b''a"}');

 dblink_build_sql_insert

--------------------------------------------------

INSERT INTO foo(f1,f2,f3) VALUES('1','b''a','1')

(1 row)

dblink_build_sql_delete — Builds a DELETE statement using the provided primary key column values

Synopsis​

dblink_build_sql_delete(text relname, int2vector primary_key_attnums, integer num_primary_key_atts, text[] tgt_pk_att_vals_array) returns text

Description​

dblink_build_sql_delete is useful when selectively copying a local table to a remote database. It builds a SQL DELETE command to delete the row with the given primary key values.

Parameters​

relname

The name of a local relation, for example foo or myschema.mytab. If the name is mixed-case or contains special characters, enclose it in double quotes, for example "FooBar"; without quotes, the string will be folded to lowercase.

primary_key_attnums The attribute numbers (starting from 1) of the primary key columns, for example 1 2. num_primary_key_atts The number of primary key columns. tgt_pk_att_vals_array The primary key column values to use in the resulting DELETE command. Each column is represented as text. The return value will be the requested SQL statement as text.

Notes​

The attribute numbers in primary_key_attnums are interpreted as logical column numbers, corresponding to the position of columns in SELECT * FROM relname. This may produce unexpected results if any columns to the left of those indicated have been dropped during the lifetime of the table.

Examples​

SELECT dblink_build_sql_delete('"MyFoo"', '1 2', 2, '{"1", "b"}'); dblink_build_sql_delete

---------------------------------------------

DELETE FROM "MyFoo" WHERE f1='1' AND f2='b'

(1 row)

dblink_build_sql_update — Builds an UPDATE statement using a local tuple, replacing the primary key column values with the provided values

Synopsis​

dblink_build_sql_update(text relname, int2vector primary_key_attnums, integer num_primary_key_atts, text[] src_pk_att_vals_array, text[] tgt_pk_att_vals_array) returns text

Description​

dblink_build_sql_update is useful when selectively copying a local table to a remote database. It selects a row from the local table based on the primary key, then builds an UPDATE command to copy the row but with the primary key values replaced by those in the last parameter (to create an exact copy of the row, simply specify the same values for the last two parameters). The UPDATE command always assigns values to all columns in the row — the main difference between this function and dblink_build_sql_insert is that it assumes the target row already exists in the remote table.

Parameters​

relname

The name of a local relation, for example foo or myschema.mytab. If the name is mixed-case or contains special characters, enclose it in double quotes, for example "FooBar"; without quotes, the string will be folded to lowercase.

primary_key_attnums

The attribute numbers (starting from 1) of the primary key columns, for example 1 2.

num_primary_key_atts

The number of primary key columns.

src_pk_att_vals_array

The primary key column values used to look up the local tuple. Each column is represented as text. If no row has these primary key values, an error is thrown.

tgt_pk_att_vals_array The primary key column values to use in the resulting UPDATE command. Each column is represented as text. The return value is the requested SQL statement as text.

Notes​

The attribute numbers in primary_key_attnums are interpreted as logical column numbers, corresponding to the position of columns in SELECT * FROM relname. This may produce unexpected results if any columns to the left of those indicated have been dropped during the lifetime of the table.

Examples​

SELECT dblink_build_sql_update('foo', '1 2', 2, '{"1", "a"}', '{"1", "b"}');

dblink_build_sql_update

-------------------------------------------------------------

UPDATE foo SET f1='1',f2='b',f3='1' WHERE f1='1' AND f2='b'

(1 row)