【PostgreSQL-9.6.3】如何得到psql中命令的实际执行SQL

最新推荐文章于 2025-07-15 09:28:14 发布

原创最新推荐文章于 2025-07-15 09:28:14 发布 · 1.8k 阅读

0 ·

CC 4.0 BY-SA版权

PostgreSQL 专栏收录该内容

29 篇文章

订阅专栏

本文介绍了如何在psql环境中配置查询执行过程的显示。通过两种方法实现：一是启动psql时添加-E参数；二是利用set ECHO_HIDDEN on|off命令进行动态开关。

当我们在psql界面执行以“\”开头的命令时，数据库会立刻返回执行结果，而不会返回命令的实际执行过程。通过两种方式可以实现执行过程的查看：

方法一：启动psql命令时加“-E”参数

postgres@lgr-pc:~$ psql -E test
psql (9.6.3)
Type "help" for help.


test=# \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 | people   | table | postgres
 public | persons  | table | postgres
 public | students | table | postgres
 public | t1       | table | postgres
 public | t11      | table | postgres
 public | t2       | table | postgres
 public | tmp12    | table | postgres
 public | tmp15    | table | postgres

这种方式只能在启动psql时使用，如果想关闭查询功能只能重新启动psql。

方法二：在psql中执行“\set ECHO_HIDDEN on|off”命令。on为开启，off为关闭，“ECHO_HIDDEN”必须为大写。

test=# \set ECHO_HIDDEN on
test=# \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 | people   | table | postgres
 public | persons  | table | postgres
 public | students | table | postgres
 public | t1       | table | postgres
 public | t11      | table | postgres
 public | t2       | table | postgres
 public | tmp12    | table | postgres
 public | tmp15    | table | postgres
(8 rows)


test=# \set ECHO_HIDDEN off
test=# \d
          List of relations
 Schema |   Name   | Type  |  Owner   
--------+----------+-------+----------
 public | people   | table | postgres
 public | persons  | table | postgres
 public | students | table | postgres
 public | t1       | table | postgres
 public | t11      | table | postgres
 public | t2       | table | postgres
 public | tmp12    | table | postgres
 public | tmp15    | table | postgres
(8 rows)