Page 1 of 1
List of tables in current schema
Posted: Tue Mar 19, 2013 9:25 pm
by 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.
Re: List of tables in current schema
Posted: Wed Mar 20, 2013 2:35 pm
by 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;
Re: List of tables in current schema
Posted: Wed Mar 20, 2013 2:41 pm
by 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;
Re: List of tables in current schema
Posted: Wed Mar 20, 2013 5:53 pm
by 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;
^