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

Tom Lane, 19.01.2011 19:19:
>>> 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.

What about something like this:

SELECT tbl.table_schema, tbl.table_name, pe.path_position
FROM information_schema.tables tbl
   JOIN (
     SELECT path_element, row_number() over () as path_position
     FROM (
       SELECT trim(unnest(string_to_array(setting, ','))) as path_element
       FROM pg_settings
       WHERE name = 'search_path'
     ) t
   ) pe on tbl.table_schema = pe.path_element
WHERE tbl.table_name = 'your_table'
ORDER BY pe.path_position;

This will list each table together with the index of the schema in the search path in the order of the schemas listed
inthe search path. 

The only thing I'm unsure about is whether unnest() will always preserve the order of the array.

Regards
Thomas