Re: Queries across multiple databases (was: SELECT from a table in another database).

Trygve Falch wrote:
>
> In article <3B01ACE4(dot)2A47F2C6(at)mbit(dot)nl>, "Nils Zonneveld" <nils(at)mbit(dot)nl>
> wrote:
>
> >> Actually I think I found 'Allow queries across multiple databases' in
> >> the TODO-list under something they call 'Exotic feature'. I thought
> >> that this feature was relativly basic and standard-feature in most
> >> DB's.
>
> > I don't know what you are using those database for, but nothing prevents
> > you from letting your clients connect to the different databases the
> > same time.
>
> But that requires me to make a new database connection for each database I
> need to access.
>
> And putting 200+ tables in one single database is not an option.
>
> The application which needs to be able to do this is a
> cross-database-application (MSSQL, Oracle, Sybase) and I have almost no
> room for doing major changes to the SQL which this application uses.
>

If you have a cross-database-application you must already have multiple
connections to several database-engines at the same time. Or is that a
situation you want to get rid of?

> But the lack of this feature in Postgres makes it almost impossible to
> make a structured database design for huge application. I know this
> question have been asked before in another postgres forum as early as
> 1998, and what Bruce Momjian said then was that most commercial databases
> couldn't do it, which was probably right for 1998, but today even MySQL
> can do this! Sybase, Oracle and MSSQL can also do this. I think even DB2
> and Informix can.
>
> I was really suprised when I discovered that this was even an issue with
> Postgres, because everything else in this wonderful DBM is on an
> enterprise level of quality and functionality.
>
> Sadly, this means I'll have to stick to Oracle (even if I really didn't
> want to) until this issue is resolved in Postgres.
>

I'm not a PostgreSQL developer, just a humble user :-) If you have the
money and resources to use Oracle, use Oracle if you really need schema
support. If not, there are workarounds. At the moment PostgreSQL doesn't
have schema support. But there's light at the end of the tunnel: I've
heard Tom Lane mention schema support several times (can you give us an
estimate when schema support will be available in PostgreSQL Tom?).

Regards,

Nils Zonneveld