Re: Unexpected omission of tables with duplicate names across schemas

On Wed, Sep 29, 2010 at 2:19 PM, Chris Ross <cross@markmonitor.com> wrote:
> On 09/29/2010 02:08 PM, Chris Ross wrote:
>>
>> On 09/28/2010 01:17 PM, Tom Lane wrote:
>>>
>>> That's the intended behavior, because only the first one is actually
>>> accessible without schema-qualifying its name. You can use a pattern
>>> of "*.*" if you want to see objects that are hidden according to the
>>> search path. The default behavior is equivalent to a pattern of "*",
>>> which only shows objects reachable with unqualified names.
>>
>> Is there a way to ask the database "What are all of the tables/views/etc
>> in my current search path?" without having it infer "that I can reach
>> without schema-qualifing them" ?
>>
>> That's what I've always used \d for, and while it's certainly a habit
>> rather than anything documented explicitly to do what I think it should
>> do, there needs to be *a* way to do this I think...
>
> =A0After thinking about this a little more, I think the problem here is m=
ore
> subtle/complex. =A0The problem, in some ways, is that I am (in some ways)
> misusing search_path to hide parts of the database from my view.
>
> =A0We have a database with dozens of schemas, some of which have many doz=
ens
> of tables et al. =A0So, I typically use search_path to modify which secti=
on of
> the database I'm looking at, and I typically schema-qualify everything wh=
en
> I code (and often when I'm just typing in psql). =A0So it's not really th=
at I
> want to know what I can reach without schema qualifications, it's that I
> want a list of all things that exist, but only within the schemas that are
> in my search_path.
>
> =A0Of course, that's not what search_path is *for*, so it's a confusing i=
ssue.
> =A0I'm using it for something slightly along-side what it is really desig=
ned
> for. =A0But, except for this minor issue, it works well for that.
>
> =A0Does this help make more clear what problem I'm trying to solve, and t=
he
> problem as I see it?

I think what you're trying to do is totally reasonable, but psql
doesn't really support it.  You might be able to rig something up
using macros (see \set).

There's a pretty significant difference between the way that graphical
admin tools like pgAdmin view schemas and the way they look from psql.
 In pgAdmin, you get a tree (which is how you seem to be thinking
about it), whereas in psql it tends to feel more like a flat namespace
that's constructed by smashing several namespaces together, a la UNIX
$PATH.  The underlying reality is that it's some of both.

--=20
Robert Haas
EnterpriseDB: http://www.enterprisedb.com
The Enterprise Postgres Company