I'm kind of stuck here. I'll try to keep it simple.

I have two tables.

1. Products (product_id, number_of_reviews, ...)
2. Reviews (main_product_id, review, ...)

main_product_id in Reviews table is the foreign key referencing product_id of Products table.

I need that the number_of_reviews column should automatically update to the number of reviews for that product_id present in the Reviews table. Match can be made by comparing product_id with main_product_id.

I know that I can use count to get number of reviews using this sql statement like:

SELECT COUNT(*) FROM reviews WHERE main_product_id = 'exampleid1'

Here exampleid1 should be product_id from products table.

But how do I create the function that I can call for DEFAULT in column number_of_reviews? Function that automatically takes the product_id from current row and passes it to that select statement and return the number of reviews...

I'm just so stuck here from hours, did a lot of searching but I can't figure it out.

It is my first time asking a question here on stackoverflow and my first time I'm taking interest in coding. PERN stack to be specific. (I didn't like code for more than 6 years but now finally i built some interest)

CodePudding user response：

First off this is actually a bad plan, you are saving a value the can easily be calculated. However, it seems quite common even though it often leads to complications. The function you need is a trigger; more specifically a trigger function and a trigger on reviews. (see demo)

create or replace function record_a_review_air()
  returns trigger 
 language plpgsql 
as $$
begin 
    update products 
       set reviews = reviews 1
     where prod_id = new.prod_id;
    return new;
end;
$$;

create trigger reviews_air
         after insert 
           on reviews
          for each row 
          execute function record_a_review_air();

NOTE: Setting a DEFAULT will not accomplish what you want. Doing so would set the a value when the Product is inserted. But would never be invoked again for that Product.

CodePudding user response：

Here is what worked for me - Thanks to the demo and code provided by @belayer

create or replace function record_a_review_air()
  returns trigger 
 language plpgsql 
as $$
begin 
    update products 
       set reviews = (SELECT COUNT(*) FROM reviews WHERE prod_id = new.prod_id)
     where prod_id = new.prod_id;
    return new;
end;
$$;

create trigger reviews_air
         after insert OR UPDATE OF prod_id, review
           on reviews
          for each row 
          execute function record_a_review_air();