Postgres Pro
Downloads
Home   >   mailing lists

Thread: search_path and current_schema

search_path and current_schema

From
Jayadevan M
Date:
Hi,

I was trying out schema settings and related functions. PostgreSQL version is 9.3

[postgres@MyCentOS 9.3]$ psql
psql (9.3.0)
Type "help" for help.

postgres=# show search_path;
  search_path  
----------------
 "$user",public
(1 row)

postgres=# select current_schemas(true);
   current_schemas  
---------------------
 {pg_catalog,public}
(1 row)

postgres=# set search_path=mynewschema, "$user", public;
SET
postgres=# select current_schemas(true);
   current_schemas  
---------------------
 {pg_catalog,public}
(1 row)

postgres=# show search_path;
         search_path         
------------------------------
 mynewschema, "$user", public
(1 row)

I thought current_schemas and search_path will return the same set of schemas (except that current_schema will show pg_catalog also, if we use true). Shouldn't mynewschema appear in the output of select current_schemas(true)?

Regards,
Jayadevan

Re: search_path and current_schema

From
Tom Lane
Date:
Jayadevan M <maymala.jayadevan@gmail.com> writes:
> I thought current_schemas and search_path will return the same set of
> schemas (except that current_schema will show pg_catalog also, if we use
> true). Shouldn't mynewschema appear in the output of select
> current_schemas(true)?

Only if it actually exists (and you have usage privilege on it).  See
the description of the search_path variable: nonexistent entries are
silently ignored.

Possibly this behavior should be documented under current_schemas()
as well as under the GUC variable.

            regards, tom lane

Re: search_path and current_schema

From
Jayadevan M
Date:
OK. When I logged in as a user who had access to the schema, the output from current_schemas and search_path were matching.
Thanks.


On Sun, Oct 27, 2013 at 9:55 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
Jayadevan M <maymala.jayadevan@gmail.com> writes:
> I thought current_schemas and search_path will return the same set of
> schemas (except that current_schema will show pg_catalog also, if we use
> true). Shouldn't mynewschema appear in the output of select
> current_schemas(true)?

Only if it actually exists (and you have usage privilege on it).  See
the description of the search_path variable: nonexistent entries are
silently ignored.

Possibly this behavior should be documented under current_schemas()
as well as under the GUC variable.

                        regards, tom lane