public inbox for [email protected]
help / color / mirror / Atom feedDeclaring a constant or variable in a query
2+ messages / 2 participants
[nested] [flat]
* Declaring a constant or variable in a query
@ 2023-07-25 14:35 JORGE MALDONADO <[email protected]>
2023-07-25 16:51 ` Re: Declaring a constant or variable in a query Greg Sabino Mullane <[email protected]>
0 siblings, 1 reply; 2+ messages in thread
From: JORGE MALDONADO @ 2023-07-25 14:35 UTC (permalink / raw)
To: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
* Re: Declaring a constant or variable in a query
2023-07-25 14:35 Declaring a constant or variable in a query JORGE MALDONADO <[email protected]>
@ 2023-07-25 16:51 ` Greg Sabino Mullane <[email protected]>
0 siblings, 0 replies; 2+ messages in thread
From: Greg Sabino Mullane @ 2023-07-25 16:51 UTC (permalink / raw)
To: JORGE MALDONADO <[email protected]>; +Cc: pgsql-sql
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
^ permalink raw reply [nested|flat] 2+ messages in thread
end of thread, other threads:[~2023-07-25 16:51 UTC | newest]
Thread overview: 2+ messages (download: mbox mbox.gz follow: Atom feed)
-- links below jump to the message on this page --
2023-07-25 14:35 Declaring a constant or variable in a query JORGE MALDONADO <[email protected]>
2023-07-25 16:51 ` Greg Sabino Mullane <[email protected]>
This inbox is served by agora; see mirroring instructions
for how to clone and mirror all data and code used for this inbox