path: root/doc/src/sgml/dblink.sgml

<sect1 id="dblink">
 <title>dblink</title>
 
 <indexterm zone="dblink">
  <primary>dblink</primary>
 </indexterm>

 <para>
  <literal>dblink</> is a module which allows connections with
  other databases.
 </para>

 <refentry id="CONTRIB-DBLINK-CONNECT">
  <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> 

 <refentry id="CONTRIB-DBLINK-DISCONNECT">
  <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> 

 <refentry id="CONTRIB-DBLINK-OPEN">
  <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>

 <refentry id="CONTRIB-DBLINK-FETCH">
  <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>

 <refentry id="CONTRIB-DBLINK-CLOSE">
  <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>

 <refentry id="CONTRIB-DBLINK-EXEC">
  <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>

 <refentry id="CONTRIB-DBLINK-CURRENT-QUERY">
  <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>

 <refentry id="CONTRIB-DBLINK-GET-PKEY">
  <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>

 <refentry id="CONTRIB-DBLINK-BUILD-SQL-INSERT">
  <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>

 <refentry id="CONTRIB-DBLINK-BUILD-SQL-DELETE">
  <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> 

 <refentry id="CONTRIB-DBLINK-BUILD-SQL-UPDATE">
  <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>

 <refentry id="CONTRIB-DBLINK-GET-CONNECTIONS">
  <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> 

 <refentry id="CONTRIB-DBLINK-IS-BUSY">
  <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>

 <refentry id="CONTRIB-DBLINK-CANCEL-QUERY">
  <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>

 <refentry id="CONTRIB-DBLINK-ERROR-MESSAGE">
  <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> 

 <refentry id="CONTRIB-DBLINK">
  <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>

 <refentry id="CONTRIB-DBLINK-SEND-QUERY">
  <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 &lt; 3') AS t1;
    </literal>
   </para>
  </refsect1>
 </refentry> 

 <refentry id="CONTRIB-DBLINK-GET-RESULT">
  <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 &lt; 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 &lt; 3; select * from foo where f1 &gt; 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>