使用UDF作为列的值

Question

假设我已经有2个电影和它们的评分表。

CREATE TABLE public.movies (
movieid int4 NOT NULL,
averagerating float4 NULL,
title varchar(255) NOT NULL,
"year" int4 NULL,
CONSTRAINT movies_pkey PRIMARY KEY (movieid)
);
CREATE TABLE public.ratings (
movie_id int4 NOT NULL,
user_id int4 NOT NULL,
rating float4 NOT NULL,
timestmp varchar(255) NOT NULL,
CONSTRAINT ratings_pkey PRIMARY KEY (movie_id, user_id),
CONSTRAINT fk44trpo3u915t27ybt03ib4h0o FOREIGN KEY (movie_id) REFERENCES movies(movieid),
CONSTRAINT fk7ymub8kd95i2xlklgole3i684 FOREIGN KEY (user_id) REFERENCES usrs(userid)
);

暂时列平均在电影到处都是空的。我想附加UDFaverage_movie_rating到这一栏

比如update movies set averagerating = average_movie_rating(movies.movieid)

这个函数:

create or replace function average_movie_rating(movieid integer)
returns float8 as $averagerating$
declare 
averagerating float8;
begin
select avg(r.rating) into averagerating from ratings r where r.movie_id = $1;
return averagerating;
end
$averagerating$ language plpgsql;

是否可能，如果是，我如何传递这个movieid到函数?

Answer 1

除非出于性能原因，否则应该在从数据库中进行选择时计算该值。如果你必须持久化它，在ratings上使用一个触发器来修改movies。