How correct transfer numeric variable from loop inside postgresql function

Question

I work with PostgreSQL 8.3.23. For some cases I want to have table with values in the format YYYYMM as integer from last date month -1 month, -2 month, ...-6 month and I want automate routine with pqsql function. I must enter only one number for example 202204 and get table with required values for further sub query. Now i wrote this sub product

create or replace  function years_months ()  
returns table(id int4, year_month int4) 
language plpgsql as $$  
declare
   end_ym integer:=202204;
   bm_1 integer:=to_char(to_date(to_char(end_ym,'999999'),'YYYYMM')
    - interval '1 MONTH','YYYYMM')::int;
   bm_2 integer:=to_char(to_date(to_char(end_ym,'999999'),'YYYYMM')
    - interval '2 MONTH','YYYYMM')::int;
   bm_3 integer:=to_char(to_date(to_char(end_ym,'999999'),'YYYYMM')
    - interval '3 MONTH','YYYYMM')::int ;
   bm_4 integer:=to_char(to_date(to_char(end_ym,'999999'),'YYYYMM')
    - interval '4 MONTH','YYYYMM')::int ;
   bm_5 integer:=to_char(to_date(to_char(end_ym,'999999'),'YYYYMM')
    - interval '5 MONTH','YYYYMM')::int ;
   bm_6 integer:=to_char(to_date(to_char(end_ym,'999999'),'YYYYMM')
    - interval '6 MONTH','YYYYMM')::int;
begin
    drop table if exists last_ym;
    create table last_ym(
    id serial4 not null,
    year_month int4  not null ,
    constraint id primary key (id));
    create index idx_year_month on last_ym using btree (year_month);

    insert into last_ym(year_month) values
    (bm_1), (bm_2), (bm_3), (bm_4), (bm_5), (bm_6), (end_ym);
    
    return query 
        select 
            * 
        from 
            last_ym
        order by 
            year_month desc;
end $$;

when I run

select * from years_months () 

I get this output

id  year_month
7   202204
1   202203
2   202202
3   202201
4   202112
5   202111
6   202110

select * from last_ym show that table last_ym exits and ready for further work.

My questions

1. How I can using more elegant way with pqslq cycle for bm in 1..7 loop like this bellow

do $$ 
declare
   end_ym integer:=202204;
begin
  drop table if exists last_ym;
  create table last_ym(
  id serial4 not null,
  year_month int4  not null ,
  constraint id primary key (id));
    create index idx_year_month on last_ym using btree (year_month);  
   for bm in 1..7 loop
     if bm < 7 then 
          insert into last_ym(year_month) values(
          to_char(to_date(to_char(end_ym,'999999'),
          'YYYYMM') - interval select'''bm MONTH'''||','|| '''YYYYMM''')::int);
         else
              insert into last_ym(year_month) values(end_ym);
         end if;
   end loop;
end $$;

When I run this script I have this message in the console near interval select'''bm MONTH'''

SQL Error [42601]: ERROR: syntax error at or near "select" Position: 447

Error position: line: 199 pos: 446

2. I want add to table new column - for exampe mo_num char type, where mo_num automate get values last year_month minus month to last_ym table - like see bellow

   id  year_month mo_num
    7   202204      m_0
    1   202203      m_1
    2   202202      m_2
    3   202201      m_3 
    4   202112      m_4
    5   202111      m_5
    6   202110      m_6
   

Thanks in advance, there is very little literature on pgsql.

Best regards, Vadim Maklakov.

Answer 1

Forget all this procedural code. Forget the temporary table, the repeated variables, the drop and recreate, the unnecessary index, the insertions, and the query of the temporary table. You need none of this.

Write a plain simple SQL function that uses the generate_series set-returning function:

CREATE OR REPLACE FUNCTION years_months() RETURNS TABLE(id int4, year_month int4) 
LANGUAGE SQL
PARALLEL SAFE
ROWS 10
IMMUTABLE AS $$  
SELECT
   i,
   to_char(to_date(to_char(202204,'999999'),'YYYYMM') - i * interval '1 MONTH','YYYYMM')::int
FROM generate_series(0, 6) i;
$$;

^{(online demo)}

It does just the same as your original function, but much more efficiently and without all the ugly stateful code. You can also trivially add the mo_num column to it (demo).