dblink
dblink is a contrib module which allows connections with
other databases.
dblink_connect
opens a persistent connection to a remote database
dblink_connect(text connstr)
dblink_connect(text connname, text connstr)
Inputs
connname
if 2 arguments ar given, the first is used as a name for a persistent
connection
connstr
standard libpq format connection string,
e.g. "hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd"
if only one argument is given, the connection is unnamed; only one unnamed
connection can exist at a time
Outputs
Returns status = "OK"
Example
select dblink_connect('dbname=postgres');
dblink_connect
----------------
OK
(1 row)
select dblink_connect('myconn','dbname=postgres');
dblink_connect
----------------
OK
(1 row)
dblink_disconnect
closes a persistent connection to a remote database
dblink_disconnect()
dblink_disconnect(text connname)
Inputs
connname
if an argument is given, it is used as a name for a persistent
connection to close; otherwiase the unnamed connection is closed
Outputs
Returns status = "OK"
Example
test=# select dblink_disconnect();
dblink_disconnect
-------------------
OK
(1 row)
select dblink_disconnect('myconn');
dblink_disconnect
-------------------
OK
(1 row)
dblink_open
opens a cursor on a remote database
dblink_open(text cursorname, text sql [, bool fail_on_error])
dblink_open(text connname, text cursorname, text sql [, bool fail_on_error])
Inputs
connname
if three arguments are present, the first is taken as the specific
connection name to use; otherwise the unnamed connection is assumed
cursorname
a reference name for the cursor
sql
sql statement that you wish to execute on the remote host
e.g. "select * from pg_class"
fail_on_error
If true (default when not present) then an ERROR thrown on the remote side
of the connection causes an ERROR to also be thrown locally. If false, the
remote ERROR is locally treated as a NOTICE, and the return value is set
to 'ERROR'.
Outputs
Returns status = "OK"
Note
dblink_connect(text connstr) must be executed first
dblink_open starts an explicit transaction. If, after using dblink_open,
you use dblink_exec to change data, and then an error occurs or you use
dblink_disconnect without a dblink_close first, your change *will* be
lost. Also, using dblink_close explicitly ends the transaction and thus
effectively closes *all* open cursors.
Example
test=# select dblink_connect('dbname=postgres');
dblink_connect
----------------
OK
(1 row)
test=# select dblink_open('foo','select proname, prosrc from pg_proc');
dblink_open
-------------
OK
(1 row)
dblink_fetch
returns a set from an open cursor on a remote database
dblink_fetch(text cursorname, int32 howmany [, bool fail_on_error])
dblink_fetch(text connname, text cursorname, int32 howmany [, bool fail_on_error])
Inputs
connname
if three arguments are present, the first is taken as the specific
connection name to use; otherwise the unnamed connection is assumed
cursorname
The reference name for the cursor
howmany
Maximum number of rows to retrieve. The next howmany rows are fetched,
starting at the current cursor position, moving forward. Once the cursor
has positioned to the end, no more rows are produced.
fail_on_error
If true (default when not present) then an ERROR thrown on the remote side
of the connection causes an ERROR to also be thrown locally. If false, the
remote ERROR is locally treated as a NOTICE, and no rows are returned.
Outputs
Returns setof record
Note
On a mismatch between the number of return fields as specified in the FROM
clause, and the actual number of fields returned by the remote cursor, an
ERROR will be thrown. In this event, the remote cursor is still advanced
by as many rows as it would have been if the ERROR had not occurred.
Example
test=# select dblink_connect('dbname=postgres');
dblink_connect
----------------
OK
(1 row)
test=# select dblink_open('foo','select proname, prosrc from pg_proc where proname like ''bytea%''');
dblink_open
-------------
OK
(1 row)
test=# 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)
test=# 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)
test=# select * from dblink_fetch('foo',5) as (funcname name, source text);
funcname | source
------------+------------
byteanlike | byteanlike
byteaout | byteaout
(2 rows)
test=# select * from dblink_fetch('foo',5) as (funcname name, source text);
funcname | source
----------+--------
(0 rows)
dblink_close
closes a cursor on a remote database
dblink_close(text cursorname [, bool fail_on_error])
dblink_close(text connname, text cursorname [, bool fail_on_error])
Inputs
connname
if two arguments are present, the first is taken as the specific
connection name to use; otherwise the unnamed connection is assumed
cursorname
a reference name for the cursor
fail_on_error
If true (default when not present) then an ERROR thrown on the remote side
of the connection causes an ERROR to also be thrown locally. If false, the
remote ERROR is locally treated as a NOTICE, and the return value is set
to 'ERROR'.
Outputs
Returns status = "OK"
Note
dblink_connect(text connstr) or dblink_connect(text connname, text connstr)
must be executed first.
Example
test=# select dblink_connect('dbname=postgres');
dblink_connect
----------------
OK
(1 row)
test=# select dblink_open('foo','select proname, prosrc from pg_proc');
dblink_open
-------------
OK
(1 row)
test=# select dblink_close('foo');
dblink_close
--------------
OK
(1 row)
select dblink_connect('myconn','dbname=regression');
dblink_connect
----------------
OK
(1 row)
select dblink_open('myconn','foo','select proname, prosrc from pg_proc');
dblink_open
-------------
OK
(1 row)
select dblink_close('myconn','foo');
dblink_close
--------------
OK
(1 row)
dblink_exec
executes an UPDATE/INSERT/DELETE on a remote database
dblink_exec(text connstr, text sql [, bool fail_on_error])
dblink_exec(text connname, text sql [, bool fail_on_error])
dblink_exec(text sql [, bool fail_on_error])
Inputs
connname/connstr
If two arguments are present, the first is first assumed to be a specific
connection name to use. If the name is not found, the argument is then
assumed to be a valid connection string, of standard libpq format,
e.g.: "hostaddr=127.0.0.1 dbname=mydb user=postgres password=mypasswd"
If only one argument is used, then the unnamed connection is used.
sql
sql statement that you wish to execute on the remote host, e.g.:
insert into foo values(0,'a','{"a0","b0","c0"}');
fail_on_error
If true (default when not present) then an ERROR thrown on the remote side
of the connection causes an ERROR to also be thrown locally. If false, the
remote ERROR is locally treated as a NOTICE, and the return value is set
to 'ERROR'.
Outputs
Returns status of the command, or 'ERROR' if the command failed.
Notes
dblink_open starts an explicit transaction. If, after using dblink_open,
you use dblink_exec to change data, and then an error occurs or you use
dblink_disconnect without a dblink_close first, your change *will* be
lost.
Example
select dblink_connect('dbname=dblink_test_slave');
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_current_query
returns the current query string
dblink_current_query () RETURNS text
Inputs
None
Outputs
Returns test -- a copy of the currenty executing query
Example
test=# select dblink_current_query() from (select dblink('dbname=postgres','select oid, proname from pg_proc where proname = ''byteacat''') as f1) as t1;
dblink_current_query
-----------------------------------------------------------------------------------------------------------------------------------------------------
select dblink_current_query() from (select dblink('dbname=postgres','select oid, proname from pg_proc where proname = ''byteacat''') as f1) as t1;
(1 row)
dblink_get_pkey
returns the position and field names of a relation's
primary key fields
dblink_get_pkey(text relname) RETURNS setof dblink_pkey_results
Inputs
relname
any relation name;
e.g. 'foobar'
Outputs
Returns setof dblink_pkey_results -- one row for each primary key field,
in order of position in the key. dblink_pkey_results is defined as follows:
CREATE TYPE dblink_pkey_results AS (position int4, colname text);
Example
test=# select * from dblink_get_pkey('foobar');
position | colname
----------+---------
1 | f1
2 | f2
3 | f3
4 | f4
5 | f5
dblink_build_sql_insert
builds an insert statement using a local tuple, replacing the
selection key field values with alternate supplied values
dblink_build_sql_insert(text relname
,int2vector primary_key_attnums
,int2 num_primary_key_atts
,_text src_pk_att_vals_array
,_text tgt_pk_att_vals_array) RETURNS text
Inputs
relname
any relation name;
e.g. 'foobar';
primary_key_attnums
vector of primary key attnums (1 based, see pg_index.indkey);
e.g. '1 2'
num_primary_key_atts
number of primary key attnums in the vector; e.g. 2
src_pk_att_vals_array
array of primary key values, used to look up the local matching
tuple, the values of which are then used to construct the SQL
statement
tgt_pk_att_vals_array
array of primary key values, used to replace the local tuple
values in the SQL statement
Outputs
Returns text -- requested SQL statement
Example
test=# 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 supplied values for selection
key field values
dblink_build_sql_delete(text relname
,int2vector primary_key_attnums
,int2 num_primary_key_atts
,_text tgt_pk_att_vals_array) RETURNS text
Inputs
relname
any relation name;
e.g. 'foobar';
primary_key_attnums
vector of primary key attnums (1 based, see pg_index.indkey);
e.g. '1 2'
num_primary_key_atts
number of primary key attnums in the vector; e.g. 2
src_pk_att_vals_array
array of primary key values, used to look up the local matching
tuple, the values of which are then used to construct the SQL
statement
tgt_pk_att_vals_array
array of primary key values, used to replace the local tuple
values in the SQL statement
Outputs
Returns text -- requested SQL statement
Example
test=# 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 selection key field values with alternate supplied values
dblink_build_sql_update(text relname
,int2vector primary_key_attnums
,int2 num_primary_key_atts
,_text src_pk_att_vals_array
,_text tgt_pk_att_vals_array) RETURNS text
Inputs
relname
any relation name;
e.g. 'foobar';
primary_key_attnums
vector of primary key attnums (1 based, see pg_index.indkey);
e.g. '1 2'
num_primary_key_atts
number of primary key attnums in the vector; e.g. 2
src_pk_att_vals_array
array of primary key values, used to look up the local matching
tuple, the values of which are then used to construct the SQL
statement
tgt_pk_att_vals_array
array of primary key values, used to replace the local tuple
values in the SQL statement
Outputs
Returns text -- requested SQL statement
Example
test=# 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)
dblink_get_connections
returns a text array of all active named dblink connections
dblink_get_connections() RETURNS text[]
Inputs
none
Outputs
Returns text array of all active named dblink connections
Example
SELECT dblink_get_connections();
dblink_is_busy
checks to see if named connection is busy with an async query
dblink_is_busy(text connname) RETURNS int
Inputs
connname
The specific connection name to use
Outputs
Returns 1 if connection is busy, 0 if it is not busy.
If this function returns 0, it is guaranteed that dblink_get_result
will not block.
Example
SELECT dblink_is_busy('dtest1');
dblink_cancel_query
cancels any active query on the named connection
dblink_cancel_query(text connname) RETURNS text
Inputs
connname
The specific connection name to use.
Outputs
Returns "OK" on success, or an error message on failure.
Example
SELECT dblink_cancel_query('dtest1');
dblink_error_message
gets last error message on the named connection
dblink_error_message(text connname) RETURNS text
Inputs
connname
The specific connection name to use.
Outputs
Returns last error message.
Example
SELECT dblink_error_message('dtest1');
dblink
returns a set from a remote database
dblink(text connstr, text sql [, bool fail_on_error])
dblink(text connname, text sql [, bool fail_on_error])
dblink(text sql [, bool fail_on_error])
Inputs
connname/connstr
If two arguments are present, the first is first assumed to be a specific
connection name to use. If the name is not found, the argument is then
assumed to be a valid connection string, of standard libpq format,
e.g.: "hostaddr=127.0.0.1 dbname=mydb user=postgres password=mypasswd"
If only one argument is used, then the unnamed connection is used.
sql
sql statement that you wish to execute on the remote host
e.g. "select * from pg_class"
fail_on_error
If true (default when not present) then an ERROR thrown on the remote side
of the connection causes an ERROR to also be thrown locally. If false, the
remote ERROR is locally treated as a NOTICE, and no rows are returned.
Outputs
Returns setof record
Example
select * from dblink('dbname=postgres','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');
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');
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)
A more convenient way to use dblink may be to create a view:
create view myremote_pg_proc as
select *
from dblink('dbname=postgres','select proname, prosrc from pg_proc')
as t1(proname name, prosrc text);
Then you can simply write:
select * from myremote_pg_proc where proname like 'bytea%';
dblink_send_query
sends an async query to a remote database
dblink_send_query(text connname, text sql)
Inputs
connname
The specific connection name to use.
sql
sql statement that you wish to execute on the remote host
e.g. "select * from pg_class"
Outputs
Returns int. A return value of 1 if the query was successfully dispatched,
0 otherwise. If 1, results must be fetched by dblink_get_result(connname).
A running query may be cancelled by dblink_cancel_query(connname).
Example
SELECT dblink_connect('dtest1', 'dbname=contrib_regression');
SELECT * FROM
dblink_send_query('dtest1', 'SELECT * FROM foo WHERE f1 < 3') AS t1;
dblink_get_result
gets an async query result
dblink_get_result(text connname [, bool fail_on_error])
Inputs
connname
The specific connection name to use. An asynchronous query must
have already been sent using dblink_send_query()
fail_on_error
If true (default when not present) then an ERROR thrown on the remote side
of the connection causes an ERROR to also be thrown locally. If false, the
remote ERROR is locally treated as a NOTICE, and no rows are returned.
Outputs
Returns setof record
Notes
Blocks until a result gets available.
This function *must* be called if dblink_send_query returned
a 1, even on cancelled queries - otherwise the connection
can't be used anymore. It must be called once for each query
sent, and one additional time to obtain an empty set result,
prior to using the connection again.
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
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)