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.

  1. One comment

  2. # nickb
    Oct 2, 2017

    I wonder if this works with isolationtester. That’d be great.

Leave a comment