Can the current search_path be used in a query?

Question

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?

Answer 1

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.

Answer 2

If you want just the search_path of the current session, then you can use the current_setting() function for that:

SELECT current_setting('search_path')

 current_setting
-----------------
 "$user", public
(1 row)

If you also want the effective search path for the current session, current_schemas(include_implicit boolean) is your friend; it returns a NAME[] array with the schemas from the search_path which exist and which the user has GRANT USAGE permissions on:

SELECT current_setting('search_path'), current_schemas(false);

 current_setting | current_schemas
-----------------+-----------------
 "$user", public | {public}
(1 row)

If, for the include_implicit parameter, you supply true instead of false, current_schemas() will also show you the schemas which are implicitly added to the effective search path:

SELECT current_setting('search_path'), current_schemas(true);

 current_setting |        current_schemas
-----------------+-------------------------------
 "$user", public | {pg_catalog,public}
(1 row)

Including the pg_tmp_<nnn> schema if the current session has any temporary objects:

CREATE TEMPORARY TABLE a (b INT);
SELECT current_schemas(true);

CREATE TABLE
        current_schemas
-------------------------------
 {pg_temp_3,pg_catalog,public}
(1 row)

Also, the "$user" expansion will be done for you, if the "$user" schema exists:

CREATE ROLE xehpuk;
CREATE SCHEMA AUTHORIZATION xehpuk;
SELECT current_setting('search_path'), current_schemas(false);

 current_setting | current_schemas
-----------------+-----------------
 "$user", public | {xehpuk,public}
(1 row)

If you want to dive deeper into search_paths, I just published an extremely long “summary” of its power and its pitfalls.