What happens when you run PSQL slash commands?

Running \? within a psql database gives a whole list of commands that come in handy when performing various kinds of tasks.

$ psql
psql (13.0)
Type "help" for help.

wangonya=# \?
General
  \copyright             show PostgreSQL usage and distribution terms
  \crosstabview [COLUMNS] execute query and display results in crosstab
  \errverbose            show most recent error message at maximum verbosity

  .....

But what are the queries executed when these commands run?

I was recently tasked to write a bash script that loops through every schema on a database and does a pg_dump on it for backup then uploads the backups to an external server.

Running \dn gives a list of schemas but it also gives the owners, which I didn't need in my case.

wangonya=# \dn                                                             
  List of schemas
  Name  |  Owner   
--------+----------
 public | wangonya
 x      | wangonya
 y      | wangonya
(3 rows)

I only needed the schema names so I had to look for a way to edit the query run by \dn to only return names.

From the psql docs:

-E
--echo-hidden

Echo the actual queries generated by \d and other backslash commands. You can use this to study psql's internal operations. This is equivalent to setting the variable ECHO_HIDDEN to on.

Running psql with the -E flag then running the /dn command gives this result:

$ psql -E

wangonya-# \dn
********* QUERY **********
SELECT n.nspname AS "Name",
  pg_catalog.pg_get_userbyid(n.nspowner) AS "Owner"
FROM pg_catalog.pg_namespace n
WHERE n.nspname !~ '^pg_' AND n.nspname <> 'information_schema'
ORDER BY 1;
**************************

  List of schemas
  Name  |  Owner   
--------+----------
 public | wangonya
 x      | wangonya
 y      | wangonya
(3 rows)

With the underlying query executed on running the slash command, I was able to edit it get the desired result.

Comments

[Manuel Tancoigne]

Simple and useful!