1

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?

3

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.

| improve this answer | |
  • Just what I needed, thanks! One small correction for me though: The delimiter seems to only be ,, without the space. – xehpuk May 31 '18 at 15:32
  • @xehpuk that's surprising, at me it works both ways. – dezso May 31 '18 at 15:36
  • I'm on version 9.6.8, if that makes any difference. Couldn't find any changes in the release notes. – xehpuk May 31 '18 at 15:45

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.