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

来源：互联网发布：php社交网站论文编辑：程序博客网时间：2024/06/14 23:01

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

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

postgres@lgr-pc:~$ psql -E testpsql (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.relnamespaceWHERE 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 ontest=# \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.relnamespaceWHERE 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 offtest=# \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)

阅读全文

0 0