Re: search_path vs extensions

On May 27, 2009, at 2:14 PM, Tom Lane wrote:

> Andrew Dunstan <andrew@dunslane.net> writes:
>> Another way of handling this might be to provide for prepending or
>> appending to the search path (or even for removing items from it).
>
> I was just about to raise that as a requirement.

Yeah, I likes.

> Some folks on this
> list might recognize the following coding pattern:
>
>     create schema rhn_channel;
>
>     --make rhn_channel be the default creation schema
>     update pg_settings set setting = 'rhn_channel,' || setting where  
> name = 'search_path';
>
>     ... create a bunch of objects in schema rhn_channel ...
>
>     -- restore the original setting
>     update pg_settings set setting = overlay( setting placing '' from 1  
> for (length('rhn_channel')+1) ) where name = 'search_path';
>
> I agree that a nicer way to do that would be good.

Oh, yes please.

>>    alter database foo set search_path = '+bar, baz'; -- append
>>    alter database foo set search_path = 'bar, baz+'; -- prepend
>
> ... but that ain't it :-(.  SET should mean SET, not "do something  
> magic".
> Particularly in ALTER DATABASE/ALTER USER, whose execution order
> relative to other stuff isn't especially well defined.

Perhaps a MODIFY keyword?

Best,

David