postgresql - 将变量传递给 DO 循环

Question

我希望能够将 2 个变量传递给我的DO LOOP(effectivedate和nbr_mem_months。这些变量当前被硬编码到DO LOOPDeclare 语句中。这个逻辑计算成员对健康计划有效的月数，并为相应的添加一个增量年和月桶。因此，对于查询的每一行，elan.elig都会DO LOOP将增量添加到YearMonth桶中。因此，如果成员的生效日期201910131和期限日期为20190531，DO LOOP则将为每个桶添加 1：201901 201902 201903 201904 201905

select  (extract(year from  age(case when terminationdate is null then 
                        CURRENT_DATE else terminationdate END ,effectivedate ))) *12 +
(extract(month from  age(case when terminationdate is null then 
                        CURRENT_DATE else terminationdate END ,effectivedate ))  +1)
                        as "effectivedate" ,to_char(effectivedate,'YYYYMM') as nbr_mem_months
from elan.elig


DO $$
declare 
nbr_mem_months integer=5;
effectivedate date='2019-04-01';
ym char(6) =to_char(effectivedate,'YYYYMM');
begin
for r in 1..nbr_mem_months loop

update elan.pmpm set mbrmonths=mbrmonths+1 where yyyyymm=ym;
effectivedate=effectivedate + interval '1 month';
ym=to_char(effectivedate,'YYYYMM');
end loop;
end;
$$;

标签： postgresqlloopsvariables