List of tables in current schema

[femibyte]

Hi, how can I check the list of tables in the current schema?
The following query

Code: Select all

select table_name from v_catalog.tables
where table_schema=(select current_schema())

produces the following error:

ERROR: Meta-function("current_schema") can be used only in the Select clause; Error while executing the query.

[JimKnicely]

Hi,

I too find it annoying that we can't use some meta-functions other than simply as "SELECT current_schema();" and "SELECT last_insert_id()':.

Maybe you can try something like this?

Code: Select all

SELECT table_schema,
       table_name
  FROM tables
 WHERE table_schema = (SELECT split_part(trim(split_part(qp.query_search_path, ',', 2)), ' ', 1) the_current_schema
                         FROM current_session cs
                         JOIN query_profiles qp
                           ON qp.transaction_id = cs.transaction_id
                          AND qp.statement_id = cs.statement_id)
 ORDER BY table_name;

[JimKnicely]

Note, if you have version 6.1.1, you can try the following query... The SEARCH_PATH column should be in the USERS table now:

Code: Select all

SELECT table_schema,
       table_name
  FROM tables
 WHERE table_schema = (SELECT split_part(trim(split_part(search_path, ',', 2)), ' ', 1) the_current_schema
                         FROM users
                        WHERE user_name = user)
 ORDER BY table_name;

[Julie]

Hi,

I wonder why current_user() = user:

Code: Select all

dbadmin=> select current_user(), user;
 current_user | current_user
--------------+--------------
 dbadmin      | dbadmin
(1 row)

But current_schema <> schema:

Code: Select all

dbadmin=> select current_schema(), schema;
ERROR 4856:  Syntax error at or near "schema" at character 26
LINE 1: select current_schema(), schema;
                                 ^