patternsqlMajor
Get equivalent SQL query for any Postgres meta-command
Viewed 0 times
equivalentpostgressqlqueryanymetagetforcommand
Problem
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,
I want to find, if possible, a way to obtain the value
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.Solution
Easy and very useful: you can launch psql with the proper switch (-E) to get the information.
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.
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.
Code Snippets
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;Context
StackExchange Database Administrators Q#131028, answer score: 31
Revisions (0)
No revisions yet.