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)