MIME-Version: 1.0
From: JORGE MALDONADO <jorgemal1960@gmail.com>
Date: Tue, 25 Jul 2023 09:35:45 -0500
Message-ID: 
 <CAAY=A7_x27sHHgWzDRyWmdhMSVoF0CzDH5121f1=VwJ+iv8RXQ@mail.gmail.com>
Subject: Declaring a constant or variable in a query
To: pgsql-sql@postgresql.org
Content-Type: multipart/alternative; boundary="000000000000a8ed4f060150a919"
Archived-At: 
 <https://www.postgresql.org/message-id/CAAY%3DA7_x27sHHgWzDRyWmdhMSVoF0CzDH5121f1%3DVwJ%2Biv8RXQ%40mail.gmail.com>
Precedence: bulk

--000000000000a8ed4f060150a919
Content-Type: text/plain; charset="UTF-8"

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

--000000000000a8ed4f060150a919
Content-Type: text/html; charset="UTF-8"
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><div di=
r=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"lt=
r"><div dir=3D"ltr"><div dir=3D"ltr"><div dir=3D"ltr">Hi,<div><br></div><di=
v>I am using <b>DataGrip </b>(a tool very much like pgAdmin)<b>=C2=A0</b>to=
 run a query with several <b>UNION</b> clauses as follows:</div><div><br></=
div><div><b>SELECT fld1 as alias1, fld2 as alias2, fld3</b><b>=C2=A0as alia=
s3</b><b>, fld4</b><b>=C2=A0as alias4</b><b>, fld5</b><b>=C2=A0as alias5</b=
><b>=C2=A0FROM table1 WHERE condition1</b></div><div><b>UNION</b></div><div=
><b>SELECT fld1 as alias1, fld2 as alias2, fld3</b><b>=C2=A0as alias3</b><b=
>, fld4</b><b>=C2=A0as alias4</b><b>, fld5</b><b>=C2=A0as alias5</b><b>=C2=
=A0FROM table2 WHERE condition2</b><b><br></b></div><div><div><b>UNION</b><=
/div><div><b>SELECT fld1 as alias1, fld2 as alias2, fld3</b><b>=C2=A0as ali=
as3</b><b>, fld4</b><b>=C2=A0as alias4</b><b>, fld5</b><b>=C2=A0as alias5</=
b><b>=C2=A0FROM table3 WHERE condition3</b><b><br></b></div><div><div><b>UN=
ION</b></div><div><b>SELECT fld1 as alias1, fld2 as alias2, fld3</b><b>=C2=
=A0as alias3</b><b>, fld4</b><b>=C2=A0as alias4</b><b>, fld5</b><b>=C2=A0as=
 alias5</b><b>=C2=A0FROM table4 WHERE condition4</b><b><br></b></div><div><=
br></div></div></div><div>I need <b>fld5</b>=C2=A0to always be a constant i=
n 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 para=
meter for such field like this:</div><div><br></div><div><div><b>SELECT fld=
1 as alias1, fld2 as alias2, fld3</b><b>=C2=A0as alias3</b><b>, fld4</b><b>=
=C2=A0as alias4</b><b>, :fld5</b><b>=C2=A0as alias5</b><b>=C2=A0FROM table1=
 WHERE condition1</b></div><div><b>UNION</b></div><div><b>SELECT fld1 as al=
ias1, fld2 as alias2, fld3</b><b>=C2=A0as alias3</b><b>, fld4</b><b>=C2=A0a=
s alias4</b><b>, :fld5</b><b>=C2=A0as alias5</b><b>=C2=A0FROM table2 WHERE =
condition2</b><b><br></b></div><div><div><b>UNION</b></div><div><b>SELECT f=
ld1 as alias1, fld2 as alias2, fld3</b><b>=C2=A0as alias3</b><b>, fld4</b><=
b>=C2=A0as alias4</b><b>, :fld5</b><b>=C2=A0as alias5</b><b>=C2=A0FROM tabl=
e3 WHERE condition3</b><b><br></b></div><div><div><b>UNION</b></div><div><b=
>SELECT fld1 as alias1, fld2 as alias2, fld3</b><b>=C2=A0as alias3</b><b>, =
fld4</b><b>=C2=A0as alias4</b><b>, :fld5</b><b>=C2=A0as alias5</b><b>=C2=A0=
FROM table4 WHERE condition4</b><b><br></b></div><div></div></div></div></d=
iv><div><br></div><div>My issue using this approach is that <b>DataGrip </b=
>interactively=C2=A0asks for the input value 4 times, which means that the =
parameter value is asked the number of times it is specified no matter=C2=
=A0if the parameter name is exactly the same. In this example, the query ha=
s 3 <b>UNION </b>clauses that involve 4 <b>SELECT </b>statements, however, =
there can be more.</div><div><br></div><div>I have read that it is possible=
 to use a <b>DECLARE</b> statement but it seems to me that this solution=C2=
=A0forces me to use a <b>FUNCTION</b> and I would like to avoid it. Another=
 solution that I saw is to declare a session level variable using <b>SET</b=
>=C2=A0and then use such variable in the query.</div><div><br></div><div>I =
will very much appreciate your feedback on what would be my best choice. I =
am open to hearing new approaches.</div><div><br></div><div>Respectfully,</=
div><div>Jorge Maldonado</div><div><br></div></div></div></div></div></div>=
</div></div></div></div></div></div></div>

--000000000000a8ed4f060150a919--