Database Reference
In-Depth Information
on the unnamed remote connection as follows:
SELECT *
FROM dblink('SELECT generate_series(1,3)')
AS link(col1 integer);
col1
------
1
2
3
(3 rows)
Note that we need to specify the output columns and column types. If
dblink()
is unable to
determine the result specification of the query, it will execute when we parse the query, so we
must explicitly define the output that we expect when the query executes.
How it works...
dblink establishes a persistent connection with the other database. The dblink functions
track the details of that connection, so you don't need to worry about doing so yourself. You
should be aware that this is an external "resource", and so the generic programming problem
of "resource leaks" becomes possible; if you forget about your connection and forget to
disconnect it, you may experience problems later. The remote connections will be terminated
should your session disconnect.
Note that the remote connection persists even across transaction failures and other errors, so
there is no need to reconnect.
dblink()
executes the remote query, and will assemble the result set in memory before the
local reply begins to be sent. That means that very large queries might fail through lack of
memory, and everybody else will notice also. This isn't a problem; this is simply not designed
to handle bulk data flows. Look at the recipe about data loading instead, if that's what you
want to do.
Running slightly larger queries can be achieved using cursors. These allow us to bring the
answer set back in smaller chunks. Conceptually, we need to open the cursor, loop while
fetching rows until we are done, and then close the cursor. Some example code for that is
as follows:
postgres=# SELECT dblink_open('example',
'SELECT generate_series(1,3)', true);
dblink_open
-------------
OK
(1 row)
