Skip site navigation (1) Skip section navigation (2)

Re: BUG #2850: Cannot select from information_schema.schemat

From: Tom Lane <tgl(at)sss(dot)pgh(dot)pa(dot)us>
To: "Tony Marston" <tony(at)marston-home(dot)demon(dot)co(dot)uk>
Cc: pgsql-bugs(at)postgresql(dot)org
Subject: Re: BUG #2850: Cannot select from information_schema.schemat
Date: 2006-12-21 15:02:44
Message-ID: 19145.1166713364@sss.pgh.pa.us (view raw or flat)
Thread:
Lists: pgsql-bugs
"Tony Marston" <tony(at)marston-home(dot)demon(dot)co(dot)uk> writes:
> If I am logged on as a user other than 'postgres' and I try the query
> SELECT * FROM information_schema.schemata
> I get no results. Yet if I try the equivalent query
> SELECT * FROM pg_namespace
> I can see all the available schema names.

> Why is there a difference when the two queries are supposed to provide the
> same results?

They're not "supposed to provide the same results".  Per SQL99, the
schemata view is supposed to

         Identify the schemata in a catalog that are owned by a given user.

and the SQL definition in the spec makes it clear that it only shows
schemas owned by CURRENT_USER or a role that CURRENT_USER is a member
of.

			regards, tom lane

In response to

pgsql-bugs by date

Next:From: Tom LaneDate: 2006-12-21 15:33:41
Subject: Re: BUG #2848: information_schema.key_column_usage does not work
Previous:From: Pavel GolubDate: 2006-12-21 12:55:16
Subject: BUG #2852: User-defined type name begins with the underscore character (_) can be created

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group