Run multiple SELECT statements that use common variables? (psql)

Question

I have a Postgresql database and need to run a series of SELECT statements to gather all relevant information for debugging purposes. Sometimes there are 20+ statements. All of the selects use the same "parameters" (or variables), but have different WHERE clauses. There are between 5 and 17 "parameters" that are currently used.

Is there a way for me to define the parameters once at the beginning, and then run all of the SELECT statements (one after another) using the defined values?

For example, suppose there was just one parameter (an ID field) and the following 2 SELECT's, then the pseudo code would be as follows. I would manually update the ID number at the top before each run.

   -- Parameter that gets set manually and is used repeatedly in the SELECTs 

   v_id integer := 4732;



   SELECT * FROM product
   WHERE  id = v_id;

   SELECT * FROM complaints
   WHERE  product_id = v_id;

   ...

The only way I have found to run multiple SELECT statements, is to put them in a file and pass it to psql as a script. Is this the only option I have?

At the moment I am doing a global search and replace on the .sql (script) file before running it, but maybe there's a better way. For example, I enter the following in the SQL file:

SELECT * FROM product
   WHERE  id = #ID;

SELECT * FROM complaints
   WHERE  product_id = #ID;

...

and before running it, do a global search and replace of #ID to 4732, and similarly replace all of the other variables.

I am thinking of writing a Python script to do this for me, but maybe there's a better way of approaching this.

I asked Bing Chat and it suggested creating a temp table to hold the values as global variables, and then I would join to that table in every SELECT. I gave some thought to that, but it's also difficult to edit when there are many potential variables and not all are used each time.

Thanks

Answer 1

Using psql you can achieve that using \set

\set v_id 4732

SELECT * FROM product WHERE id = :'v_id';
SELECT * FROM complaints WHERE product_id = :'v_id';