simple and nice

I got a very useful tips from PostgreSQL mailing list. If you put option -E to your psql command, it will display all the query that it used for the meta command such as \d in psql console. Cool. You can use the query if you need to peek into PostgreSQL catalog for some reason. For instance, command \d will give you the sql statement used by psql to display all object in the database.

-E
--echo-hidden
      Echo the actual queries generated by \d and other backslash commands. You can use this if you  wish  to include  similar  functionality  into  your  own  programs.  This is equivalent to setting the variable
      ECHO_HIDDEN from within psql.

some example:-

Tue Sep 20 ...> psql -E drupal
Welcome to psql 7.4.8, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

drupal=> \dt
********* QUERY **********
SELECT n.nspname as "Schema",
  c.relname as "Name",
  CASE c.relkind WHEN 'r' THEN 'table' WHEN 'v' THEN 'view' WHEN 'i' THEN 'index' WHEN 'S' THEN 'sequence' WHEN 's' THEN 'special' END as "Type",
  u.usename as "Owner"
FROM pg_catalog.pg_class c
     LEFT JOIN pg_catalog.pg_user u ON u.usesysid = c.relowner
     LEFT JOIN pg_catalog.pg_namespace n ON n.oid = c.relnamespace
WHERE c.relkind IN ('r','')
      AND n.nspname NOT IN ('pg_catalog', 'pg_toast')
      AND pg_catalog.pg_table_is_visible(c.oid)
ORDER BY 1,2;
**************************