Get equivalent SQL query for any Postgres meta-command

Question

I hope the title is self-descriptive. I want to be able somehow to translate any Postgres meta-command into its corresponding/underlying SQL query, at least to learn more about Postgres and the way it stores meta-info in its tables.

Any ideas if this is possible?

E.g.:

When connected to the database EXAMPLE, \dt and SELECT table_schema,table_name FROM information_schema.tables ORDER BY table_schema,table_name; return the same result.

I want to find, if possible, a way to obtain the value SELECT table_schema,table_name FROM information_schema.tables ORDER BY table_schema,table_name; when inputting \dt to the function/macro/whatever.

Answer 1

Easy and very useful: you can launch psql with the proper switch (-E) to get the information.

me@mystation:~/ > psql -E 
psql (9.3.11)
Type "help" for help.
me@mystation # \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;

Have a look at http://www.postgresql.org/docs/current/static/app-psql.html for more information. Once in psql, you could also set the ECHO_HIDDEN variable.

• philwalk: very promising! but when I try to get a table definition with 'psql -E mytable' it reports several queries, none of which return anything useful when issued individually, so something unreported is going on.