Thread: Cross Database Shared Tables?

I know this has probably been asked before but the archive searching
seems to just hang for me..

I'm in a situation trying to teach an old application new tricks. I
have a table in one database that I need to be shared across several
other databases ( to do joins against it etc). Is it possible to do
this now with schemas, or am I out of luck?

Also, can a function/trigger access another database to perform queries
there instead of in the database where the trigger/function is created?

Thanks very much!

-Mitch

Mitch Vincent wrote:
> I'm in a situation trying to teach an old application new tricks. I have
> a table in one database that I need to be shared across several other
> databases ( to do joins against it etc). Is it possible to do this now
> with schemas, or am I out of luck?

If you can move all of your databases into their own schema within one
database, then yes, schemas can be a solution. It may require you to
modify your applications to be schema-aware and will require that your
hardware can handle them all on one server.

An alternative option is contrib/dblink. That will allow queries
executed remotely and joined with local tables. In PostgreSQL 7.2 it is
pretty crude though -- 7.3 is much better if you [can use | are using]
that. Just keep in mind that whatever data you ask for is dragged
through libpq, so push down as many qualifiers to the remote table as
possible (you have to do this manually).

> Also, can a function/trigger access another database to perform queries
> there instead of in the database where the trigger/function is created?

Same as the above -- across schemas in one database is no problem as
long as your trigger function is schema-aware; across databases use dblink.

HTH,

Joe