301.do.update--record-list-with-calculated-values.fn.sql

create or replace function calc_after_add() returns numeric as $$
declare
  pe record;
  first_tier3_time int :=0;
  first_pair_tier3_time int :=0;
  eatst_value int :=0;
begin
  --
  -- retrieve all records
  --
  -- 1) in a system with active data addition this would check for nulls so it would only
  --   run on new records, and probably be trigger controlled not to mention, there'd be a
  --   case for "calculate on add"
  -- 2) in my case, the SQL is just re-run and the data-set is small, so I omitted that and,
  --   I could have kept "calculate on add" but the file was becoming unwieldy with a lot of
  --   cut and paste duplication
  --
  for pe in
    select
      thlogepen_id,
      tier5_ymto_two,
      tier5_ymto_one,
      lv2a_ymto_1,
      lv2a_ymto_1_1_two,
      laf_ymto_1,
      laf_ymto_2,
      lv2a_ymto_1_2,
      lv2a_ymto_2,
      otc,
      gnsis_tlen,
      tier3_ymto_1,
      tier3_ymto_2,
      tier3_ymto_3,
      cover_tlen,
      sec_of_lv1_ex
    from
      thlogepen pe
  loop
    first_tier3_time = calc_lv3_tier3_ymto(pe);
    first_pair_tier3_time = calc_lv3_tier3_ymto_pr(pe);
    eatst_value = calc_eatst(pe);

    update
      thlogepen
    set
      tier5_ymto_tlen = calc_tlen(pe.tier5_ymto_one, pe.tier5_ymto_two),
      lv2a_ymto_1_tlen = calc_lv2a_ymto_1_tlen(pe),
      lv2a_ymto_2_tlen = calc_tlen(pe.lv2a_ymto_2, eatst_value),
      tier3_ymto_1_tlen = calc_tlen(pe.tier3_ymto_1, eatst_value),
      tier3_ymto_2_tlen = calc_tlen(pe.tier3_ymto_2, eatst_value),
      tier3_ymto_3_tlen = calc_tlen(pe.tier3_ymto_3, eatst_value),
      first_tier3_ymto = first_tier3_time,
      first_tier3_ymto_type = calc_lv3_tier3_ymto_type(pe, first_tier3_time),
      tier3_ymto_any = tier3_ymto_sec(pe),
      full_tier3_ymto_tlen = calc_tlen(first_tier3_time, eatst_value),
      tier3_ymto_tlen = calc_tlen(first_pair_tier3_time, eatst_value),
      tier3_ymto_proportion = first_tier3_time::float / eatst_value::float,
      cover_proportion = pe.cover_tlen::float / calc_survived_vew_tlen(pe)::float,
      thlogepen_tlen = calc_tlen(0, eatst_value),
      vew_record_is_complete = calc_vew_record_is_complete(pe)
    where
      thlogepen_id = pe.thlogepen_id;
  end loop;
  return null;
end;
$$ language plpgsql;