From: | JORGE MALDONADO <jorgemal1960(at)gmail(dot)com> |
---|---|
To: | pgsql-sql(at)postgresql(dot)org |
Subject: | Declaring a constant or variable in a query |
Date: | 2023-07-25 14:35:45 |
Message-ID: | CAAY=A7_x27sHHgWzDRyWmdhMSVoF0CzDH5121f1=VwJ+iv8RXQ@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | 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
From | Date | Subject | |
---|---|---|---|
Next Message | Greg Sabino Mullane | 2023-07-25 16:51:14 | Re: Declaring a constant or variable in a query |
Previous Message | Anthony Apollis | 2023-07-20 15:47:08 | Re: TSQL To Postgres - Unpivot/Union All |