6

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?

8
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.

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.

2

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!

0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.