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 ();

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!

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 ();

Unexpected loop behaviour

Similar Threads

Unexpected loop behaviour

Similar Threads

Similar Threads

Similar Threads