Setting PostgreSQL psql Variable Based Upon Query Result

Dustin MarxAugust 25th, 2015Last Updated: August 23rd, 2015

0 1,230 2 minutes read

When using PostgreSQL‘s psql command-line tool to interact with a PostgreSQL database via operator interaction or script, it is not uncommon to want to set psql variables based on the results of a particular query. While PostgreSQL’s procedural language PL/pgSQL supports approaches such as SELECT INTO and assignment (:=) to set PL/pgSQL variables based on a query result, these approaches are not supported for psql variable assignment.

The typical way to make a psql variable assignment is via use of \set. This allows for the setting of the psql variable to a literal value. However, there are situations in which it is desirable to set the psql variable based upon the result of a query. This is done with the \gset option in psql. Unlike the \set operation in psql which sets a variable with an explicitly specified name to an explicitly specified literal value, \gset implicitly names the psql variables after the names of the columns (or aliases if columns are aliased) returned by the query to which the \gset is associated. The \gset is specified after the query (no semicolon generally on the query) and there is no semicolon after the \gset statement (just as no semicolon should be placed after a \set statement).

It is easier to see how \gset works with a code sample. The next code listing shows a small psql file that takes advantage of \gset to set a psql variable named “name” whose value that was set by the query is displayed using psql’s colon prefix notation to “echo” its value.

CREATE TABLE person
(
   name text
);

INSERT INTO person (name) VALUES ('Dustin');

SELECT name FROM person \gset
\echo :name

DROP TABLE person;

In the previous code listing, lines 8-9 are the relevant lines for this discussion (the remainder of the lines are for setup and teardown of the demonstration). Line 8 contains the query (sans semicolon) followed by \gset. A psql variable of ‘name’ is set by that as evidenced by the echo-ing of its value in line 9. The output showing this works looks like this in a psql terminal window:

CREATE TABLE
INSERT 0 1
Dustin
DROP TABLE

Additional Considerations When Using psql’s \gset

Placement of a semicolon between the query and the \gset affects the output.
- Placing a semicolon after the query and before the \gset will execute the query and display the query results before setting the variable(s).
- Leaving the semicolon out will execute the query to populate parameters with the names of the query’s columns and aliases, but will not display the actual query results.
There should be no semicolon after the entire statement and placing a semicolon after the \gset will mess up the variable setting.
- Error: invalid command \gset;
Query being used to set variable via \gset should return exactly one row.
- ERROR: more than one row returned for \gset
When a column in the SELECT clause of a query associated with \gset is aliased, there are actually two psql variable names by which the returned value can be accessed. They are the column name and the alias name.
- This allows a psql developer to alias a predefined column to any name he or she prefers for the variable set by \gset.

Conclusion

When using psql, use \set variable_name variable_value to explicitly set a psql variable with the name provided by the first argument and an associated value provided by the second argument. To set a psql variable based on query results, append \gset after the query (without semicolon generally) and access returned values by column names (or by columns’ aliased names).

Reference:

Setting PostgreSQL psql Variable Based Upon Query Result from our JCG partner Dustin Marx at the Inspired by Actual Events blog.