On Thu, Feb 09, 2006 at 09:37:35AM +1100, James Harper wrote:
> Under Microsoft SQL, a fully qualified table name is
> db.owner.tablename (well actually, linked_server.db.owner.table, but we
> won't go there :)
>
> Under Postgres, you can create schemas to simulate different databases
> within the one actual database, but as far as I can tell, the owner does
> not contribute to the namespace at all. I can't have two views with the
> same name in the same database but with different owners (eg
> mydb.james.myview and mydb.fred.myview). Can someone tell me if this is
> correct?
>
> Assuming I'm correct, how well might it work if I created schema's in
> the form db_owner, eg mydb_james and mydb_fred, to emulate this? There
> would also be the schema of the dbo which mydb_dbo, which is also
> searched given an unqualified table name. If I'm logged in as james, my
> search path would be mydb_james then mydb_dbo.
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.
BTW, here's one little known feature of PostgreSQL:
$ psql template1
Welcome to psql 7.4.7, the PostgreSQL interactive terminal.
template1=# select count(*) from template1.pg_catalog.pg_proc;
count
-------
1655
(1 row)
You can specify the database name in the table reference. You can only
reference your own database ofcourse, but the syntax is accepted
without complaint.
Have a nice day,
--
Martijn van Oosterhout <kleptog@svana.org> http://svana.org/kleptog/
> Patent. n. Genius is 5% inspiration and 95% perspiration. A patent is a
> tool for doing 5% of the work and then sitting around waiting for someone
> else to do the other 95% so you can sue them.