One of the greatest advantages of a Database Management System is its embedded data dictionary, also called metadata or system catalog [1]. On PostgreSQL DBMS it is not different, and its metadata are widely used by database handling tools.
A great and yet simple tool available to this relational database is psql [2], a command-line, bash-enabled application that permits issuing SQL instructions to PostgreSQL server instances.
Even when sending a simple command such as "\d" on psql, there might be one or several SQL instructions sent internally to that given instance. Knowing exactly what is being sent could help a database administrator or even a simple developer on common and ordinary daily tasks.
So, how could we know those SQL instructions? Simpler than expected!
There is an environment variable on psql called ECHO_HIDDEN, which once set echoes all hidden instructions, as its own name says. In order to enable that, simply execute the following command:
# \set ECHO_HIDDEN
Then, try to issue some internal commands like "\l" (typing "\?" shows the entire list of options). Here is the output after enabling ECHO_HIDDEN:
# \l
********* QUERY **********
SELECT d.datname as "Name",
r.rolname as "Owner",
pg_catalog.pg_encoding_to_char(d.encoding) as "Encoding"
FROM pg_catalog.pg_database d
JOIN pg_catalog.pg_roles r ON d.datdba = r.oid
ORDER BY 1;
**************************
List of databases
Name | Owner | Encoding
-------------+----------------+----------
auction5 | sa_auction5 | UTF8
postgres | postgres | UTF8
rodrigo | rodrigo | UTF8
template0 | postgres | UTF8
template1 | postgres | UTF8
(5 rows)
Note the contents enclosed by "QUERY" and "***". Here are the internal SQL instructions respectively performed.
When you're done, you could deactivate this variable by executing the command below:
# \unset ECHO_HIDDEN
That's it! :D
References:
[1] PostgreSQL 8.3.8 Documentation - System Catalogs
[2] psql - PostgreSQL interactive terminal
