Thread: Declaring a constant or variable in a query

Declaring a constant or variable in a query

From

JORGE MALDONADO

Date:

25 July 2023, 17:35:45

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

Re: Declaring a constant or variable in a query

From

Greg Sabino Mullane

Date:

25 July 2023, 19:51:14

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