Re: How can I find a schema that a table belong to?

Thomas Kellerer <spam_eater@gmx.net> writes:
> Jerry LeVan, 19.01.2011 17:35:
>> So I guess the question is:
>> Given a bare table name, how can I recover the schema
>> qualified name with whatever the current search path happens
>> to be?

> SELECT table_schema
> FROM information_schema.tables
> WHERE table_name = 'your_table'
> ;

That's not going to work, at least not in the interesting case where you
have more than one candidate table --- that SELECT will list all of 'em.

In most cases the answer to this type of problem is "use regclass",
but regclass doesn't quite solve Jerry's problem because it won't
schema-qualify the name if the table is visible in the search path.
The best solution I can think of is

select nspname from pg_namespace n join pg_class c on n.oid = c.relnamespace
  where c.oid = 'my_table_name'::regclass;

which works but seems a bit brute-force.

            regards, tom lane