search_path and current_schema
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
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
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:
Show quoted text
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