Matthew Seaborn wrote:
> Given the situation where a user connecting to the database
> needs access to two separate schemas: the primary schema
> which contains the data they will be updating and a second
> schema which contains read-only reference data, used by many
> users, that will be using in joins on queries.
>
> I don't want to have to use fully qualified names (I am
> migrating code from Oracle which uses synonyms), so what is
> the best way (in both performance and reliability) to refer
> to the tables; Search Path or Synonyms?
There are no synonyms in PostgreSQL: synonyms are Oracle's way
of search_path (though more selective).
You excluded the best solution, namely to qualify the objects.
If your user should be able to access *all* tables in both
schemas unqualified and there are no name collisions between
objects in the schemas, I would recommend search_path.
Otherwise, use views.
Yours,
Laurenz Albe