How do I automate the summing of values of a column whose table has a reference towards another one?-CodePudding

I am working on a Uni project and so far I've been able to get nearly everything ready. One thing they haven't taught us in detail were triggers and functions, so I had to improvise and learn by myself.

I have the following setup:

create table finalproduct(
    id_final_product int primary key,
    price float default 0,
    usage text,
    --   columns and foreign keys unrelated to the question
);

create table rawmaterials(
    id_product integer primary key,
    material varchar(100),
    price float,
    id_final_product int not null references finalproduct(id_final_product)
        on update cascade
        on delete restrict,
    --   columns and foreign keys unrelated to the question
);

I have values inserted, it's all working fine, however, for automation, I have the following:

create function autosum_product_price()
returns trigger
as $autosum_product_price$
begin
    update finalproduct set price = sum(rpm.price)
    from finalproduct fpr
    left join rawmaterials rmat on rmat.id_final_product = fpr.id_final_product;
end;
$autosum_product_price$ language plpgsql;

create trigger autosum_product
after update
on rawmaterials
for each row
execute function autosum_product_price();

The idea is to have the price of a final product be a sum of the prices of each raw material that references that product. For example:

I add raw materials with IDs 111, 222, and 333, each with the price of 11, referencing one final product with the ID 444. This should end up making the final product 444 have a price of 33, but instead it stays at the default value 0.

What can I do to fix this?

CodePudding user response：

When you say "add raw materials" I assume you mean an insert statement. Well you need to check the documentation for create trigger. Pay particular attention to event parameter, as it controls when the trigger fires (is run). Then look at your trigger definition. I think you will see why you are getting the current results.

Now have a look at your trigger function. There is nothing especially wrong with it, except a missing requires element. And that join is just not necessary. Again the documentation to the rescue: see Trigger Functions. You will see you have complete access to copies of the current row being processed. Using these allows reducing the trigger function to:

create function autosum_product_price()
   returns trigger
  language plpgsql
as $autosum_product_price$
begin
    update finalproduct  
       set price = price   new.price
     where id_final_product = new.id_product;
    return new; 
end;
$autosum_product_price$;

The above will correctly update finalproduct on an insert to rawmaterials. It will not, however, work correctly for update or delete. I leave that as an exercise for you. If you have specific problems then post another question.