Queries across multiple databases  (was: SELECTfrom a table in another database).

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

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

I'm stuck in the same cleft in the tree - database application originally
written for Oracle and Sybase, that still needs to work in Oracle, and the
SQL and database structure etched in stone.  The problem isn't about a client
with multiple connections, its about executing the following query:

SELECT A.*, B.* FROM FOO.USERS A, BAR.FAVORITE_BEERS B WHERE A.USER =
B.GUZZLER

Putting 200+ tables in a database certainly isn't a big deal, as I think Tom
Lane points out in another post in this thread. I am poking at the parser in
my copious free time just to see how easy it would be to just strip a schema
name off the items in the FROM clause before anything happens, but one
doesn't pick up the internals of the parser in 10-15 minutes a day...hints
anyone? Anyway, this way I COULD put all the tables in one database, keep the
schema-based queries, and no one would ever know.

I would twitch on the floor in utter extasy if I could hose Oracle...while
their licensing is more flexible than in the past, it still doesn't sit
right, and despite all their claims to the contrary their java support is a
joke.  And maybe their pinheaded sales reps WOULD STOP CALLING ME EVERY WEEK.

If I ever come up with said schema-dropping patch, and anyone else wants it,
let me know.

--
Regards,

Andrew Rawnsley
Ravensfield Geographic Resources, Ltd.
(740) 587-0114
www.ravensfield.com