Re: [despammed] Reading across databases

On Fri, Dec 17, 2004 at 08:49:02AM +0100, Andreas Kretschmer wrote:
> am  17.12.2004, um  7:48:12 +0000 mailte Steve Tucknott folgendes:
> >
> > Is there a way of 'cross accessing' databases?
>
> IMHO no. Not in a single psql-Session. But you can do this in a
> client-application. There can you open any connections to several
> databases.

With the contrib/dblink module you can query other databases, even
databases on other servers.  You can hide the complexity by creating
a view:

CREATE VIEW othertable AS
SELECT *
FROM dblink(
  'host=otherhost dbname=otherdb user=johndoe password=abc123xyz',
  'SELECT * FROM othertable'
) AS othertable(id INTEGER, name TEXT);

SELECT * FROM othertable;

> > We tend to use three environments for development - a dev_ , test_ and
> > rel_ prefixed database then exists - one for each environment. There are
>
> You should use for this reason different schemas in one database. Then
> you can cross-access between the different schemas.

Some environments might prefer to have development and production
in different databases or on different servers so problems on one
don't affect the other.  In such cases, dblink can be a handy way
to perform queries that need to access both databases.

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/