PostgreSQL DBA(148) - pgAdmin（Showscript for psql command）

本节简单介绍了如何显示psql命令内置的SQL。
我们在使用psql的时候，可以使用\d,\d+,\df,\sf等命令来查询相关信息，这些命令背后的SQL是什么呢？可以通过设置ECHO_HIDDEN on来查看相应的SQL。

[local:/data/run/pg12]:5120 pg12@testdb=# \set  ECHO_HIDDEN on[local:/data/run/pg12]:5120 pg12@testdb=# \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' 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.relnamespaceWHERE c.relkind IN ('r','p','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 | a                  | table | pg12 public | b                  | table | pg12 public | rel                | table | pg12 public | t                  | table | pg12 public | t1                 | table | pg12 public | t2                 | table | pg12 public | t_autovacuum_1     | table | pg12 public | t_autovacuum_db1   | table | pg12 public | t_big_autovacuum_1 | table | pg12 public | t_count            | table | pg12 public | t_fillfactor_100   | table | pg12 public | t_fillfactor_50    | table | pg12 public | t_fillfactor_70    | table | pg12 public | t_mvcc             | table | pg12 public | t_tx               | table | pg12 public | tbl                | table | pg12(16 rows)[local:/data/run/pg12]:5120 pg12@testdb=# \d+ t_mvcc********* QUERY **********SELECT c.oid,  n.nspname,  c.relnameFROM pg_catalog.pg_class c     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespaceWHERE c.relname OPERATOR(pg_catalog.~) '^(t_mvcc)$' COLLATE pg_catalog.default  AND pg_catalog.pg_table_is_visible(c.oid)ORDER BY 2, 3;*********************************** QUERY **********SELECT c.relchecks, c.relkind, c.relhasindex, c.relhasrules, c.relhastriggers, c.relrowsecurity, c.relforcerowsecurity, false AS relhasoids, c.relispartition, pg_catalog.array_to_string(c.reloptions || array(select 'toast.' || x from pg_catalog.unnest(tc.reloptions) x), ', '), c.reltablespace, CASE WHEN c.reloftype = 0 THEN '' ELSE c.reloftype::pg_catalog.regtype::pg_catalog.text END, c.relpersistence, c.relreplident, am.amnameFROM pg_catalog.pg_class c LEFT JOIN pg_catalog.pg_class tc ON (c.reltoastrelid = tc.oid)LEFT JOIN pg_catalog.pg_am am ON (c.relam = am.oid)WHERE c.oid = '74886';*********************************** QUERY **********SELECT a.attname,  pg_catalog.format_type(a.atttypid, a.atttypmod),  (SELECT substring(pg_catalog.pg_get_expr(d.adbin, d.adrelid, true) for 128)   FROM pg_catalog.pg_attrdef d   WHERE d.adrelid = a.attrelid AND d.adnum = a.attnum AND a.atthasdef),  a.attnotnull,  (SELECT c.collname FROM pg_catalog.pg_collation c, pg_catalog.pg_type t   WHERE c.oid = a.attcollation AND t.oid = a.atttypid AND a.attcollation <> t.typcollation) AS attcollation,  a.attidentity,  a.attgenerated,  a.attstorage,  CASE WHEN a.attstattarget=-1 THEN NULL ELSE a.attstattarget END AS attstattarget,  pg_catalog.col_description(a.attrelid, a.attnum)FROM pg_catalog.pg_attribute aWHERE a.attrelid = '74886' AND a.attnum > 0 AND NOT a.attisdroppedORDER BY a.attnum;*********************************** QUERY **********SELECT pol.polname, pol.polpermissive,  CASE WHEN pol.polroles = '{0}' THEN NULL ELSE pg_catalog.array_to_string(array(select rolname from pg_catalog.pg_roles where oid = any (pol.polroles) order by 1),',') END,  pg_catalog.pg_get_expr(pol.polqual, pol.polrelid),  pg_catalog.pg_get_expr(pol.polwithcheck, pol.polrelid),  CASE pol.polcmd    WHEN 'r' THEN 'SELECT'    WHEN 'a' THEN 'INSERT'    WHEN 'w' THEN 'UPDATE'    WHEN 'd' THEN 'DELETE'    END AS cmdFROM pg_catalog.pg_policy polWHERE pol.polrelid = '74886' ORDER BY 1;*********************************** QUERY **********SELECT oid, stxrelid::pg_catalog.regclass, stxnamespace::pg_catalog.regnamespace AS nsp, stxname,  (SELECT pg_catalog.string_agg(pg_catalog.quote_ident(attname),', ')   FROM pg_catalog.unnest(stxkeys) s(attnum)   JOIN pg_catalog.pg_attribute a ON (stxrelid = a.attrelid AND        a.attnum = s.attnum AND NOT attisdropped)) AS columns,  'd' = any(stxkind) AS ndist_enabled,  'f' = any(stxkind) AS deps_enabled,  'm' = any(stxkind) AS mcv_enabledFROM pg_catalog.pg_statistic_ext stat WHERE stxrelid = '74886'ORDER BY 1;*********************************** QUERY **********SELECT pubnameFROM pg_catalog.pg_publication pJOIN pg_catalog.pg_publication_rel pr ON p.oid = pr.prpubidWHERE pr.prrelid = '74886'UNION ALLSELECT pubnameFROM pg_catalog.pg_publication pWHERE p.puballtables AND pg_catalog.pg_relation_is_publishable('74886')ORDER BY 1;*********************************** QUERY **********SELECT c.oid::pg_catalog.regclass FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhparent AND i.inhrelid = '74886' AND c.relkind != 'p' ORDER BY inhseqno;*********************************** QUERY **********SELECT c.oid::pg_catalog.regclass,       pg_catalog.pg_get_expr(c.relpartbound, c.oid),       c.relkind FROM pg_catalog.pg_class c, pg_catalog.pg_inherits i WHERE c.oid=i.inhrelid AND i.inhparent = '74886' ORDER BY pg_catalog.pg_get_expr(c.relpartbound, c.oid) = 'DEFAULT',          c.oid::pg_catalog.regclass::pg_catalog.text;**************************                                  Table "public.t_mvcc" Column |  Type   | Collation | Nullable | Default | Storage | Stats target | Description --------+---------+-----------+----------+---------+---------+--------------+------------- id     | integer |           |          |         | plain   |              | Access method: heap[local:/data/run/pg12]:5120 pg12@testdb=#