在psql中,我们可以通过一系列的的快捷命令查看数据库元素,如:\d 查看当前搜索路径下的表,那么内部用到的SQL语句是什么呢,可以通过命令来设置是否打印出来:
apple=# \set ECHO_HIDDEN on
apple=# \c mydb
You are now connected to database “mydb” as user “apple”.
mydb=# \d
********* QUERY **********
SELECT n.nspname as “Schema”,
c.relname as “Name”,
CASE c.relkind WHEN ‘r’ THEN ‘table’ WHEN ‘v’ THEN ‘view’ WHEN ‘m’ THEN ‘materialized view’ WHEN ‘i’ THEN ‘index’ WHEN ‘S’ THEN ‘sequence’ WHEN ‘s’ THEN ‘special’ WHEN ‘f’ THEN ‘foreign table’ END as “Type”,
pg_catalog.pg_get_userbyid(c.relowner) as “Owner”
FROM pg_catalog.pg_class c
LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN (‘r’,‘v’,‘m’,‘S’,‘f’,’’)
AND n.nspname <> ‘pg_catalog’
AND n.nspname <> ‘information_schema’
AND n.nspname !~ ‘^pg_toast’
AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
List of relations
Schema | Name | Type | Owner
--------±----------------±--------------±---------
public | employees | table | postgres
public | indent | table | apple
public | indent_id_seq | sequence | apple
public | student | table | postgres
public | table_new | table | postgres
public | test1 | table | apple
public | test_cur | table | postgres
public | test_from_apple | foreign table | apple
public | test_id_seq | sequence | apple
public | test_time | table | apple
public | test_type | table | apple
(11 rows)
mydb=# \set ECHO_HIDDEN off
mydb=# \d
List of relations
Schema | Name | Type | Owner
--------±----------------±--------------±---------
public | employees | table | postgres
public | indent | table | apple
public | indent_id_seq | sequence | apple
public | student | table | postgres
public | table_new | table | postgres
public | test1 | table | apple
public | test_cur | table | postgres
public | test_from_apple | foreign table | apple
public | test_id_seq | sequence | apple
public | test_time | table | apple
public | test_type | table | apple
(11 rows)