Re: Unexpected omission of tables with duplicate names across schemas

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?

Thanks again.

- Chris