Re: Do we want SYNONYMS?

On 12/6/10 4:09 PM, Tom Lane wrote:
> Michael C Rosenstein<mcr@mdibl.org>  writes:
>>> What is "schema" in this context?
>
>> Oracle "schema" == Postgres "database":  a collection of objects
>> (tables, functions, triggers, views, etc) owned by a user.
>
> That seems like a pretty unlikely equivalence.  What I'm afraid
> you are really saying you want is cross-database synonyms (ie links
> to objects in remote databases).  Which I'm pretty sure is not what
> JD is offering to implement, though I think it is possible to do
> in Oracle.

Nope, not talking about remote database links, but merely links to
different databases in the same process on the same host.

For example webAppUser sometimes needs to access the
public1.get_customer_name() function, the public1.order table and the
edit.account table. After a new data load of the public2 database, the
webAppUser would need to access the public2.get_customer_name()
function, the public2.order table and the edit.account table. By
switching the webAppUser's 'get_customer_name()' and 'account' synonyms,
this toggling between accessing public1 and public2 objects is quick,
easy and seamless.  The webAppUser code need only contain:
select get_customer_name();
or
select * from order;
without needing to be conscious of whether it is selecting from public1
or public2.

Synonyms are a great feature in Oracle. The lack of synonyms in
PostgreSQL was one of our biggest hesitations in switching. As I said,
however, we found a hacky workaround by toggling the webAppUser's search
path.

/m