Unanswered: Usage of schemas with multiple database
Looking at the postgres' documentation I noticed the concept of "schemas". They seem to be a way of structuring one single database in multiple directory all at the same level.
I think that "schemas" denote a clear best practice: each application is tied to one specific database and in general, the mapping between databases and application is "1 to 1".
However, my application needs (for legacy reasons) to be mapped to N database in the same cluster and sometimes I need to retrieve data from more than one database at a time.
So I was wondering if it is possible to define a schema as global to all of the database in the cluster, in a way that, when I'm connected to a database, I'm able to see tables from that global schema, even if this schema is defined for a database different from the one I'm connected to.
Schemas are a method to allow multiple sets of database objects (tables, views, etc.) to be logically distinct from one another, while physically (potentially) using the same underlying files for storage. Of course, you can also use separate tablespaces to allow physically separate underlying data files as well. You might follow the approach of each application using a separate schema. In this approach, you can access the other data using three-part naming (schemaname.tablename.fieldname)
Each database maintains a separate set of catalog data in the Information schema, a set of views which can be used with SQL Server (2005 and greater) MySQL and PostgreSQL. pg-specific metadata is not available in the information schema - you must use system tables to view these metadata.
If you create separate databases, however, you must use the dblink function to access the other databases. You could conceivably use the dblink function to access the information schema (or system tables) in other databases to retrieve their metadata, and once the metadata is retrieved, you could retrieve the data. (dblink requires a field list; the '*' convention representing fields in a table is not supported.)
The advantage of placing separate applications within the same database in separate schemas is that, since these schemas share a common catalog, it is much easier to access the metadata and data, using three-part naming. (SchemaName.TableName.FieldName)
Last edited by loquin; 07-02-09 at 17:20.
"Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
"I have my standards. They may be low, but I have them!" - Bette Middler
"It's a book about a Spanish guy named Manual. You should read it." - Dilbert