>
> Martijn van Oosterhout <kleptog@svana.org> writes:
> > IIRC, the idea of a schema came from Oracle where the schema is the
> > username. If you create a schema with the same name as the user what
> > you describe above works. ie mydb.james.myview is in the james
schema
> > in the mydb database.
>
> Actually that isn't an Oracle-ism, it's a reasonable interpretation of
> the minimum requirements of the SQL standard: if you associate each
user
> with a schema named after and owned by that user, you get the minimum
> spec behavior. It sounds like MSSQL is doing about the same thing.
>
MSSQL has the dbname and username matrix of namespaces, pg only has the
one.
The problem I am trying to solve is where an application wants to make
use of both the dbname and username combination of namespaces, but it
looks like it will need to be emulated with the schema.
The dbname under MSSQL is a bit different though, it is actually a
completely separate database which may or may not interfere with
transaction framing. I think that the only time it would is if you have
to do a database restore. You then have to restore both to exactly the
same point to ensure data consistency.
Often though, another database is accessed purely to allow foreign
lookups of remote application data, it isn't so often that an
application would need to update data in two databases (although I have
such an application under mssql :)
> Our default search_path setting is set up to support this usage, btw.
> See "Usage Patterns" in the schema documentation.
> http://www.postgresql.org/docs/8.1/static/ddl-schemas.html
>
> regards, tom lane
Thanks
James
