Database Reference
In-Depth Information
postgres=# SELECT *
FROM dblink_fetch('example', 10, true)
AS link (col1 integer);
col1
------
1
2
3
(3 rows)
Notice that we didn't need to define the cursor when we opened it, though we do need to
define the results from the cursor when we fetch from it, just as we did with a normal query.
Fetch 10 rows at a time.
postgres=# SELECT *
FROM dblink_fetch('example', 10, true)
AS link (col1 integer);
col1
------
(0 rows)
postgres=# SELECT dblink_close('example');
dblink_close
--------------
OK
(1 row)
dblink also allows you to use more than one connection. Using just one connection is
generally not good for modular programming. For more complex situations, it's a good
practice to assume that the connection you want is not the same one that another part of
the program might need. dblink allows named connections, so you don't need to hope that
the default connection is still the right one. There is also a function named
dblink_get_
connections()
that will allow you to see what connections you have active.
There's more...
Remote data sources look like they can be treated as tables, though in practice this doesn't
work in all the ways you might hope and expect.
There is no federated query optimizer. If we join a local and a remote table, then data from the
remote database is simply pulled through, even if it would have been quicker to send through
data, and then pull back matching rows.
The local
WHERE
clause is not sent to the remote database, so a query like the following would
perform poorly:
