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
--
Sent via pgsql-general mailing list (pgsql-general@xxxxxxxxxxxxxx)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
