Thread: Cross-database tables

Hi,

I'd love it if I could access tables from other databases.

Is there a way to do this?

Here's some sample pseudo-SQL in case I wasn't clear about that:

    select * from database1^table3, database2^table4;

Which would do something similar to:

    psql -d database1 -c 'select * from table3;'
    psql -d database2 -c 'select * from table4;'

All I see when I've read docs, or checked other sources is that databases
are separate, and can't access other info.

I suppose that answers my question -- but it just doesn't seem right.
It'd seem like there oughtta be a way, somehow, to access (at least read!)
data from multiple tables in multiple databases -- at least on the same
site.

There are only two other ways I can think of to do this...

  1)    Consolidate all tables into a single database.  I'd have a lot of
    stuff in that database then.  Uck.

  2)    Write scripts that access both databases, and essentially duplicate
    some of the database functions in the script.

So far, I do small parts of method 2, and in some cases, simply copy info
from one database to another, duplicating it.

Any other ideas are welcome.

--
RJohnson.com -+- Ron Johnson <rjohnson@rjohnson.com>
http://www2.rjohnson.com/me/1yyiUmKVrL9Sn/

On Sat, 6 Jan 2001, Ron Johnson wrote:

> I'd love it if I could access tables from other databases.
>
> Is there a way to do this?
>
> Here's some sample pseudo-SQL in case I wasn't clear about that:
>
>     select * from database1^table3, database2^table4;

Is this the same as what in SQL92 are known as 'logical schemas' (versus
the physical schema)?  They aren't supported yet in PostgreSQL, but are in
the TODO list, according to http://www.postgresql.org/docs/todo.html,
under exotic features. hmm...  distributed queries (via replication) are
listed in there as well.  Cool.

-- Brett
                                     http://www.chapelperilous.net/~bmccoy/
---------------------------------------------------------------------------
I'd never join any club that would have the likes of me as a member.
        -- Groucho Marx

On Sat, 6 Jan 2001, Brett W. McCoy is thought to have said:

> Is this the same as what in SQL92 are known as 'logical schemas' (versus
> the physical schema)?  They aren't supported yet in PostgreSQL, but are in
> the TODO list, according to http://www.postgresql.org/docs/todo.html,

Thanks.  I didn't think to look in that list for some reason.  Same list
contains a few other things I've been looking for too, but didn't know
what to call them.

I did find it kinda funny that it's listed under "exotic", though.

Eh, it's a good, solid database.  I'll find a way around it till someone
good at writing databases sits down and thinks "Drats, I could really use
one of those exotic logical schemas to access multiple tables from
multiple databases right about now.  Why -- I'll write one!". ;)

Thanks.

--
RJohnson.com -+- Ron Johnson <rjohnson@rjohnson.com>
http://www2.rjohnson.com/me/2Ns9qoTqQqDyi/