PostgreSQL 显示快捷命令真实执行的 SQL

需求

查看\dt、\di等快捷命令实际执行的SQL语句。

方法一

启动psql时指定-E参数

psql -E

查看帮助

psql --help

输出

Input and output options:
  -a, --echo-all           echo all input from script
  -b, --echo-errors        echo failed commands
  -e, --echo-queries       echo commands sent to server
  -E, --echo-hidden        display queries that internal commands generate

方法二

注意：ECHO_HIDDEN必须大写。

ECHO_HIDDEN取值：noexec/on/off。

noexec：只打印使用的sql，但不真正执行。

\set ECHO_HIDDEN on

on

z-blog=# \set ECHO_HIDDEN on
z-blog=# \dt
********* 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 't' THEN 'TOAST table' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' 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
     LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam
WHERE c.relkind IN ('r','p','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname !~ '^pg_toast'
      AND n.nspname <> 'information_schema'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************

                 List of relations
 Schema |          Name          | Type  |  Owner
--------+------------------------+-------+----------
 public | dashboard_user         | table | z-blog
 public | english                | table | z-blog
 public | ip_pool                | table | z-blog
 public | ip_unknown             | table | z-blog
 public | link                   | table | z-blog
 public | message_board          | table | z-blog
 public | post                   | table | z-blog
 public | record_invalid_request | table | z-blog
 public | record_page_view       | table | z-blog
 public | record_search          | table | z-blog
 public | topic                  | table | z-blog
(13 rows)

noexec

z-blog=# \set ECHO_HIDDEN noexec
z-blog=# \dt
********* 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 't' THEN 'TOAST table' WHEN 'f' THEN 'foreign table' WHEN 'p' THEN 'partitioned table' WHEN 'I' THEN 'partitioned index' 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
     LEFT JOIN pg_catalog.pg_am am ON am.oid = c.relam
WHERE c.relkind IN ('r','p','')
      AND n.nspname <> 'pg_catalog'
      AND n.nspname !~ '^pg_toast'
      AND n.nspname <> 'information_schema'
  AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************