Subject: Declaring a constant or variable in a query

From: JORGE MALDONADO <[email-masked]>
Date: Tue, 25 Jul 2023 09:35:45 -0500

Hi,

I am using *DataGrip *(a tool very much like pgAdmin) to run a query with
several *UNION* clauses as follows:

*SELECT fld1 as alias1, fld2 as alias2, fld3** as alias3**, fld4** as
alias4**, fld5** as alias5** FROM table1 WHERE condition1*
*UNION*
*SELECT fld1 as alias1, fld2 as alias2, fld3** as alias3**, fld4** as
alias4**, fld5** as alias5** FROM table2 WHERE condition2*
*UNION*
*SELECT fld1 as alias1, fld2 as alias2, fld3** as alias3**, fld4** as
alias4**, fld5** as alias5** FROM table3 WHERE condition3*
*UNION*
*SELECT fld1 as alias1, fld2 as alias2, fld3** as alias3**, fld4** as
alias4**, fld5** as alias5** FROM table4 WHERE condition4*

I need *fld5* to always be a constant in every SELECT that I would like to
read interactively or to set somewhere in the query (as a variable or
constant for example). I tried to use a parameter for such field like this:

*SELECT fld1 as alias1, fld2 as alias2, fld3** as alias3**, fld4** as
alias4**, :fld5** as alias5** FROM table1 WHERE condition1*
*UNION*
*SELECT fld1 as alias1, fld2 as alias2, fld3** as alias3**, fld4** as
alias4**, :fld5** as alias5** FROM table2 WHERE condition2*
*UNION*
*SELECT fld1 as alias1, fld2 as alias2, fld3** as alias3**, fld4** as
alias4**, :fld5** as alias5** FROM table3 WHERE condition3*
*UNION*
*SELECT fld1 as alias1, fld2 as alias2, fld3** as alias3**, fld4** as
alias4**, :fld5** as alias5** FROM table4 WHERE condition4*

My issue using this approach is that *DataGrip *interactively asks for the
input value 4 times, which means that the parameter value is asked the
number of times it is specified no matter if the parameter name is exactly
the same. In this example, the query has 3 *UNION *clauses that
involve 4 *SELECT
*statements, however, there can be more.

I have read that it is possible to use a *DECLARE* statement but it seems
to me that this solution forces me to use a *FUNCTION* and I would like to
avoid it. Another solution that I saw is to declare a session level
variable using *SET* and then use such variable in the query.

I will very much appreciate your feedback on what would be my best choice.
I am open to hearing new approaches.

Respectfully,
Jorge Maldonado

---

From: Greg Sabino Mullane <[email-masked]>
Date: Tue, 25 Jul 2023 12:51:14 -0400

Not sure how exactly DataGrip works for prompting input, but a CTE should
allow you to declare the constant once:

WITH x AS (SELECT :myinput AS alias5)
SELECT fld1 as alias1, fld2 as alias2, fld3 as alias3, fld4 as alias4,
alias5 FROM table1, x WHERE condition1
UNION
SELECT fld1 as alias1, fld2 as alias2, fld3 as alias3, fld4 as
alias4, alias5 FROM table2, x WHERE condition2
UNION
SELECT fld1 as alias1, fld2 as alias2, fld3 as alias3, fld4 as
alias4, alias5 FROM table3, x WHERE condition3
UNION
SELECT fld1 as alias1, fld2 as alias2, fld3 as alias3, fld4 as
alias4, alias5 FROM table4, x WHERE condition4

Cheers,
Greg