add schema to path in postgresql
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 开发者_Go百科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?
Use the set_config()
function like so:
SELECT set_config(
'search_path',
current_setting('search_path') || ',zzz',
false
) WHERE current_setting('search_path') !~ '(^|,)zzz(,|$)';
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.
精彩评论