On 12nd of September 2017, Tom Lane committed patch:
Add psql variables to track success/failure of SQL queries. This patch adds ERROR, SQLSTATE, and ROW_COUNT, which are updated after every query, as well as LAST_ERROR_MESSAGE and LAST_ERROR_SQLSTATE, which are updated only when a query fails. The expected usage of these is for scripting. Fabien Coelho, reviewed by Pavel Stehule Discussion: https://postgr.es/m/alpine.DEB.2.20..12290@lancre
Description from commit message seems pretty straightforward, but let's see how it actually works:
=$ \echo :ERROR :ERROR
That's surprising. But understandable – I ran it as first command in connection, so the state error/not-error is undefined.
So, let's see some working queries:
=$ SELECT 1; ?COLUMN? ---------- 1 (1 ROW) =$ \echo :ERROR FALSE =$ \echo :SQLSTATE 00000 =$ \echo :ROW_COUNT 1 =$ \echo :LAST_ERROR_MESSAGE =$ \echo :LAST_ERROR_MESSAGE
and what if I'll run some error?
=$ SELECT 1/0; ERROR: division BY zero =$ \echo :ERROR TRUE =$ \echo :SQLSTATE 22012 =$ \echo :ROW_COUNT 0 =$ \echo :LAST_ERROR_MESSAGE division BY zero =$ \echo :LAST_ERROR_MESSAGE division BY zero
The cool thing is that you can then use \if things to “do stuff". For example, let's look at this:
BEGIN; CREATE TABLE z (i int4, j int4); ALTER TABLE z ADD COLUMN j int4; commit;
The idea is that we want to create table, or add column if it is necessary. We could use create table if not exists, but let's use our new toy to do it another way:
BEGIN; SAVEPOINT sp1; CREATE TABLE z (i int4, j int4); \IF :ERROR ROLLBACK TO SAVEPOINT sp1; \ELSE release SAVEPOINT sp1; \endif SAVEPOINT sp2; ALTER TABLE z ADD COLUMN j int4; \IF :ERROR ROLLBACK TO SAVEPOINT sp2; \ELSE release SAVEPOINT sp2; \endif commit;
Now, this transaction will always succeed. If the table is not there – it will be created. And if it is, but lacks “j" column – it will be added.
Of course – this particular example would be better off with “if not exists", but I'm merely showcasing what you could do with it.
That's great addition, and I love that psql becomes more powerful. Thanks a lot.