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 in the search path.

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

Regards
Thomas