The command-line based "PostgreSQL interactive terminal" known as psql is handy for manipulating and accessing data in a PostgreSQL database. Because of its command-line nature, psql is particularly well-suited for use in scripts. One of the psql features that makes it even more useful in scripting contexts is its support for "meta-commands." As the psql documentation states, "Anything you enter in psql that begins with an unquoted backslash is a psql meta-command that..." and "...these commands make psql more useful for administration or scripting."
When writing psql scripts, it is often preferable to set some variables locally for the time period that the script is being run but might also be desirable to not change these variables permanently for the psql session if it's likely that other scripts or other work will be performed from the psql session after the script's conclusion. In this post, I will demonstrate use of psql's \set meta-command to temporarily store off previous settings of variables to restore these settings at the script's conclusion.
Revealing the Queries Behind Psql’s Backslash Commands
PostgreSQL's psql interactive terminal tool provides several useful "backslash list commands" such as \d (lists "relations" such as tables, views, indexes, and sequences), \dt (lists tables), \di (lists indexes), \ds (lists sequences), \dv (lists views), \df (lists functions), \du (lists roles), and \? (displays help/usage details on backslash commands). These commands are concise and much simpler to use than writing the queries against PostgreSQL system catalogs ( pg_class, pg_roles, pg_namespace, pg_trigger, pg_index, etc.) and information_schema that would provide the same types of details.
Although the psql backslash commands are easier to use than their associated queries, there are situations when it is important to know the full query behind a particular command. These situations include needing to perform a slightly different/adapted query from that associated with the pre-built command and needing to perform similar queries in scripts or code that are being used as PostgreSQL clients instead of psql. These situations make it important to be able to determine what queries psql is performing and the psql option -E (or --echo-hidden) allow that.