So the problem can be described as follows :
1. Let be Ent01 an enterprise, and Ent02 a different one.
2. At Ent01 the database schema has the name "SCH01" and at Ent02 the database schema has the name "FOO" .
3. The same application must run in booth enterprises, and all the database queries and table names are the same, just the schemas has different names.
4. The application can run using a database user other then the tables owner, so the queries must be written using the coplete format (schema.table.column) .
Using Oracle I can set up synonyms for the tables and by pass the format above, or I can use a macro substitution (ODAC components) to use the correct schema name, setting it at runtime.
As you told me PostgreSQL does not has table synonyms, I would like to write a query like "SELECT alias.column FROM &schema.table AS alias", and set up the &schema value at runtime. This way the query could be ran in every schema which has the table.
That is the problem how to do this in PostgreSQL ? I am planing to use Zeos database components.
By the way what "FWIW" stands for ?
Thank you very much.
----- Original Message -----
From: "Jim C. Nasby"
To: jjeffman@xxxxxxxxx
Sent: 21-May-2005 15:15:49 -0300
Subject: Re: table synonyms
I suspect you're mixing users and schemas, but it's been too long since
I've used Oracle, so I'm not sure. Can you provide a more concrete
example? FWIW, I suspect this is a non-issue with postgresql, since the
only hierarchy of objects is schemas, and you can handle that with
search_path.
