Hi Rob, > On 22. Jan, 2021, at 23:48, Rob Sargent <robjsarg...@gmail.com> wrote: > > Yes, I'm confused. As I said in reply to Jeff, I would rather not need to > remember to set the search_path, which I can avoid if I login as "role".
I didn't follow the whole discussion, so sorry, to just jump in here. You don't need to remember the search_path once your role is set up correctly. The following example demonstrates that you can set a default search_path for a role. But keep in mind that it is a *default* search_path for that role, which means a) it's for that role only and b) since it's a default, it is in effect only after the next login to that role. db01=# \conninfo You are connected to database "db01" as user "paul" on host ... db01=# show search_path; search_path ----------------- "$user", public (1 row) db01=# \dn List of schemas Name | Owner --------+---------- public | postgres (1 row) db01=# create schema s1; CREATE SCHEMA db01=# create schema s2; CREATE SCHEMA db01=# create schema s3; CREATE SCHEMA db01=# \dn List of schemas Name | Owner --------+---------- public | postgres s1 | paul s2 | paul s3 | paul (4 rows) Now comes the crucial part. Notice that the search path still shows the old value until I reconnect: db01=# alter role paul set search_path to s2, s3; ALTER ROLE db01=# show search_path; search_path ----------------- "$user", public (1 row) db01=# \c db01 psql (13.1, server 12.5) You are now connected to database "db01" as user "paul". db01=# show search_path; search_path ------------- s2, s3 (1 row) See? No $user, public or s1 after connecting to the database, only s2 and s3 as specified by me. The same applies to resetting it to its default values "$user", public: db01=# alter role paul reset search_path; ALTER ROLE db01=# show search_path; search_path ------------- s2, s3 (1 row) db01=# \c db01 psql (13.1, server 12.5) You are now connected to database "db01" as user "paul". db01=# show search_path; search_path ----------------- "$user", public (1 row) There's a lot of descriptions on how this works. Just google for something like "postgres set default search path". Also, see chapter 5.9.3 of the docs: The Schema Search Path https://www.postgresql.org/docs/current/ddl-schemas.html#DDL-SCHEMAS-PATH Hope this helps. Cheers, Paul
