PostgreSQL: Use PSQL command line variable to make your SQL Queries Dynamic

This article is half-done without your Comment! *** Please share your thoughts via Comment ***

In this post, I am sharing tips like how to use a psql session variable to make your queries more dynamic in PostgreSQL.

As a DBA, we prepared lots of .sql files in our server for different purposes like check DB stats, get tables size, get object information.

We are executing this scripts (.sql files) many times, but every time we have to change the schema name parameters because we are checking for different schemas and we hard coded the schema values in the SQL scripts.

I found one solution to resolve this problem, that is PSQL Command line variable. You can declare the variable in the PSQL Session and can use for that session.

The benefit of this is, you do not require to update .sql files for again and again, and multiple people can use same SQL script at a time for different schemas.

Note: As this is a session variable so whenever you connect to PSQL, you have to declare this variable.

Check the below demonstration:

Step 1: Connect to PSQL

Step 2: Create a session variable

Step 3: Embed that variable in your SELECT script of .sql file

If you are using a list of schemas, you can also assign multiple schema names to a session variable.

Declare session variable for multiple values:

Use session variable in IN clause: