Re: Unexpected omission of tables with duplicate names across schemas

On Wed, Sep 29, 2010 at 2:19 PM, Chris Ross <cross(at)markmonitor(dot)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...
>
>  After thinking about this a little more, I think the problem here is more
> subtle/complex.  The problem, in some ways, is that I am (in some ways)
> misusing search_path to hide parts of the database from my view.
>
>  We have a database with dozens of schemas, some of which have many dozens
> of tables et al.  So, I typically use search_path to modify which section of
> the database I'm looking at, and I typically schema-qualify everything when
> I code (and often when I'm just typing in psql).  So it's not really that 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.
>
>  Of course, that's not what search_path is *for*, so it's a confusing issue.
>  I'm using it for something slightly along-side what it is really designed
> for.  But, except for this minor issue, it works well for that.
>
>  Does this help make more clear what problem I'm trying to solve, and the
> 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.

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