It is also possible to store some values produced by a query into variables—for instance, to reuse them later in other queries.
In this recipe, we will demonstrate this approach with a concrete example.
In the Controlling automatic database maintenance recipe of Chapter 9, Regular Maintenance, we will describe VACUUM
, showing that it runs regularly on each table based on the number of rows that might need vacuuming (dead rows). The VACUUM
command will run if that number exceeds a given threshold, which by default is just above 20% of the row count.
In this recipe, we will create a script that picks the table with the largest number of dead rows and runs VACUUM
on it.