Thread: Reading across databases

Postgresql 7.4.5 (pgaccess and psql) RedHat 8

Is there a way of 'cross accessing' databases?
We tend to use three environments for development - a dev_ , test_ and rel_ prefixed database then exists - one for each environment. There are occasions when it is very useful to be able to, say, load the test_ database with some data that has already been created in the dev_ database. I was hoping that a command such as:

INSERT INTO test_agents:someTable
    SELECT *
         FROM dev_agents:someTable

OR simply
SELECT blah FROM databaseName:tableName.....

Is such a feature available?

Regards, Steve Tucknott ReTSol Ltd DDI: 01903 828769

am  17.12.2004, um  7:48:12 +0000 mailte Steve Tucknott folgendes:
> Postgresql 7.4.5 (pgaccess and psql) RedHat 8
>
> 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.


> 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.


Regards, Andreas
--
Andreas Kretschmer    (Kontakt: siehe Header)
               Tel. NL Heynitz:  035242/47212
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===    Schollglas Unternehmensgruppe    ===

On Fri, Dec 17, 2004 at 07:48:12AM +0000, Steve Tucknott wrote:

> Is there a way of 'cross accessing' databases?

See contrib/dblink.

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

Thanks Andreas, I'll take a look at using Schemas.

On Fri, 2004-12-17 at 07:49, Andreas Kretschmer wrote:

am  17.12.2004, um  7:48:12 +0000 mailte Steve Tucknott folgendes:
> Postgresql 7.4.5 (pgaccess and psql) RedHat 8
> 
> 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.


> 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.


Regards, Andreas
-- 
Andreas Kretschmer    (Kontakt: siehe Header)
               Tel. NL Heynitz:  035242/47212
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===    Schollglas Unternehmensgruppe    === 

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

Regards, Steve Tucknott ReTSol Ltd DDI: 01903 828769

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/

am  17.12.2004, um  2:07:53 -0700 mailte Michael Fuhr folgendes:
> > 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:

Thank you. I read your other answer and so i read the Doku for dblink.


> > 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.

Yes, okay. I have a simular problem, i need the same table on 2
databases. Now I have the knowladge to solve this with a VIEW.


Regards, Andreas
--
Andreas Kretschmer    (Kontakt: siehe Header)
               Tel. NL Heynitz:  035242/47212
GnuPG-ID 0x3FFF606C http://wwwkeys.de.pgp.net
 ===    Schollglas Unternehmensgruppe    ===