Stashing Previously Set psql Variables

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 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.

The psql documentation describes "a number of ... variables [that] are treated specially by psql." These "specially treated variables" are the ones that we most likely want to ensure that we set for our script's duration only and the restore their previously set values upon script exit. The documentation describes these "specially treated variables": "They represent certain option settings that can be changed at run time by altering the value of the variable, or in some cases represent changeable state of psql. By convention, all specially treated variables' names consist of all upper-case ASCII letters (and possibly digits and underscores). To ensure maximum compatibility in the future, avoid using such variable names for your own purposes." Examples of these "specially treated variables" include AUTOCOMMIT, ECHO, ECHO_HIDDEN, PROMPT1, PROMPT2, PROMPT3, and VERBOSE, but there are many more.

For demonstration purposes, let's suppose you want to set the ECHO variable to something other than its default (none). For our purposes, we'll set ECHO to queries. We want to make sure, however, that we set it back to whatever it was when our script was called before leaving the script. The following simple psql logic accomplishes this.

\set PRIOR_ECHO :ECHO
\set ECHO queries

-- Run various queries for which you want to see the query itself output before the query results ...

\set ECHO :PRIOR_ECHO

It's as simple as that to temporarily set "specially treated variables" for your script's convenience without permanently changing the settings for the caller who might be running your script in the same psql session. The key things to remember are that the \set meta-command is always all lowercase, the specially treated variables have names that are always all uppercase but the specially treated variable values do not need to be uppercase (and typically are not), and the values in a variable can be accessed by prefixing the variable name with a colon (:).