Hiroshi Inoue [mailto:Inoue@seiren.co.jp] wrote:
> > > > > In my mind the point of the "database" concept is to
> > > provide a domain
> > > > > within which custom datatypes and functions are available.
> > > >
> > >
> > > AFAIK few users understand it and many users have wondered
> > > why we couldn't issue cross "database" queries.
> >
> > Imho the same issue is access to tables on another machine.
> > If we "fix" that, access to another db on the same instance is just
> > a variant of the above.
> >
>
> What is a difference between SCHAMA and your "database" ?
> I myself am confused about them.
Think of it as a hierarchy:instance -> database -> schema -> object
- "instance" corresponds to one postmaster
- "database" as in current implementation
- "schema" name corresponds to the owner of the object,
only that a corresponding db or os user does not need to exist in
some of the implementations I know.
- "object" is one of table, index, function ...
The database is what you connect to in your connect statement,
you then see all schemas inside this database only. Access to another
database would need an explicitly created synonym or different syntax.
The default "schema" name is usually the logged in user name
(although I don't like this approach, I like Informix's approach where
the schema need not be specified if tabname is unique (and tabname
is unique per db unless you specify database mode ansi)).
All other schemas have to be explicitly named ("schemaname".tabname).
Oracle has exactly this layout, only you are restricted to one database
per instance.
(They even have a "create database .." statement, although it is somehow
analogous to our initdb).
Andreas
