Declare variable from other variable

On Wed, Feb 5, 2020 at 2:22 PM Raul Kaubi <raulkaubi@gmail.com> 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;

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.

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 (<yasinsari81@googlemail.com>) kirjutas kuupäeval K, 5.
veebruar 2020 kell 14:28:

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

Makes sense yeah.

Thanks for both of your help.

Raul

Kontakt hubert depesz lubaczewski (<depesz@depesz.com>) kirjutas kuupäeval
K, 5. veebruar 2020 kell 14:50: