304.do.update--ov-indv-rat.fn.sql

create or replace function update_ov_coindv_rat() returns numeric as $$
declare
  pe record;
  cel_rot numeric(10,9) := 0.0;
  svpth_rot numeric(10,9) := 0.0;
  ov_rat numeric := 0.00;
  tar_gras record;
  manw int := 60;
  weis int := 40;
begin
  --
  -- reset
  --
  update
    thlogepen
  set
    ov_coindv_rat = 0;
  
  --
  -- retrieve gnars for individual calculations relative to gnars
  --
  select 
      max(svpth) - min(svpth) as svpth_gra,
      max(cel) - min(cel) as cel_gra
    from
      thlogepen
    where
      otc = 'LV2'
  into
    tar_gras;
    
  --
  -- now set the ov value in a loop
  --
  for pe in
    select 
      cel,
      svpth,
      thlogepen_id,
      otc
    from
      thlogepen pe
  loop
    --
    -- non-lafs involve c_l
    --
    if
      pe.otc = 'LV2'
    then
      -- c_l relative to tar
      cel_rot :=
        pe.cel
        /
        tar_gras.cel_gra;

      -- sv of event relatoive to tar
      svpth_rot :=
        pe.svpth
        /
        tar_gras.svpth_gra;

      -- ov rating using wgts
      ov_rat :=
      (
        svpth_rot
        *
        weis
      )
      +
      (
        cel_rot
        *
        manw
      );
    end if;

    --
    -- lafs are simpler, just the sv itself for them, no weig
    --
    if
      pe.otc = 'LV1'
    then
      ov_rat := pe.svpth;
    end if;

    -- finish with the update
    update
      thlogepen
    set
      ov_coindv_rat = ov_rat
    where
      thlogepen_id = pe.thlogepen_id;

  end loop;
  return ov_rat;
end;
$$ language plpgsql;