8

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.

14

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/9.5/static/app-psql.html for more information. Once in psql, you could also set the ECHO_HIDDEN variable.

  • 1
    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. – philwalk Jul 31 '18 at 17:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.