Substitution variables in psql scripts

As postgresql users and administrators, we tend to create lot of scripts, and run them routinely, for common queries we need to execute against the database. It is likely that in some of those scripts, you would want to parameterize values used in filter conditions. Below is an example of how this can be done.

psql is the terminal based front end tool to interact with postgresql. You can run scripts stored in files in the file system, using the \i directive in plsql. You can use the \set command to set variables in psql. If you want to prompt for the value to be entered by the user, you can use the \prompt directive.

\prompt 'Enter Table Name : ' tabname


select last_vacuum,last_autovacuum,last_analyze ,last_autoanalyze,n_live_tup,n_dead_tup
from pg_stat_user_tables
where relname = :'tabname';

Now if you run this from psql you will be first prompted for the table name and then it will display the results for the table name you entered.

postgres=# \i pgstats.sql
Enter Table Name : nflstats

 last_vacuum | last_autovacuum |         last_analyze         |       last_autoanalyze        | n_live_tup | n_dead_tup 
-------------+-----------------+------------------------------+-------------------------------+------------+------------
             |                 | 2020-06-08 18:07:36.29538+00 | 2020-06-08 15:37:48.738104+00 |     270418 |          0