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.