Re: Cross Database Shared Tables?

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