On 2nd of February, Tom Lane committed patch:

Create a psql command \gset to store query results into psql variables.
 
This eases manipulation of query results in psql scripts.
 
Pavel Stehule, reviewed by Piyush Newe, Shigeru Hanada, and Tom Lane

psql is the basic client application / query tool for PostgreSQL. As such, it is used daily by quite a lot of people, including me.

It has support for variables (and this support is in my opinion pretty confusing, with \pset, \set and set commands all doing different things), but so far all you could do was either set the variable to some literal value, like:

\SET var 'depesz'

or set it to value returned by shell command, like:

\SET var `date`

Eventually, prompt user to provide the value, like:

\prompt 'Value? ' var

While it generally works, and you can do a lot of stuff with it, some things are missing. And Pavel is working on fixing the deficiencies.

Today, we got support for setting values based on queries.

This actually could have been done before, but in a not really nice way:

$ \pset format unaligned
Output format IS unaligned.
 
$ \pset tuples_only ON
Showing ONLY tuples.
 
$ SELECT now() \g /tmp/output.txt
 
$ \pset tuples_only off
Tuples ONLY IS off.
 
$ \pset format aligned
Output format IS aligned.
 
$ \SET var `cat /tmp/output.txt`

Now, it's much cleaner syntax, and without any side effects:

$ SELECT now() \gset
 
$ \echo :now
2013-02-03 15:32:33.772225+01

That is – ending your query with \gset will set variable named like returned column, to value that was set in this column.

Nice. What about multiple columns?

$ SELECT relname, relkind FROM pg_class LIMIT 1 \gset
 
$ \echo :relname :relkind
pg_statistic r

The cool thing is that you can provide variable name prefix, like this:

$ SELECT relname, relkind FROM pg_class LIMIT 1 \gset prefix
 
$ \echo :prefixrelname :prefixrelkind
pg_statistic r

In case your query would return more than one row, psql will simply return an error – since there its not obvious which value would get stored in variable:

$ SELECT i FROM generate_series(1,2) i \gset
more than one ROW returned FOR \gset

I like it a lot. Thanks Pavel.