Thread: Declare variable from other variable
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
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 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;
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:
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