Re: Multiple database queries

On Sun, Oct 02, 2005 at 11:20:38PM -0400, Marc Andre Paquin wrote:
> Le 2005 10 02 14:36, David Fetter a ecrit:
> > On Sat, Oct 01, 2005 at 09:37:49PM -0700, Cosmopo wrote:
> > > Hello,
> > >
> > > I was using many years ago Sybase that was able then to query
> > > several tables that could be located in more than one
> > > database... I know that Postgresql did not at the time have this
> > > capability.
> > >
> > > Does the new version 8.0.x ofter this feature? We need to create
> > > several databases based on our customer's specs... but we need
> > > our own customer, permission, history, etc database. We want to
> > > be able to query 2 database that would have share customer's ID
> > > and other info.
> >
> > I don't know exactly what you mean by tables that can be located
> > in more than one database, as "database" has several definitions,
> > but PostgreSQL has "schemas" which are essentially namespaces
> > inside the same database, and you can query across schemas without
> > extra add-ons.
>
> Hello, thank you...
>
> Well, when I do a "createdb owndb1" and "createdb customerdb2" I
> want to put a table "customer" in the owndb1 with user name, account
> specification that will be valuable for all our customers...

In PostgreSQL, you'd use schemas for this kind of separation. Each
can have its own owner, it's own constellation of permissions, and any
or none of it can overlap with those of another schema.

http://www.postgresql.org/docs/current/static/sql-createschema.html
http://www.postgresql.org/docs/current/static/ddl-schemas.html

Also handy, and from the SQL:2003 standard:

http://www.postgresql.org/docs/current/static/information-schema.html

> I would like to make a query like this:
>
> select
> owndb1.customer.co_name,owndb1.customer.passwd,customerdb2.event.title,...
> from owndb1.customer,customerdb2.event where customerdb2.info.user_id =
> owndb1.customer.user_id;
>
> In Sybase I used to be able to query accross 2 databases since we
> usualy made a database per customer but account, permissions info,
> etc for those customers where in a central database.
>
> From your response, I guess this is not possible even many years
> later.

Not just possible, but easy :)

Cheers,
D
--
David Fetter david(at)fetter(dot)org http://fetter.org/
phone: +1 510 893 6100 mobile: +1 415 235 3778

Remember to vote!