Re: Search Path vs Synonyms - Mailing list pgsql-general

Ah, I am actually using EnterpriseDB (a commercial 'fork' of PSQL) which does have synonyms.

Is it possible set define the default search_path for a given user?

-----Original Message-----
From: Albe Laurenz [mailto:laurenz.albe@wien.gv.at]
Sent: 24 July 2009 07:22
To: Matthew Seaborn; pgsql-general@postgresql.org
Subject: RE: [GENERAL] Search Path vs Synonyms

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

________________________________________________________________________

CONFIDENTIALITY - This email and any files transmitted with it, are confidential, may be legally privileged and are
intendedsolely for the use of the individual or entity to whom they are addressed. If this has come to you in error,
youmust not copy, distribute, disclose or use any of the information it contains. Please notify the sender immediately
anddelete them from your system. 

SECURITY - Please be aware that communication by email, by its very nature, is not 100% secure and by communicating
withPerform Group by email you consent to us monitoring and reading any such correspondence. 

VIRUSES - Although this email message has been scanned for the presence of computer viruses, the sender accepts no
liabilityfor any damage sustained as a result of a computer virus and it is the recipient�s responsibility to ensure
thatemail is virus free. 

AUTHORITY - Any views or opinions expressed in this email are solely those of the sender and do not necessarily
representthose of Perform Group. 

COPYRIGHT - Copyright of this email and any attachments belongs to Perform Group, Companies House Registration number
6324278.