Writing a conditional psql script

psql supports conditional meta-commands \if, \elif, \else and \endif . In this recipe we demonstrate some of them.

Getting ready

We want to improve the vartest.sql script so that it runs VACUUM only if there actually are dead rows in that table.

How to do it...

We add conditional commands to vartest.sql resulting in the following script:

SELECT schemaname
, relname
, n_dead_tup
, n_live_tup
, n_dead_tup > 0 AS needs_vacuum
FROM pg_stat_user_tables
ORDER BY n_dead_tup DESC
LIMIT 1
\gset
\if :needs_vacuum
\qecho Running VACUUM on table :"relname...