add schema to path in postgresql

Question

I'm the process of moving applications over from all in the public schema to each having their own schema. for each application, I have a small script that will create the schema and then create the tables,functions,etc... to that schema. Is there anyway to automatically add a newly created schema to the search_path? Currently, the only way I see is to find the users current path SHOW search_path; and then add the new schema to it SET search_path to xxx,yyy,zzz;

I would like some way to just say, append schema zzz to the users_search path. is this possible?

theory · Accepted Answer · 2015-12-07 17:10:13Z

17

Use the set_config() function like so:

SELECT set_config(
    'search_path',
    current_setting('search_path') || ',zzz',
    false
) WHERE current_setting('search_path') !~ '(^|,)zzz(,|$)';

edited Dec 7, 2015 at 17:10

answered Mar 29, 2010 at 6:25

theory

9,97912 gold badges68 silver badges134 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Randall Over a year ago

I believe this will only set the search_path for the current session, and not persist beyond that.

ngreen Over a year ago

I'm having trouble with that regex because my path has a space after the comma. I'm using '(^|,\s?)zzz(,|$)' instead.

Temuz · Accepted Answer · 2016-06-15 19:26:26Z

Building on top of theory's answer, here's how you permanent prepend a schema to another user's search_path. Useful for setting up read-only users and splitting setup of various schemas in different .sql files.

create or replace function prepend_search_path(
    role_name text, schema_name text
) returns void as $$
declare
    current_search_path text;
begin
    -- First, we get the current search_path for that user
    select  replace(sc.configval,'search_path=','')
    from    pg_db_role_setting rs
    left 
    join    pg_roles r
    on      r.oid = rs.setrole,
    lateral unnest(rs.setconfig) as sc(configval)
    where   sc.configval like 'search_path=%'
    and r.rolname = role_name
    into current_search_path;

    -- It is possible that a new user is not in pg_roles. To fix this,
    -- we find the default search_path values.
    if not found then
        select boot_val
        from pg_settings
        where name='search_path'
        into current_search_path;
    end if;

    -- Prepend the schema_name to search_path
    if current_search_path !~ ('(^|, )' || schema_name || '(,|$)') then
        current_search_path := schema_name || ', ' || current_search_path;
    end if;


    -- Make the changes
    execute format('alter role %I set search_path = %s', role_name, current_search_path);
end
$$ language plpgsql;

Why a prepend? Depends on your use case. IMO, it's useful to have schemas for each stored procedure. That means if you change a stored procedure, you can simply define it in a separate schema and overwrite the search_path of the user that uses it.

Collectives™ on Stack Overflow

add schema to path in postgresql

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related