diff options
Diffstat (limited to 'doc/src/sgml/dblink.sgml')
| -rw-r--r-- | doc/src/sgml/dblink.sgml | 1312 |
1 files changed, 1312 insertions, 0 deletions
diff --git a/doc/src/sgml/dblink.sgml b/doc/src/sgml/dblink.sgml new file mode 100644 index 0000000000..095d600099 --- /dev/null +++ b/doc/src/sgml/dblink.sgml @@ -0,0 +1,1312 @@ +<!-- <reference> --> +<sect1 id="dblink"> + <title>dblink</title> + <para> + dblink is a contrib module which allows connections with + other databases. + </para> + + <!-- dblink_connect --> + + <refentry> + <refnamediv> + <refname>dblink_connect</refname> + <refpurpose>opens a persistent connection to a remote database</refpurpose> + </refnamediv> + + <refsynopsisdiv> + <synopsis> + dblink_connect(text connstr) + dblink_connect(text connname, text connstr) + </synopsis> + </refsynopsisdiv> + + <refsect1> + <title>Inputs</title> + + <refsect2> + <title>connname</title> + <para> + if 2 arguments ar given, the first is used as a name for a persistent + connection + </para> + </refsect2> + + <refsect2> + <title>connstr</title> + <para> + standard libpq format connection string, + e.g. "hostaddr=127.0.0.1 port=5432 dbname=mydb user=postgres password=mypasswd" + </para> + <para> + if only one argument is given, the connection is unnamed; only one unnamed + connection can exist at a time + </para> + </refsect2> + </refsect1> + + <refsect1> + <title>Outputs</title> + <para>Returns status = "OK"</para> + </refsect1> + + <refsect1> + <title>Example</title> + <programlisting> + select dblink_connect('dbname=postgres'); + dblink_connect + ---------------- + OK + (1 row) + + select dblink_connect('myconn','dbname=postgres'); + dblink_connect + ---------------- + OK + (1 row) + </programlisting> + </refsect1> + </refentry> + + <!-- dblink_disconnect --> + <refentry> + <refnamediv> + <refname>dblink_disconnect</refname> + <refpurpose>closes a persistent connection to a remote database</refpurpose> + </refnamediv> + + <refsynopsisdiv> + <synopsis> + dblink_disconnect() + dblink_disconnect(text connname) + </synopsis> + </refsynopsisdiv> + + <refsect1> + <title>Inputs</title> + + <refsect2> + <title>connname</title> + <para> + if an argument is given, it is used as a name for a persistent + connection to close; otherwiase the unnamed connection is closed + </para> + </refsect2> + </refsect1> + + <refsect1> + <title>Outputs</title> + <para>Returns status = "OK"</para> + </refsect1> + + <refsect1> + <title>Example</title> + <programlisting> + test=# select dblink_disconnect(); + dblink_disconnect + ------------------- + OK + (1 row) + + select dblink_disconnect('myconn'); + dblink_disconnect + ------------------- + OK + (1 row) + </programlisting> + </refsect1> + </refentry> + + <!-- dblink_open --> + <refentry> + <refnamediv> + <refname>dblink_open</refname> + <refpurpose>opens a cursor on a remote database</refpurpose> + </refnamediv> + + <refsynopsisdiv> + <synopsis> + dblink_open(text cursorname, text sql [, bool fail_on_error]) + dblink_open(text connname, text cursorname, text sql [, bool fail_on_error]) + </synopsis> + </refsynopsisdiv> + + <refsect1> + <title>Inputs</title> + + <refsect2> + <title>connname</title> + <para> + if three arguments are present, the first is taken as the specific + connection name to use; otherwise the unnamed connection is assumed + </para> + </refsect2> + + <refsect2> + <title>cursorname</title> + <para> + a reference name for the cursor + </para> + </refsect2> + + <refsect2> + <title>sql</title> + <para> + sql statement that you wish to execute on the remote host + e.g. "select * from pg_class" + </para> + </refsect2> + + <refsect2> + <title>fail_on_error</title> + <para> + 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'. + </para> + </refsect2> + </refsect1> + + <refsect1> + <title>Outputs</title> + <para>Returns status = "OK"</para> + </refsect1> + + <refsect1> + <title>Note</title> + <itemizedlist> + <listitem> + <para> + dblink_connect(text connstr) must be executed first + </para> + </listitem> + <listitem> + <para> + 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. + </para> + </listitem> + </itemizedlist> + + </refsect1> + <refsect1> + <title>Example</title> + <programlisting> + 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) + </programlisting> + </refsect1> + </refentry> + + <!-- dblink_fetch --> + <refentry> + <refnamediv> + <refname>dblink_fetch</refname> + <refpurpose>returns a set from an open cursor on a remote database</refpurpose> + </refnamediv> + + <refsynopsisdiv> + <synopsis> + dblink_fetch(text cursorname, int32 howmany [, bool fail_on_error]) + dblink_fetch(text connname, text cursorname, int32 howmany [, bool fail_on_error]) + </synopsis> + </refsynopsisdiv> + + <refsect1> + <title>Inputs</title> + + <refsect2> + <title>connname</title> + <para> + if three arguments are present, the first is taken as the specific + connection name to use; otherwise the unnamed connection is assumed + </para> + </refsect2> + + <refsect2> + <title>cursorname</title> + <para> + The reference name for the cursor + </para> + </refsect2> + + <refsect2> + <title>howmany</title> + <para> + 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. + </para> + </refsect2> + + <refsect2> + <title>fail_on_error</title> + <para> + 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. + </para> + </refsect2> + </refsect1> + + <refsect1> + <title>Outputs</title> + <para>Returns setof record</para> + </refsect1> + + <refsect1> + <title>Note</title> + <para> + 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. + </para> + </refsect1> + + <refsect1> + <title>Example</title> + <programlisting> + 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) + </programlisting> + </refsect1> + </refentry> + + <!-- dblink_close --> + <refentry> + <refnamediv> + <refname>dblink_close</refname> + <refpurpose>closes a cursor on a remote database</refpurpose> + </refnamediv> + + <refsynopsisdiv> + <synopsis> + dblink_close(text cursorname [, bool fail_on_error]) + dblink_close(text connname, text cursorname [, bool fail_on_error]) + </synopsis> + </refsynopsisdiv> + + <refsect1> + <title>Inputs</title> + + <refsect2> + <title>connname</title> + <para> + if two arguments are present, the first is taken as the specific + connection name to use; otherwise the unnamed connection is assumed + </para> + </refsect2> + + <refsect2> + <title>cursorname</title> + <para> + a reference name for the cursor + </para> + </refsect2> + + <refsect2> + <title>fail_on_error</title> + <para> + 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'. + </para> + </refsect2> + </refsect1> + + <refsect1> + <title>Outputs</title> + <para>Returns status = "OK"</para> + </refsect1> + + <refsect1> + <title>Note</title> + <para> + dblink_connect(text connstr) or dblink_connect(text connname, text connstr) + must be executed first. + </para> + </refsect1> + + <refsect1> + <title>Example</title> + <programlisting> + 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) + </programlisting> + </refsect1> + </refentry> + + <!-- dblink_exec --> + <refentry> + <refnamediv> + <refname>dblink_exec</refname> + <refpurpose>executes an UPDATE/INSERT/DELETE on a remote database</refpurpose> + </refnamediv> + + <refsynopsisdiv> + <synopsis> + 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]) + </synopsis> + </refsynopsisdiv> + + <refsect1> + <title>Inputs</title> + + <refsect2> + <title>connname/connstr</title> + <para> + 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. + </para> + </refsect2> + + <refsect2> + <title>sql</title> + <para> + sql statement that you wish to execute on the remote host, e.g.: + insert into foo values(0,'a','{"a0","b0","c0"}'); + </para> + </refsect2> + <refsect2> + <title>fail_on_error</title> + <para> + 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'. + </para> + </refsect2> + </refsect1> + + <refsect1> + <title>Outputs</title> + <para>Returns status of the command, or 'ERROR' if the command failed.</para> + </refsect1> + + <refsect1> + <title>Notes</title> + <para> + 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. + </para> + </refsect1> + + <refsect1> + <title>Example</title> + <programlisting> + 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) + </programlisting> + </refsect1> + </refentry> + + <!-- dblink_current_query --> + <refentry> + <refnamediv> + <refname>dblink_current_query</refname> + <refpurpose>returns the current query string</refpurpose> + </refnamediv> + + <refsynopsisdiv> + <synopsis> + dblink_current_query () RETURNS text + </synopsis> + </refsynopsisdiv> + + <refsect1> + <title>Inputs</title> + + <refsect2> + <title>None</title> + <para> + </para> + </refsect2> + </refsect1> + + <refsect1> + <title>Outputs</title> + <para>Returns test -- a copy of the currenty executing query</para> + </refsect1> + + <refsect1> + <title>Example</title> + <programlisting> + 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) + </programlisting> + </refsect1> + </refentry> + + <!-- dblink_get_pkey --> + <refentry> + <refnamediv> + <refname>dblink_get_pkey</refname> + <refpurpose>returns the position and field names of a relation's + primary key fields + </refpurpose> + </refnamediv> + + <refsynopsisdiv> + <synopsis> + dblink_get_pkey(text relname) RETURNS setof dblink_pkey_results + </synopsis> + </refsynopsisdiv> + + <refsect1> + <title>Inputs</title> + + <refsect2> + <title>relname</title> + <para> + any relation name; + e.g. 'foobar' + </para> + </refsect2> + </refsect1> + + <refsect1> + <title>Outputs</title> + <para> + 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); + </para> + </refsect1> + + <refsect1> + <title>Example</title> + <programlisting> + test=# select * from dblink_get_pkey('foobar'); + position | colname + ----------+--------- + 1 | f1 + 2 | f2 + 3 | f3 + 4 | f4 + 5 | f5 + </programlisting> + </refsect1> + </refentry> + + <!-- dblink_build_sql_insert --> + <refentry> + <refnamediv> + <refname>dblink_build_sql_insert</refname> + <refpurpose> + builds an insert statement using a local tuple, replacing the + selection key field values with alternate supplied values + </refpurpose> + </refnamediv> + + <refsynopsisdiv> + <synopsis> + 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 + </synopsis> + </refsynopsisdiv> + + <refsect1> + <title>Inputs</title> + + <refsect2> + <title>relname</title> + <para> + any relation name; + e.g. 'foobar'; + </para> + </refsect2> + <refsect2> + <title>primary_key_attnums</title> + <para> + vector of primary key attnums (1 based, see pg_index.indkey); + e.g. '1 2' + </para> + </refsect2> + <refsect2> + <title>num_primary_key_atts</title> + <para> + number of primary key attnums in the vector; e.g. 2 + </para> + </refsect2> + <refsect2> + <title>src_pk_att_vals_array</title> + <para> + 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 + </para> + </refsect2> + <refsect2> + <title>tgt_pk_att_vals_array</title> + <para> + array of primary key values, used to replace the local tuple + values in the SQL statement + </para> + </refsect2> + </refsect1> + + <refsect1> + <title>Outputs</title> + <para>Returns text -- requested SQL statement</para> + </refsect1> + + <refsect1> + <title>Example</title> + <programlisting> + 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) + </programlisting> + </refsect1> + </refentry> + + <!-- dblink_build_sql_delete --> + <refentry> + <refnamediv> + <refname>dblink_build_sql_delete</refname> + <refpurpose>builds a delete statement using supplied values for selection + key field values + </refpurpose> + </refnamediv> + + <refsynopsisdiv> + <synopsis> + dblink_build_sql_delete(text relname + ,int2vector primary_key_attnums + ,int2 num_primary_key_atts + ,_text tgt_pk_att_vals_array) RETURNS text + </synopsis> + </refsynopsisdiv> + + <refsect1> + <title>Inputs</title> + + <refsect2> + <title>relname</title> + <para> + any relation name; + e.g. 'foobar'; + </para> + </refsect2> + <refsect2> + <title>primary_key_attnums</title> + <para> + vector of primary key attnums (1 based, see pg_index.indkey); + e.g. '1 2' + </para> + </refsect2> + <refsect2> + <title>num_primary_key_atts</title> + <para> + number of primary key attnums in the vector; e.g. 2 + </para> + </refsect2> + <refsect2> + <title>src_pk_att_vals_array</title> + <para> + 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 + </para> + </refsect2> + <refsect2> + <title>tgt_pk_att_vals_array</title> + <para> + array of primary key values, used to replace the local tuple + values in the SQL statement + </para> + </refsect2> + </refsect1> + + <refsect1> + <title>Outputs</title> + <para>Returns text -- requested SQL statement</para> + </refsect1> + + <refsect1> + <title>Example</title> + <programlisting> + 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) + </programlisting> + </refsect1> + </refentry> + + <!-- dblink_build_sql_update --> + <refentry> + <refnamediv> + <refname>dblink_build_sql_update</refname> + <refpurpose>builds an update statement using a local tuple, replacing + the selection key field values with alternate supplied values + </refpurpose> + </refnamediv> + + <refsynopsisdiv> + <synopsis> + 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 + </synopsis> + </refsynopsisdiv> + + <refsect1> + <title>Inputs</title> + + <refsect2> + <title>relname</title> + <para> + any relation name; + e.g. 'foobar'; + </para> + </refsect2> + <refsect2> + <title>primary_key_attnums</title> + <para> + vector of primary key attnums (1 based, see pg_index.indkey); + e.g. '1 2' + </para> + </refsect2> + <refsect2> + <title>num_primary_key_atts</title> + <para> + number of primary key attnums in the vector; e.g. 2 + </para> + </refsect2> + <refsect2> + <title>src_pk_att_vals_array</title> + <para> + 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 + </para> + </refsect2> + <refsect2> + <title>tgt_pk_att_vals_array</title> + <para> + array of primary key values, used to replace the local tuple + values in the SQL statement + </para> + </refsect2> + </refsect1> + + <refsect1> + <title>Outputs</title> + <para>Returns text -- requested SQL statement</para> + </refsect1> + + <refsect1> + <title>Example</title> + <programlisting> + 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) + </programlisting> + </refsect1> + </refentry> + + <!-- dblink_get_connections --> + <refentry> + <refnamediv> + <refname>dblink_get_connections</refname> + <refpurpose>returns a text array of all active named dblink connections</refpurpose> + </refnamediv> + + <refsynopsisdiv> + <synopsis> + dblink_get_connections() RETURNS text[] + </synopsis> + </refsynopsisdiv> + + <refsect1> + <title>Inputs</title> + + <refsect2> + <title>none</title> + <para></para> + </refsect2> + </refsect1> + + <refsect1> + <title>Outputs</title> + <para>Returns text array of all active named dblink connections</para> + </refsect1> + + <refsect1> + <title>Example</title> + <programlisting> + SELECT dblink_get_connections(); + </programlisting> + </refsect1> + </refentry> + + <!-- dblinkd_is_busy --> + <refentry> + <refnamediv> + <refname>dblink_is_busy</refname> + <refpurpose>checks to see if named connection is busy with an async query</refpurpose> + </refnamediv> + + <refsynopsisdiv> + <synopsis> + dblink_is_busy(text connname) RETURNS int + </synopsis> + </refsynopsisdiv> + + <refsect1> + <title>Inputs</title> + + <refsect2> + <title>connname</title> + <para> + The specific connection name to use + </para> + </refsect2> + </refsect1> + + <refsect1> + <title>Outputs</title> + <para> + 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. + </para> + </refsect1> + + <refsect1> + <title>Example</title> + <programlisting> + SELECT dblink_is_busy('dtest1'); + </programlisting> + </refsect1> + </refentry> + + <!-- dblink_cancel_query --> + <refentry> + <refnamediv> + <refname>dblink_cancel_query</refname> + <refpurpose>cancels any active query on the named connection</refpurpose> + </refnamediv> + + <refsynopsisdiv> + <synopsis> + dblink_cancel_query(text connname) RETURNS text + </synopsis> + </refsynopsisdiv> + + <refsect1> + <title>Inputs</title> + + <refsect2> + <title>connname</title> + <para> + The specific connection name to use. + </para> + </refsect2> + </refsect1> + + <refsect1> + <title>Outputs</title> + <para> + Returns "OK" on success, or an error message on failure. + </para> + </refsect1> + + <refsect1> + <title>Example</title> + <programlisting> + SELECT dblink_cancel_query('dtest1'); + </programlisting> + </refsect1> + </refentry> + + <!-- dblink_error_message --> + <refentry> + <refnamediv> + <refname>dblink_error_message</refname> + <refpurpose>gets last error message on the named connection</refpurpose> + </refnamediv> + + <refsynopsisdiv> + <synopsis> + dblink_error_message(text connname) RETURNS text + </synopsis> + </refsynopsisdiv> + + <refsect1> + <title>Inputs</title> + + <refsect2> + <title>connname</title> + <para> + The specific connection name to use. + </para> + </refsect2> + </refsect1> + + <refsect1> + <title>Outputs</title> + <para> + Returns last error message. + </para> + </refsect1> + + <refsect1> + <title>Example</title> + <programlisting> + SELECT dblink_error_message('dtest1'); + </programlisting> + </refsect1> + </refentry> + + <!-- dblink --> + <refentry> + <refnamediv> + <refname>dblink</refname> + <refpurpose>returns a set from a remote database</refpurpose> + </refnamediv> + + <refsynopsisdiv> + <synopsis> + 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]) + </synopsis> + </refsynopsisdiv> + + <refsect1> + <title>Inputs</title> + + <refsect2> + <title>connname/connstr</title> + <para> + 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. + </para> + </refsect2> + + <refsect2> + <title>sql</title> + <para> + sql statement that you wish to execute on the remote host + e.g. "select * from pg_class" + </para> + </refsect2> + <refsect2> + <title>fail_on_error</title> + <para> + 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. + </para> + </refsect2> + <refsect2> + <title></title> + <para> + </para> + </refsect2> + </refsect1> + + <refsect1> + <title>Outputs</title> + <para>Returns setof record</para> + </refsect1> + + <refsect1> + <title>Example</title> + <programlisting> + 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) + </programlisting> + <para> + A more convenient way to use dblink may be to create a view: + </para> + <programlisting> + create view myremote_pg_proc as + select * + from dblink('dbname=postgres','select proname, prosrc from pg_proc') + as t1(proname name, prosrc text); + </programlisting> + <para> + Then you can simply write: + </para> + <programlisting> + select * from myremote_pg_proc where proname like 'bytea%'; + </programlisting> + </refsect1> + </refentry> + + <!-- dblink_send_query --> + <refentry> + <refnamediv> + <refname>dblink_send_query</refname> + <refpurpose>sends an async query to a remote database</refpurpose> + </refnamediv> + + <refsynopsisdiv> + <synopsis> + dblink_send_query(text connname, text sql) + </synopsis> + </refsynopsisdiv> + + <refsect1> + <title>Inputs</title> + + <refsect2> + <title>connname</title> + <para> + The specific connection name to use. + </para> + </refsect2> + <refsect2> + <title>sql</title> + <para> + sql statement that you wish to execute on the remote host + e.g. "select * from pg_class" + </para> + </refsect2> + </refsect1> + + <refsect1> + <title>Outputs</title> + <para> + 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). + </para> + </refsect1> + + <refsect1> + <title>Example</title> + <para> + <literal> + SELECT dblink_connect('dtest1', 'dbname=contrib_regression'); + SELECT * FROM + dblink_send_query('dtest1', 'SELECT * FROM foo WHERE f1 < 3') AS t1; + </literal> + </para> + </refsect1> + </refentry> + + <!-- dblink_get_result --> + <refentry> + <refnamediv> + <refname>dblink_get_result</refname> + <refpurpose>gets an async query result</refpurpose> + </refnamediv> + + <refsynopsisdiv> + <synopsis> + dblink_get_result(text connname [, bool fail_on_error]) + </synopsis> + </refsynopsisdiv> + + <refsect1> + <title>Inputs</title> + + <refsect2> + <title>connname</title> + <para> + The specific connection name to use. An asynchronous query must + have already been sent using dblink_send_query() + </para> + </refsect2> + <refsect2> + <title>fail_on_error</title> + <para> + 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. + </para> + </refsect2> + </refsect1> + + <refsect1> + <title>Outputs</title> + <para>Returns setof record</para> + </refsect1> + + <refsect1> + <title>Notes</title> + <para> + 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. + </para> + </refsect1> + + <refsect1> + <title>Example</title> + <programlisting> + 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) + </programlisting> + </refsect1> + </refentry> +</sect1> |