Add schema to path in postgresql

Question

I am the process of transferring applications from all in a public scheme, each of which has its own scheme. for each application, I have a small script that will create a schema and then create tables, functions, etc. to this circuit. Is there a way to automatically add a newly created schema to the search_path? Currently, the only way I can see is to find the current user path SHOW search_path; , and then add to it a new scheme SET search_path to xxx,yyy,zzz;

I would like to say something, add a zzz scheme to the users_search path. is it possible?

Answer 1

Use the set_config() function as follows:

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

Answer 2

Based on the theoretical answer, here is how you constantly add a scheme to another user's search path. Useful for configuring read-only users and separating the settings of different 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 supplement? Depends on your use case. IMO, it is useful to have schemas for each stored procedure. This means that if you modify the stored procedure, you can simply define it in a separate schema and overwrite the search_ path of the user of the user who uses it.