Thread: search_path for database vs user vs session

On 05/27/2013 11:29 AM, Moshe Jacobson wrote:
> How can I show the value of search_path for the database, the user and
> the session?
> I ask because I cannot explain the following:
>
>     $ psql -U postgres -d ises
>     psql (9.1.4)
>     Type "help" for help.
>
>     postgres@moshe=>devmain:ises=# show search_path;
>          search_path
>     -------------------
>       public, audit_log
>     (1 row)
>
>     postgres@moshe=>devmain:ises=# alter database ises set search_path
>     to public, auditlog;
>     ALTER DATABASE
>
>     postgres@moshe=>devmain:ises=# \q
>     $ psql -U postgres -d ises
>     psql (9.1.4)
>     Type "help" for help.
>
>     postgres@moshe=>devmain:ises=# show search_path;
>          search_path
>     -------------------
>       public, audit_log
>     (1 row)

Is the below what you are looking for?

http://www.postgresql.org/docs/9.2/static/runtime-config-client.html

"The current effective value of the search path can be examined via the
SQL function current_schemas (see Section 9.25). This is not quite the
same as examining the value of search_path, since current_schemas shows
how the items appearing in search_path were resolved."

Section 9.25:

http://www.postgresql.org/docs/9.2/static/functions-info.html

>
>
> Thanks.
>


--
Adrian Klaver
adrian.klaver@gmail.com

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

On 05/27/2013 12:16 PM, Moshe Jacobson wrote:
> On Mon, May 27, 2013 at 3:07 PM, Moshe Jacobson
> <moshe@neadwerx.com <mailto:moshe@neadwerx.com>> wrote:
>
> I'd like to know how to see the search_path setting attached to a
> particular user/role independent of the session.
>
>
> Oh, and I'd also like to see the current setting of the database so
> I know what will happen if I clear the user setting.

create user foo;
create schema bar;
create schema foobar;
alter user foo set search_path to bar;
alter database contrib_regression set search_path to foobar;
select * from pg_db_role_setting ;
 setdatabase | setrole |      setconfig
- -------------+---------+----------------------
           0 |   16401 | {search_path=bar}
       16384 |       0 | {search_path=foobar}
(2 rows)

Is that what you are looking for?

HTH,

Joe

- --
Joe Conway
credativ LLC: http://www.credativ.us
Linux, PostgreSQL, and general Open Source
Training, Service, Consulting, & 24x7 Support
-----BEGIN PGP SIGNATURE-----
Version: GnuPG v1.4.11 (GNU/Linux)
Comment: Using GnuPG with Thunderbird - http://www.enigmail.net/

iQIcBAEBAgAGBQJRo7j4AAoJEDfy90M199hlqJAP/1jkrRcNfhmWib/+8CH2vYbn
8ZyxSL5QTrAxhyVokmNOFXssnNwxSWnq2GAxtO2Jren/PZnekCuY/BKD4jAUtPPp
IssS8Tv2rQYfoXlDfj1ANHH4YUe4HNhK7X+mTBupzCUUHbZdtoIjuzW02nq7YhA/
tNJzEIvB/GAhxHRxXdAMAy19hKx7J6px7LMIBwCBonMRDGyFYSsxKRns0UQjP2T4
k2w2oM9Z3oDvzJ97teXfSfkbGlUH/2CBnJx6y/Y6noxF9fskGqjCHtOEZSEcVx4u
YhWTFDFZZbPKXP9HkGaUGoBLhvKsv4qGg3njwIbkb2AUyLOlvo4r6Z2r3xxmjONA
JxTZqhD6To/EP+GeTQa4e11hLDVyspFY0Y0c9lJhWvGjKo0LJAKVjCVAjvFcVoPm
sABKeQuxnAXQv67lhnogvpPvostWtGItlLUitDRtCVxCpHjBEwDi+2oqsJkAJvIq
KXRT5j/X+cnsr8sGYpLl+PtkOGNcUd9LQYQnBN0KgZa1LCdaClDvxIq1gwN/is63
1n0YkqBICiKWbhk2ieQ5891Zwh9GtaPRliKE9T27FROgj829nAtAfM9xJS8hHnnT
A6S8bEgndU4kcg0fUHnTgp/bKm25tVjvRtvAES29l6MKSMYljpckSxWJtDQcRFUb
NDbGu0WFsioE/5B0VkRV
=knBO
-----END PGP SIGNATURE-----