I'd like to do something like this:
SELECT * FROM information_schema.tables WHERE table_schema IN (search_path)
I couldn't find anything in the docs. Is it possible? How?
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It only takes a minute to sign up.Sign up to join this community
pg_settings is a system view containing all the configuration options and their values available in the current context:
The view pg_settings provides access to run-time parameters of the server. It is essentially an alternative interface to the SHOW and SET commands. It also provides access to some facts about each parameter that are not directly available from SHOW, such as minimum and maximum values.
This view you can query just like any other, so you can get the value of
search_path like this:
SELECT setting FROM pg_settings WHERE name = 'search_path';
The returned value is a
text, with the delimiter being
', '. Some tinkering is needed so that you can compare
table_schema to this:
SELECT * FROM information_schema.tables WHERE table_schema = ANY (SELECT unnest(string_to_array(setting, ', ')) FROM pg_settings WHERE name = 'search_path');
This is still only half perfect, as
"$user" can be in the
search_path. One way to solve this is to replace it with the current user like
... replace(setting, '"$user"', CURRENT_USER) ...
See all this at work on DBFiddle.