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

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

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.

"I work with PostgreSQL 8.3.23" - please upgrade, we're far out of the support lifetime for that version. — Bergi
– Bergi, Commented Jul 9, 2022 at 20:15
Are you using that table last_ym outside of this function? You really should not need that just to produce the result for the function! — Bergi
– Bergi, Commented Jul 9, 2022 at 20:18
interval select'''bm MONTH''' doesn't make sense. See postgresql.org/docs/8.3/datatype-datetime.html#AEN4983 and stackoverflow.com/q/5266758/1048572 — Bergi
– Bergi, Commented Jul 9, 2022 at 20:25
please upgrade, we're far out of the support lifetime for that version. Bergi I can't upgrade it because it is the enterprise standard — Vadim_Mk
– Vadim_Mk, Commented Jul 9, 2022 at 21:26
Are you using that table last_ym outside of this function? You really should not need that just to produce the result for the function! – Bergi Yes, table last_ym will be using further in the middle complicate query — Vadim_Mk
– Vadim_Mk, Commented Jul 9, 2022 at 21:50

Bergi · Accepted Answer · 2022-07-12 16:04:46Z

0

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).

answered Jul 12, 2022 at 16:04

Bergi

671k162 gold badges1k silver badges1.5k bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Vadim_Mk Over a year ago

Bergi, hi! I am sorry because I was very busy on my work. Thank you very match for yours simple and elegant decision . This is exactly what I needed, thanks you very much again. Advise something to read or some resource on pgsql functions - sometimes there was a need to write sometimes simple functions on the server side. Best regards

Collectives™ on Stack Overflow

How correct transfer numeric variable from loop inside postgresql function

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related