Declare variable from other variable

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
6 messages Options
Reply | Threaded
Open this post in threaded view
|

Declare variable from other variable

Raul Kaubi
Hi

PostgreSQL 12.1

How can I declare another variable from another variable.
Basically from oracle, I can just: 
var1 := 'asda'||var2;

In postgres, I have the following example, I would like to use variable j to add number of months there.

" interval 'j month')::date; "

DO $$
DECLARE
v_var integer := 1;
v_from_date date;
BEGIN
for j in 0..v_var LOOP
v_from_date := (date_trunc('month',current_date) + interval 'j month')::date;
RAISE NOTICE '%', v_from_date;
END LOOP;
END;
$$ LANGUAGE plpgsql;

ERROR:  invalid input syntax for type interval: "j month"
LINE 1: ...LECT (date_trunc('month',current_date) + interval 'j month')... 


Raul 
Reply | Threaded
Open this post in threaded view
|

Re: Declare variable from other variable

Yasin Sari
On Wed, Feb 5, 2020 at 2:22 PM Raul Kaubi <[hidden email]> wrote:

DO $$
DECLARE
v_var integer := 1;
v_from_date date;
BEGIN
for j in 0..v_var LOOP
v_from_date := (date_trunc('month',current_date) + interval 'j month')::date;
RAISE NOTICE '%', v_from_date;
END LOOP;
END;
$$ LANGUAGE plpgsql;

ERROR:  invalid input syntax for type interval: "j month"
LINE 1: ...LECT (date_trunc('month',current_date) + interval 'j month')... 



If you replace red line with this one it will work:

v_from_date := (date_trunc('month',current_date) + interval '1 month'*j)::date; 
Reply | Threaded
Open this post in threaded view
|

Re: Declare variable from other variable

Thomas Kellerer-4
In reply to this post by Raul Kaubi
Raul Kaubi schrieb am 05.02.2020 um 12:21:

> How can I declare another variable from another variable.
> Basically from oracle, I can just: 
>
>     var1 := 'asda'||var2;
>
> In postgres, I have the following example, I would like to use variable j to add number of months there.
>
>     " interval 'j month')::date; "
>
>
>     DO $$
>     DECLARE
>     v_var integer := 1;
>     v_from_date date;
>     BEGIN
>     for j in 0..v_var LOOP
>     v_from_date := (date_trunc('month',current_date) + interval 'j month')::date;
>     RAISE NOTICE '%', v_from_date;
>     END LOOP;
>     END;
>     $$ LANGUAGE plpgsql;

The easiest way is to use make_interval()

    v_from_date := (date_trunc('month',current_date) + make_interval(months => j))::date;


But it sounds as if generate_series() is what you are really looking for.


Reply | Threaded
Open this post in threaded view
|

Re: Declare variable from other variable

Raul Kaubi
In reply to this post by Yasin Sari
Thanks, it worked! 

By the way, what does this "*j" mean there..? (this does not mean multiply there?)

And what if, I would like to declare v_to_date also, so that v_to_date is always + 1 month compared to v_date_from..?

-- This one will work, but can this be done simpler..?
v_to_date := (date_trunc('month',current_date)::date + interval '1 month' + interval '1 month'*j)::date;

Raul

Kontakt Yasin Sari (<[hidden email]>) kirjutas kuupäeval K, 5. veebruar 2020 kell 14:28:
On Wed, Feb 5, 2020 at 2:22 PM Raul Kaubi <[hidden email]> wrote:

DO $$
DECLARE
v_var integer := 1;
v_from_date date;
BEGIN
for j in 0..v_var LOOP
v_from_date := (date_trunc('month',current_date) + interval 'j month')::date;
RAISE NOTICE '%', v_from_date;
END LOOP;
END;
$$ LANGUAGE plpgsql;

ERROR:  invalid input syntax for type interval: "j month"
LINE 1: ...LECT (date_trunc('month',current_date) + interval 'j month')... 



If you replace red line with this one it will work:

v_from_date := (date_trunc('month',current_date) + interval '1 month'*j)::date; 
Reply | Threaded
Open this post in threaded view
|

Re: Declare variable from other variable

hubert depesz lubaczewski-2
On Wed, Feb 05, 2020 at 02:42:42PM +0200, Raul Kaubi wrote:
> Thanks, it worked!
>
> By the way, what does this "**j"* mean there..? (this does not mean
> multiply there?)

it's normal multiplication.

Your "j" variable is integer.

So, '1 month'::interval * j is some number of months.
> And what if, I would like to declare v_to_date also, so that v_to_date is
> always + 1 month compared to v_date_from..?

v_to_date := v_from_date + '1 month'::interval; ?

Best regards,

depesz



Reply | Threaded
Open this post in threaded view
|

Re: Declare variable from other variable

Raul Kaubi
Makes sense yeah.

Thanks for both of your help.

Raul

Kontakt hubert depesz lubaczewski (<[hidden email]>) kirjutas kuupäeval K, 5. veebruar 2020 kell 14:50:
On Wed, Feb 05, 2020 at 02:42:42PM +0200, Raul Kaubi wrote:
> Thanks, it worked!
>
> By the way, what does this "**j"* mean there..? (this does not mean
> multiply there?)

it's normal multiplication.

Your "j" variable is integer.

So, '1 month'::interval * j is some number of months.
> And what if, I would like to declare v_to_date also, so that v_to_date is
> always + 1 month compared to v_date_from..?

v_to_date := v_from_date + '1 month'::interval; ?

Best regards,

depesz