Unexpected loop behaviour

Hi again all,

I once again am struggling writing a function that does what I want it to. I am aware the ultimate root of my problem is my inadequate knowledge of postgres/SQL in general and I promise I am working on it, but in the meantime any help with this particular issue would be appreciated.

The following function + trigger definition does mostly what I want it to:

create
or replace function generate_instance_balances () returns trigger as $$
BEGIN
  FOR i IN 1..(NEW.instance_year-NEW.previous_instance_year) LOOP
    INSERT INTO fractional_balances (parent_instance, year, effective_age, balance) VALUES (NEW.id, NEW.previous_instance_year+i, i, (NEW.previous_instance_cost*((1+(NEW.annual_inflation_rate/100))^(i)))*((i/NEW.lifetime)));
  END LOOP;
  RETURN NEW;
END;
$$ language plpgsql;

create or replace trigger generate_instance_balances_trigger
after insert on component_instances for each row
execute function generate_instance_balances ();

create
or replace function generate_instance_balances () returns trigger as $$
BEGIN
  FOR i IN 1..(NEW.instance_year-NEW.previous_instance_year) LOOP
    INSERT INTO fractional_balances (parent_instance, year, effective_age, balance) VALUES (NEW.id, NEW.previous_instance_year+i, i, (NEW.previous_instance_cost*((1+(NEW.annual_inflation_rate/100))^(i)))*((i/NEW.lifetime)));
  END LOOP;
  RETURN NEW;
END;
$$ language plpgsql;

create or replace trigger generate_instance_balances_trigger
after insert on component_instances for each row
execute function generate_instance_balances ();

create
or replace function generate_instance_balances () returns trigger as $$
BEGIN
  FOR i IN 1..(NEW.instance_year-NEW.previous_instance_year) LOOP
    INSERT INTO fractional_balances (parent_instance, year, effective_age, balance) VALUES (NEW.id, NEW.previous_instance_year+i, i, (NEW.previous_instance_cost*((1+(NEW.annual_inflation_rate/100))^(i)))*((i/NEW.lifetime)));
  END LOOP;
  RETURN NEW;
END;
$$ language plpgsql;

create or replace trigger generate_instance_balances_trigger
after insert on component_instances for each row
execute function generate_instance_balances ();

create
or replace function generate_instance_balances () returns trigger as $$
BEGIN
  FOR i IN 1..(NEW.instance_year-NEW.previous_instance_year) LOOP
    INSERT INTO fractional_balances (parent_instance, year, effective_age, balance) VALUES (NEW.id, NEW.previous_instance_year+i, i, (NEW.previous_instance_cost*((1+(NEW.annual_inflation_rate/100))^(i)))*((i/NEW.lifetime)));
  END LOOP;
  RETURN NEW;
END;
$$ language plpgsql;

create or replace trigger generate_instance_balances_trigger
after insert on component_instances for each row
execute function generate_instance_balances ();

It inserts the expected number of new rows in the target table, and populates the parent_instance, year, and effective_age columns properly, but not the balance. The balance shows up as 0 for every row except the last one generated by the function, which appears to calculate correctly. What the heck? Can anyone ELI5 what's happening? Thanks!

Unexpected loop behaviour

Similar Threads

Similar Threads

Similar Threads