Postgres: modify existing search_path (preserve current values)

Question

Sometimes I would like to extend the existing search path, rather than replace it, say:

To start with say it is already set like so:

SET search_path TO schema_b, schema_c, public;

I want to add my schema to the front of the line:

SET search_path TO schema_a + search_path; --doesn't work

I'm thinking in analogy to what I'd do in BASH:

PATH=path_a:$PATH

Bonus question, perhaps related: is there a way I can store the current path temporarily, so I can change it to something totally different, and then restore it without having to know what it was?

Answer 1

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.

• vrms: this seems to be only temporary, is there a way to do this constantly?
• vrms: Maybe some way to use SELECT set_cont('search_path'} as a subselect in ALTER USER name SET search_path ...
• Craig Ringer: @vrms ALTER USER ... SET , ALTER DATABASE .. SET or ALTER SYSTEM .. SET

Answer 2

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.

Answer 3

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!

Answer 4

Adapted from the answer from Craig Ringer: if you want to make a check for distinct values, use the following query:

SELECT set_config('search_path', '"$user", mview,'||ARRAY_TO_STRING(
 ARRAY(
 SELECT DISTINCT elem FROM
 UNNEST(
 STRING_TO_ARRAY(
 REPLACE(
 current_setting('search_path'), ' ', '')
 , ',')) elem
 WHERE elem <> '"$user"')
 ,','), false);

It will make sure:

• "$user" is always at the beginning of the search_path
• no duplicate entries in search path
• whitespace is removed on count-distinct check
• replace mview with your new search path entry of choice