diff options
Diffstat (limited to 'contrib/dblink/doc/query')
| -rw-r--r-- | contrib/dblink/doc/query | 242 |
1 files changed, 0 insertions, 242 deletions
diff --git a/contrib/dblink/doc/query b/contrib/dblink/doc/query deleted file mode 100644 index 42427b5d5c3..00000000000 --- a/contrib/dblink/doc/query +++ /dev/null @@ -1,242 +0,0 @@ -================================================================== -Name - -dblink -- Returns a set from a remote database - -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]) - -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 usage - -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%'; - - -================================================================== -Name - -dblink_send_query -- Sends an async query to a remote database - -Synopsis - -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 usage - - SELECT dblink_connect('dtest1', 'dbname=contrib_regression'); - SELECT * from - dblink_send_query('dtest1', 'select * from foo where f1 < 3') as t1; - -================================================================== -Name - -dblink_get_result -- Gets an async query result - -Synopsis - -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 usage - -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) |