Postgres: modify existing search_path (preserve current values)

SELECT set_config('search_path', 'fred,'||current_setting('search_path'), false);

The false says it's not a transaction-LOCAL setting.

For the bonus question, you can store the value in a custom setting:

SELECT set_config('tmp.search_path', current_setting('search_path'), false);

From version 9.2 on, you don't even have to define this setting in postgresql.conf.

Another way to do this is to originally set the search_path in a two-step procedure:

\set my_path schema_b, schema_c, public
set search_path to :my_path;

Then, whenever you want to extend search_path, do it like so:

\set my_path schema_a, :my_path
set search_path to :my_path;

This does not allow for storing the existing value of search_path however.

A nice way to do this is using the 9.3-specific \gset psql command:

SELECT current_setting('search_path') AS my_path \gset
set search_path to schema_a, :my_path;

\gset is documented here and is demonstrated nicely on depesz.com.

I was not able to test this as I don't have access to an instance of 9.3. I'd be grateful to anyone who could confirm to me that this works as I have outlined. Thanks!