psql supports the conditional meta-commands \if, \elif, \else, and \endif. In this recipe, we will demonstrate some of them.
We want to improve the vartest.sql, script so that it runs VACUUM only if there actually are dead rows in that table.
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" in schema :"schemaname" \qecho Rows before: :n_dead_tup dead, :n_live_tup live VACUUM ANALYZE :schemaname.:relname; \qecho Waiting 1 second... SELECT pg_sleep(1); SELECT n_dead_tup AS n_dead_tup_now , n_live_tup AS n_live_tup_now FROM pg_stat_user_tables WHERE schemaname = :'schemaname' AND relname = :'relname' \gset \qecho Rows after: :n_dead_tup_now dead, :n_live_tup_now live \else \qecho Skipping VACUUM...