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

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

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/

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

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/

---------------------------(end of broadcast)---------------------------
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to majordomo@postgresql.org so that your
message can get through to the mailing list cleanly

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

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend