Обсуждение: Feature request: Per database search_path

As far as I know, currently one can set the search path globally, or on a
per role bases.

I was wondering if it could be possible to have a per database search_path.
I believe this would be not only convenient, but will add flexibility.

What got me thinking of this was that I manually ste a search_path.
Connected to another database and when I came back to the original database
the search_path was gone.

At first I thought perhaps search_path could be set by database and that
changing database was blanking my change. After looking at the help for
alter database I noticed that there is no way to set the search_path by
database.

Francisco Reyes wrote:
> As far as I know, currently one can set the search path globally, or on
> a per role bases.
>
> I was wondering if it could be possible to have a per database search_path.
> I believe this would be not only convenient, but will add flexibility.


ALTER DATABASE leia SET search_path = public,lookups;

Seems to work for me on 8.2 - you'll need to disconnect and reconnect to
see it take place though.

--
   Richard Huxton
   Archonet Ltd

Francisco Reyes <lists@stringsutils.com> writes:
> As far as I know, currently one can set the search path globally, or on a
> per role bases.
> I was wondering if it could be possible to have a per database search_path.

ALTER DATABASE SET search_path = ...

            regards, tom lane

Richard Huxton writes:

> ALTER DATABASE leia SET search_path = public,lookups;
> Seems to work for me on 8.2 - you'll need to disconnect and reconnect to
> see it take place though.

Hmm.. I must have typed something wrong when I tried..

For the archives..
If a user has a search path, that takes precedence over the database
search_path


Is there a way to unset a role's search_path?
I had set one role with a particular search path. Now want to take that off
so the user can get the database's search_path setting.

Francisco Reyes wrote:
>
> Is there a way to unset a role's search_path?
> I had set one role with a particular search path. Now want to take that
> off so the user can get the database's search_path setting.

ALTER ROLE ... RESET search_path;

--
   Richard Huxton
   Archonet Ltd

On Wed, Jul 18, 2007 at 11:02:51PM +0100, Richard Huxton wrote:
> Francisco Reyes wrote:
> >As far as I know, currently one can set the search path globally, or on
> >a per role bases.
> >
> >I was wondering if it could be possible to have a per database search_path.
> >I believe this would be not only convenient, but will add flexibility.
>
>
> ALTER DATABASE leia SET search_path = public,lookups;
>
> Seems to work for me on 8.2 - you'll need to disconnect and reconnect to
> see it take place though.

BTW, in case it's not obvious, that works with pretty much anything that
session-setable. Same with ALTER ROLE ... SET.
--
Jim Nasby                                      decibel@decibel.org
EnterpriseDB      http://enterprisedb.com      512.569.9461 (cell)