> > Martijn van Oosterhout <kleptog@xxxxxxxxx> 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
