PSQL中命令实际执行的SQL

作者：瀚高PG实验室 （Highgo PG Lab）- 海无涯

在启动psql的命令中添加 “-E”参数，运行”\”开头的命令就可以把实际的SQL打印出来。

举例：

[highgo@Higtest ~]$ psql -E

psql (4.1.1)

 

PSQL: Release 4.1.1

Connected to:

HighGo Database V4.1 Enterprise EditionRelease 4.1.1 - 64-bit Production

 

Type "help" for help.

 

highgo=# \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' WHEN 'P' THEN 'partitioned 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','P','v','m','S','f','')

     AND n.nspname <> 'pg_catalog'

     AND n.nspname <> 'information_schema'

     AND n.nspname !~ '^pg_toast'

  ANDpg_catalog.pg_table_is_visible(c.oid)

ORDER BY 1,2;

**************************

 

          List of relations

    Schema     | Name | Type  | Owner 

----------------+------+-------+--------

 oracle_catalog | dual | view  | highgo

 public        | t1   | table | highgo

(2 rows)

 

此外可以使用 \SET ECHO_HIDDEN ON  打开实际执行SQL的设置

（注意ECHO_HIDDEN必须为大写，否则无法生效）

highgo=# \set ECHO_HIDDEN off

highgo=# \d

          List of relations

    Schema     | Name | Type  | Owner 

----------------+------+-------+--------

 oracle_catalog | dual | view  | highgo

 public        | t1   | table | highgo

(2 rows)

 

highgo=# \set echo_hidden on 

highgo=# \d

          List of relations

    Schema     | Name | Type  | Owner 

----------------+------+-------+--------

 oracle_catalog | dual | view  | highgo

 public        | t1   | table | highgo

(2 rows)此外可以使用 \SET ECHO_HIDDEN ON  打开实际执行SQL的设置

（注意ECHO_HIDDEN必须为大写，否则无法生效）

highgo=# \set ECHO_HIDDEN off

highgo=# \d

          List of relations

    Schema     | Name | Type  | Owner 

----------------+------+-------+--------

 oracle_catalog | dual | view  | highgo

 public        | t1   | table | highgo

(2 rows)

 

highgo=# \set echo_hidden on 

highgo=# \d

          List of relations

    Schema     | Name | Type  | Owner 

----------------+------+-------+--------

 oracle_catalog | dual | view  | highgo

 public        | t1   | table | highgo

(2 rows)