Update column value with trigger postgresql/ fetch value from insert query

Question

Whenever user rates a movie such trigger should be called and update the rating of a rated movie. How do I fetch value of movie_id from query insert into ratings values (movie_id, /*etc*/)?

create trigger new_rating after insert on ratings
execute procedure update_movie_rating();

create or replace function update_movie_rating()
returns trigger 
as $new_rating$
begin 
update movies 
set averagerating =
select avg(r.rating)
from ratings r
where r.movie_id = /*movieid fetched from insert query*/
return new;
end;
$new_rating$ language plpgsql;

See postgresql.org/docs/12/plpgsql-trigger.html

clamp
– clamp

2021-02-08 21:09:23 +00:00
Commented Feb 8, 2021 at 21:09 — clamp
– clamp, Commented Feb 8, 2021 at 21:09

user_0 · Accepted Answer · 2021-02-08 21:13:05Z

1

Assuming field in table "ratings"is called movie_id:

where r.movie_id = NEW.movie_id

You can find a detailed explanation here: https://www.postgresql.org/docs/current/plpgsql-trigger.html

Anyway, you are interested in this specific part:

When a PL/pgSQL function is called as a trigger, several special variables are created automatically in the top-level block. They are:

NEW:

Data type RECORD; variable holding the new database row for INSERT/UPDATE operations in row-level triggers. This variable is null in statement-level triggers and for DELETE operations.

answered Feb 8, 2021 at 21:13

user_0

3,38323 silver badges38 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Update column value with trigger postgresql/ fetch value from insert query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related